Re: mutating tables

Hi Harald,

Z is mutating because of the deletion of Y. So you should delete Z first (within trigger on X/before delete/for each) then delete Y. Maybe you will still get the mutating state. You can avoid this by disabling the apropriate foreign keys. You can forget all about "on delete cascade". One bad thing: you must guarante the integrity within your aplication...the disable FKs will be just for documentation...

Hope this helps and, if so, glad to be of any help. Maurício.

In article <37C3AA59.64C347B5_at_pcb899.gud.siemens.at>,   Harald.Mitterhofer_at_siemens.at wrote:
> 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
>
>

--
Maurício B. Falleiros <mbf_at_uniconsult.com.br>
Uniconsult Sistemas e Serviços


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.