Re: triggers/mutating tables

You are righ. You must not use "on delete cascade" on Z. Any attempt to delete a row in Y which has children rows in Z will raise the following error:
ORA-02292: integrity constraint (schema.constraint_name) violated - child record found.
If you want to capture the error code in your application, you should use PRAGMA EXCEPTION_INIT. Carmen
----- Original Message -----
From: Harald Mitterhofer <mitt_at_pcb899.gud.siemens.at> To: <comp.databases.oracle.server_at_list.deja.com> Sent: Tuesday, August 24, 1999 8:22 PM
Subject: triggers/mutating tables

> Message from the Deja.com forum:
> comp.databases.oracle.server
> Your subscription is set to individual email delivery
> >
> Hi,
> I would like to ask you for a possible solution for the following
> problem:
> I have 3 tables: X, Y, Z, where Z references Y and Y references X;
> If a row in X is deleted, all child-rows in Y and Z also have to be
> deleted; this is perfect for "on delete cascade" in Y and Z, but.....
> if a row in Y is deleted, the rows in Z must not be deleted, but the
> deletion has to fail, if child-rows exist;
> so I think I must not use "on delete cascade" in Z;
>
> how can I solve this problem? I tried to write a "before delete for each
> row" trigger for X, which deletes the appropriate rows in Z, but this
> doesn't work, because Z is a mutating table;
> so I tried to store the deleted id's of X in a temp-list in the
> row-trigger and delete the Z-records in a statement-trigger, but I can
> find no way to do this right;
>
> thanks a lot
> harald
>
>
>
>
> _____________________________________________________________
> Deja.com: Share what you know. Learn what you don't.
> http://www.deja.com/
> * To modify or remove your subscription, go to
> http://www.deja.com/edit_sub.xp?group=comp.databases.oracle.server
> * Read this thread at
>

http://www.deja.com/thread/%3C37C2D4C4.D429B0E7%40pcb899.gud.siemens.at%3E
>

 Sent via Deja.com http://www.deja.com/  Share what you know. Learn what you don't. Received on Wed Aug 25 1999 - 03:37:19 CDT