Master/Details tables "mutating" error on CASCADE delete [message #347125] |
Wed, 10 September 2008 16:45 |
greenstone
Messages: 4 Registered: June 2007 Location: Alexandria, Va
|
Junior Member |
|
|
Hi,
I have two tables in a master/details configuration (table 1 is "master", table 2 is "details").
The details has a FK contraint relation to the master with a CASCADE delete (so when the master is deleted, all associated details are deleted).
The master table has a column "last_updated_datetime" which is updated by a master table update trigger whenever most (excluding the last_updated_datetime column) columns are updated.
The details table has an update trigger which also updates the master table's last_updated_datetime column (whenever a detail row is changed).
The details table also has a delete trigger which also updated the master table's last_updated_datetime column (whenever a detail row is deleted).
The problem I have is: When the master record is deleted, which cascade deletes the details record(s), the delete trigger on the details table throws a "table is mutating" error.
I understand that the "mutating" error is "correct" because the master record is being deleted.
But is there some way I can get around this problem (for example, having the details table delete trigger not update the master table last_updated_datetime) when it's this cascade delete?
Thanks for your help!
|
|
|
|
|
Re: Master/Details tables "mutating" error on CASCADE delete [message #347402 is a reply to message #347126] |
Thu, 11 September 2008 08:53 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Why not rewrite the On Delete trigger on the detail table as if it were a standard mutating table problem.
That way you elete the master record, the On Delete Cascade constraint deletes the detail rows, recording the ids of all the deleted rows as it goes, and then after this is all finished, the mutating table workaround package tries to update the master table count, finds that the row isn't there and won't need to raise an exception.
You are aware that your master table updating code will almost certainly not work properly in a multi-user environment, aren't you?
|
|
|