Re: How to Enable CASCADE UPDATES via trigger ?

A copy of this was sent to decompton_at_worldnet.att.net (if that email address didn't require changing) On Thu, 02 Dec 1999 17:31:57 GMT, you wrote:

>This is not the answer you want to hear. A trigger on table X cannot
>read a row back from table. Since you have RI enabled between the
>tables, ORACLE is performing and internal read back to the parent table
>and that is where the mutating table is coming from.
>

well, an AFTER trigger can read the table... this can be done in triggers with declaritive RI enabled (although option 3 below is IMHO the correct answser)...

We can (and do) do it via triggers by:

• have a row trigger 'undo' the update to the primary key saving the 'old' primary key and 'new' primary key values in package variables.
• having an after trigger 'clone' the parent row with the new key value, updateing the child rows to have the new foreign key values and then getting rid of the old parent rows

A package that automates this is available at http://osi.oracle.com/~tkyte/update_cascade/index.html

>You choices as I see them are:
>
>1. Remove the RI and do both the cascade delete and cascade update via
>triggers.
>
>2. Have the application call a stored proc to do the updates rather
>than a trigger
>
>3. Redesign the tables to have artifical keys. Then the updates can
>happen via trigger. It is not considered proper design to have a
>primary key that changes.
>
>good luck,
>
>dave
>
>In article <824sun$39dm$1_at_newssvr03-int.news.prodigy.com>,
> "ALEXANDROS KOTSIRAS" <ALEXANDROS_K_at_prodigy.net> 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 ? ?
>>
>> Thanks,
>> Alex.
>>
>> I
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
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 - 12:38:01 CST