Re: How to Enable CASCADE UPDATES via trigger ?

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.

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. Received on Thu Dec 02 1999 - 11:31:57 CST