Re: Triggers and mutating tables, small example

Hi.

1. ALTER TABLE is DDL command and it implies COMMIT. COMMIT can NOT be executed in trigger.
2. If you are working with 8i then you can use PRAGMA AUTONOMOUS TRANSACTION to encapsulate UPDATE statement.

 HTH. Michal

In article <0Cm45.1609$PQ4.30303_at_news1.online.no>,   "Knut Hovda" <p-poweas_at_online.no> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 22 2000 - 00:00:00 CDT