mutating tables

From: Harald Mitterhofer <mitt_at_pcb899.gud.siemens.at>
Date: Wed, 25 Aug 1999 10:33:29 +0200
Message-ID: <37C3AA59.64C347B5_at_pcb899.gud.siemens.at>



Hi,
[Quoted] [Quoted] 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

[Quoted] 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 - 10:33:29 CEST

Original text of this message