How to Enable CASCADE UPDATES via trigger ?

From: ALEXANDROS KOTSIRAS <ALEXANDROS_K_at_prodigy.net>
Date: Wed, 1 Dec 1999 23:36:52 -0500
Message-ID: <824sun$39dm$1@newssvr03-int.news.prodigy.com>

Hello,
I have a master detail relationship and although Oracle supports the "ON DELETE CASCADE" during table creation i would like to add "CASCADE UPDATE" on the relationship , which can probably done via trigger ? Unfortunatelly i can't find how to write the trigger, the one that i figured doesn't work and is the following :

BEGIN
   IF UPDATING AND :old.Parent_Table.PrimaryKey != :new.Parent_Table.PrimaryKey THEN

      UPDATE Child_Table
         SET Child_Table.ForeignKey = :new.Parent_Table.PrimaryKey
         WHERE Child_Table.ForeignKey = :old.Parent_Table.PrimaryKey ;

   END IF;
END; The figure fires ON AFTER UPDATE but i also tried ON BEFORE UPDATE.

The error that i get is :
ORA-04091: table PARENT_TABLE is mutating, trigger/function may not see it

How can i fix this ? ?

Thanks,
Alex.

I Received on Wed Dec 01 1999 - 22:36:52 CST