Home » SQL & PL/SQL » SQL & PL/SQL » Master/Details tables "mutating" error on CASCADE delete
Master/Details tables "mutating" error on CASCADE delete [message #347125] Wed, 10 September 2008 16:45 Go to next message
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 #347126 is a reply to message #347125] Wed, 10 September 2008 16:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Yet another example of why "triggers are evil".
Search this forum for "trouble with triggers" post earlier this week.

The best I can think of is to "suppress/ignore" this error in an EXCEPTION block.
Re: Master/Details tables "mutating" error on CASCADE delete [message #347401 is a reply to message #347126] Thu, 11 September 2008 08:49 Go to previous messageGo to next message
greenstone
Messages: 4
Registered: June 2007
Location: Alexandria, Va
Junior Member
Hi anacedent,

Great solution! (well, not architectually pretty, but I just tested and it seems to be completely function).

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 Go to previous message
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?
Previous Topic: oracle
Next Topic: Settings for ORDCOM
Goto Forum:
  


Current Time: Sat Nov 09 16:51:35 CST 2024