Re: Triggers and mutating tables, small example

For use ALTER statement in a pl/sql block you can use Dynamic Sql . With Package DBMS_SQL it is possible to use DLL. This is documented in Oracle 7 server application developer's guide

I hope this may help you

--
        Dagoberto Navea Alfaro
Ingeniero en Computación e Informática
          Analista de Sistemas
Unidad de Informática y Comunicaciones
          Universidad Arturo Prat
I               quique - Chile
"Solo pido a Dios fortaleza para cambiar las cosas
  que puedo, resignación para aquellas que no puedo
  y sabiduria para diferenciarlas"

Knut Hovda escribió en mensaje ...


>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.

>

>

>