Triggers and mutating tables, small example

From: Knut Hovda <p-poweas_at_online.no>
Date: 2000/06/22
Message-ID: <0Cm45.1609$PQ4.30303@news1.online.no>#1/1

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:

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. Received on Thu Jun 22 2000 - 00:00:00 CDT