Re: How to enable CASCADE UPDATE in Master-Detail Realtionship?

A copy of this was sent to "ALEXANDROS KOTSIRAS" <ALEXANDROS_K_at_prodigy.net> (if that email address didn't require changing) On Wed, 1 Dec 1999 23:53:30 -0500, you wrote:

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

Although I am somewhat opposed to cascading updates (if the primary key changes -- it ain't the primary key), sometimes people want it...

see the url in my signature for a package that generates the update cascade logic for 7.x and up.

In 8.0 and up, you can do it another way -- via a procedure for example. You can create the foreign key constraint to be deferrable, defer the constraint, update the parent and then update the child. Then, to update a primary key, you would call this procedure with the old/new values and let it do the work.

>Thanks,
>Alex.
>
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Dec 02 1999 - 06:30:37 CST