Re: Mutating Triggers

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Tue, 21 Sep 2010 06:38:22 -0700 (PDT)
Message-ID: <82e815a9-0e33-476b-bb36-fef21971296f_at_a11g2000vbn.googlegroups.com>


On Sep 21, 3:14 am, The Magnet <a..._at_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. Received on Tue Sep 21 2010 - 15:38:22 CEST

Original text of this message