Re: Triggers and mutating tables, small example

Knut Hovda wrote:
>
> Hello,
>
> I've run into an interesting problem with triggers and mutating table. Say I
> have three tables, A, B and C, where BC and C are children of A, and C
> refers to B (this referance can be NULL.) Table defs:
>
> CREATE TABLE A (
> A_ID number(16) NOT NULL PRIMARY KEY);
>
> CREATE TABLE B (
> B_ID number(16) NOT NULL PRIMARY KEY,
> RA_ID number(16) NOT NULL
> CONSTRAINT B_A_FK REFERENCES A(A_ID) ON DELETE CASCADE);
>
> CREATE TABLE C (
> C_ID number(16) NOT NULL PRIMARY KEY,
> RB_ID number(16)
> CONSTRAINT C_B_FK REFERENCES B(B_ID),
> RA_ID number(16) NOT NULL
> CONSTRAINT C_A_FK REFERENCES A(A_ID) ON DELETE CASCADE);
>
> Next I need a trigger to set C.RB_ID to NULL if needed after a delete
> operation on B:
>
> CREATE OR REPLACE TRIGGER Reset_RB_ID
> BEFORE DELETE ON B
> FOR EACH ROW
> BEGIN
> UPDATE C SET RB_ID = NULL
> WHERE RB_ID = :old.B_ID;
> END;
>
> This trigger works fine when I delete something from B. But if I delete
> something from A, because on the ON DELETE CASCADE I get
>
> ORA-04091: table SYSTEM.C is mutating, trigger/function may not see it
> ORA-06512: at "SYSTEM.RESET_RB_ID", line 2
> ORA-04088: error during execution of trigger 'SYSTEM.RESET_RB_ID'
>
> OK, so I assume there are (at least) two possibilities:
>
> 1. Set up a trigger on BEFORE DELETE ON A that disables the trigger
> Reset_RB_ID (and another to enable it again).
>
> CREATE OR REPLACE TRIGGER Disable_RB_Trig
> BEFORE DELETE ON A
> BEGIN
> ALTER TRIGGER Reset_RB_ID DISABLE;
> END;
>
> CREATE OR REPLACE TRIGGER Enable_RB_Trig
> AFTER DELETE ON A
> BEGIN
> ALTER TRIGGER Reset_RB_ID ENABLE;
> END;
>
> However, these triggers will not compile, as ALTER does not seem to be
> allowed here. Is this correct, or have I missed something?
>
> 2. If I somehow could encapsulate the UPDATE statement in Reset_RB_ID in an
> if-test checking if the table is mutating, I guess I could avoid this error
> message. But how can I do that?
>
> Or are there other, better solutions to this problem? In advance, thanks
> for your help.
>
> Regards,
>
> Knut Hovda (knut.hovda_at_pti.no)
> PTI Scandpower AS.

http://osi.oracle.com/~tkyte

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse