Re: Mutating Triggers
Date: Mon, 27 Sep 2010 19:42:08 +1000
The Magnet <art_at_unsu.com> writes:
> I've read a bunch of this but not 100% how to implement it. We are on
> 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?
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.
-- tcross (at) rapttech dot com dot auReceived on Mon Sep 27 2010 - 11:42:08 CEST