Re: ORA-4091 and mutating tables

tdry_at_my-dejanews.com schrieb:
>
> We have a situation where we want to maintain a count of child records for
> each parent record in a table. We would also like to delete the parent
> record when all of it's children are gone. To complicate matters there is a
> foreign key constraint on the child table to ensure that the reference to
> it's parent is valid.
>
> We've created a trigger (after/delete) which updates the record count in
> the parent table to record_count-1 and then attempts to perform a delete if
> record_count is zero. Like you might have guessed, we are getting the
> "mutating tables" Oracle error.
>
> In our case, we are not querying the table that the trigger is on, but I'm
> guessing that Oracle probably is because of the foreign key constraint. I
> would think that if all children were truly gone, that the error wouldn't
> occur, but it still seems to. Any thoughts?
>

Yes, it has to do with the foreign key constraint. One solution:

create a package that defines a PL/SQL-Table that holds the the pk of the chield and the fk.

Your row-level trigger populates the PL/SQL-table with data from the deleted rows
you implement a statemnet level trigger to clean up the parent table according to the contents of the PL/SQL-table

HTH
Matthias
--
grema_at_t-online.de

Protect privacy, boycott Intel: http://www.bigbrotherinside.org Received on Fri Mar 05 1999 - 01:15:18 CST