Re: Mutating Triggers

From: Tim X <>
Date: Mon, 27 Sep 2010 19:42:08 +1000
Message-ID: <>

The Magnet <> 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.


tcross (at) rapttech dot com dot au
Received on Mon Sep 27 2010 - 11:42:08 CEST

Original text of this message