ORA-4091 and mutating tables

  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?

Tim Dry
Senior Software Engineer
Contractors Labor Pool, Inc.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 04 1999 - 23:16:50 CST