Re: Mutating Triggers

The Magnet <art_at_unsu.com> writes:

> 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?

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