ORA-4091 Mutating Tables [message #38012] |
Wed, 13 March 2002 10:53 |
Brian Reeves
Messages: 1 Registered: March 2002
|
Junior Member |
|
|
I have read a bit about this and think my problem may
be a bit unique. I have a vendor supplied system with
no access to their code and would like to write a
'delete trigger' to fix a bug. When the user deletes
a row, I need to read all the other rows 'like that row,but not that one,', sum a value and store that
value in another table (actually update a row in another table). Can anyone suggest a workaround from
the obvious mutating table problem. I know I can write a procedure that runs say every hour that does this but
I really need it to happen when the row is deleted.
Help, Tks, Brian
|
|
|
Re: ORA-4091 Mutating Tables [message #38015 is a reply to message #38012] |
Wed, 13 March 2002 11:41 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
See this link for a sample of how to handle this situation:
http://osi.oracle.com/~tkyte/Mutate/index.html
Basically, in the row delete trigger, you will store in a packaged array the value(s) that will let you identify the 'like, but not exact' rows. Then, in an after statement trigger, which is not subject to the mutation issue, you cycle through this array, which will contain one entry for each row you deleted in the statement, do your sum calculation, and update the value in the other table.
Let us know if you have some questions along the way.
|
|
|