[Trigger] -recursivité before|after insert
Désidément bc de pb aujourd'hui ... mais j'apprends bcp.
Encore un pb avec mes triggers ; la récursivité.
Mon problème concerne la duplication d'un enregistrement via un trigger.
Un article est dédié à ce sujet dans le oracle book d'oracle press (Oracle 8i) mais dans l'exemple cité la duplication se fait sur une base distante via Dblink dans mon cas la duplication est partiel (pas de pb de ce coté la) mais dans la même table. Existe t il une solution pour evité une execution récursive du trigger.
voila mes triggers ::
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| CREATE OR REPLACE TRIGGER TRIG_BRANCARD
BEFORE INSERT ON BRANCARD
FOR EACH ROW
DECLARE
ROW1 INT;
BEGIN
SELECT SEQ_BRANCARD.NEXTVAL INTO ROW1 FROM DUAL ;
:NEW.NO_BRANC:=ROW1;
END;
/
-- Trigger preparent le retour....
CREATE OR REPLACE TRIGGER TRIG_BRANCARD_PREPRETOUR
BEFORE INSERT ON BRANCARD
FOR EACH ROW
DECLARE
R1 INT;
BEGIN
SELECT SEQ_BRANCARD.NEXTVAL INTO R1 FROM DUAL ;
INSERT INTO BRANCARD
(NO_HOP,NO_BRANC,TY_BRANC,DT_TRANS,ID_PATIENT,MD_TRANS)
VALUES
(:new.NO_HOP,R1,'R',:new.DT_TRANS,:new.ID_PATIENT,:new.MD_TRANS) ;
END;
/ |
voila l'erreur
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "OPS$BRANCARD.TRIG_BRANCARD", line 5
ORA-04088: error during execution of trigger 'OPS$BRANCARD.TRIG_BRANCARD'
ORA-06512: at "OPS$BRANCARD.TRIG_BRANCARD", line 8
ORA-04088: error during execution of trigger 'OPS$BRANCARD.TRIG_BRANCARD'
ORA-06512: at "OPS$BRANCARD.TRIG_BRANCARD", line 8
ORA-04088: error during execution of trigger 'OPS$BRANCARD.TRIG_BRANCARD'
ORA-06512: at "OPS$BRANCARD.TRIG_BRANCARD", line 8
ORA-04088: error during execution of trigger 'OPS$BRANCARD.TRIG_BRANCARD'
ORA-06512: at "OPS$BRANCARD.TRIG_BRANCARD", line 8
ORA-04088: error during execution of trigger 'OPS$BRANCARD.TRIG_BRANCARD'
ORA-06512: at "OPS$BRANCARD.TRIG_BRANCARD", line 8
ORA-04088: error during execution of trigger 'OPS$BRANCARD.TRIG_BRANCARD'
ORA-06512: at "OPS$BRANCARD.TRIG_BRANCARD", line 8
ORA-04088: error during execution of trigger 'OPS$BRANCARD.TRIG_BRANCARD'
ORA-06512: at "OPS$BRANCARD.TRIG_BRANCARD", line 8
ORA-04088: error during execution |
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE OR REPLACE TRIGGER TRIG_BRANCARD
BEFORE INSERT ON BRANCARD
FOR EACH ROW
DECLARE
ROW1 INT;
R1 INT;
BEGIN
SELECT SEQ_BRANCARD.NEXTVAL INTO ROW1 FROM DUAL ;
:NEW.NO_BRANC:=ROW1;
SELECT SEQ_BRANCARD.NEXTVAL INTO R1 FROM DUAL ;
INSERT INTO BRANCARD
(NO_HOP,NO_BRANC,TY_BRANC,DT_TRANS,ID_PATIENT,MD_TRANS)
VALUES
(:new.NO_HOP,R1,'R',:new.DT_TRANS,:new.ID_PATIENT,:new.MD_TRANS) ;
END;
/ |
voila l'erreur
Code:
1
2
| Warning: Entry/Exit code is optimized. Cannot restore context (UNWIND 22)
ORA-03113: end-of-file on communication channel |
une idée...
merci d'avance && merci pour votre grande réactivité constatée lors de mes précédents posts
