Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> mutating table

mutating table

From: Harald Mitterhofer <mitt_at_pcb899.gud.siemens.at>
Date: Wed, 25 Aug 1999 10:34:03 +0200
Message-ID: <37C3AA7B.833C2DFD@pcb899.gud.siemens.at>


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 Received on Wed Aug 25 1999 - 03:34:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US