On Sep 21, 3:14 am, The Magnet <a
...@unsu.com> wrote:
> I've read a bunch of this but not 100% how to implement it. We are on
> 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?
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.
