Mutating table with a BEFORE UPDATE statement what is the workaround?

[xanadoo]

Hi, you can call me XANADOO or Phil

I have a mutating table error that occurs when I try to insert into a table which has a before insert trigger on it. I found a workaround for that case with the folowing script (I created a temp table called MUTATING_TMP_TAB (A NUMBER(1):

CREATE OR REPLACE TRIGGER TRI_BEFORE_I_CREDIT_TAXE
BEFORE INSERT on CREDIT_TAXE
for each row
DECLARE
I NUMBER := 1;
BEGIN

SELECT A INTO I FROM MUTATING_TMP_TAB;

EXCEPTION
WHEN NO_DATA_FOUND THEN

INSERT INTO MUTATING_TMP_TAB VALUES (1);

INSERT INTO CREDIT_TAXE
VALUES( CFP_SEQNC_PAR.nextval,
:NEW.ANNEE_COURANT,
:NEW.MONTANT / 12,
:NEW.C_TYPE_TAXE,
189);

delete from MUTATING_TMP_TAB;

end TRI_AFTER_I_CREDIT_TAXE;
/

This works fine... But my problem is that when I try to use the same logic with a before UPDATE trigger, I does not work!

Here it is :


CREATE OR REPLACE TRIGGER TRI_BEFORE_U_CREDIT_TAXE
before UPDATE on CREDIT_TAXE
for each row
DECLARE

I NUMBER := 1;

BEGIN

SELECT A INTO I FROM MUTATING_TMP_TAB;

EXCEPTION
WHEN NO_DATA_FOUND THEN

INSERT INTO MUTATING_TMP_TAB VALUES (1);

update CREDIT_TAXE
set ANNEE_COURANT = :new.ANNEE_COURANT,
MONTANT = :new.MONTANT / 12
where ANNEE_COURANT = ld.ANNEE_COURANT
and C_TYPE_TAXE = ld.C_TYPE_TAXE
and C_TYPE_TAUX = 189;


delete from MUTATING_TMP_TAB;

end TRI_BEFORE_U_CREDIT_TAXE;
/


Have a solution?

 

[listener22]

If you have the book, Oracle 8 PL/SQL Programming (Urman, Oracle Press),
there's an example on pages 336 - 340. It involves having both a row-level trigger
and a statement-level trigger on the table.
If you don't have the book, let me know. [sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]