Mutating Triggers
Date: Mon, 20 Sep 2010 18:14:18 -0700 (PDT)
Message-ID: <4f3c1516-eba6-4ee7-9e7e-43d2369d7674_at_u13g2000vbo.googlegroups.com>
[Quoted] [Quoted] 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? Received on Tue Sep 21 2010 - 03:14:18 CEST