Mutating Triggers
The Magnet
10gR2.
We've an customer order table. When a given product is ordered we
need to set all other products in that customer account to a given
status. We also need to save the existing order status in a different
column so we have it when we are ready to revert the orders back.
We have 2 procedures: SUSPEND_ORDER & RESTORE_ORDER. Procedures that
select the data and store it in another column of the same table, or
restore the original column with the saved value. This will throw an
error as we are running into that mutating table error.
Basic logic is: They order product X, for all other orders in their
account, save their status in column A and set those orders to a
Cancelled status.
When product X completes, take all the saved data from column A and
restore the status of those original orders.
So, with all this logic, how to avoid the mutating table error? This
is a lot of code so I do not want to put it all into a trigger. I'd
rather call the procedure as who knows, in the future we may use that
code elsewhere.
Does any of this make sense?
Carlos
There is no such thing 'mutating triggers' but 'mutating tables', and
they are related to ORA-04091.
:"We've an customer order table. When a given product is ordered we
need to set all other products in that customer account to a given
status."
Why?
:"We also need to save the existing order status in a different column
so we have it when we are ready to revert the orders back."
It smells like a very bad design...
:"So, with all this logic, how to avoid the mutating table error?"
Rethink the whole process.
:"Does any of this make sense?"
I don't think so.
Cheers.
Carlos.
Tim X
No, not really. Either I don't understand your descripiton of your data
model and how it works or it is a very bad model. More info requried -
ideally with a sample table and perhaps some sample transactions. From
your description, it really sounds like your doing something very odd.
Tim
--
tcross (at) rapttech dot com dot au