Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database Trigger causing Mutating Table

Re: Database Trigger causing Mutating Table

From: Yogesh Sharma <sharmayNOSPAM_at_usa.net.NOSPAM>
Date: 2000/06/29
Message-ID: <395B7C43.EA16F1AB@usa.net.NOSPAM>#1/1

japs1_at_my-deja.com wrote:

> i should add that the mutating table error (ORA-04091) only occurs when
> i try to update the ORDER_DISPATCHED_DATE.

Reason : coz of your if condition, update statement in else part is executed only when you try to change the ORDER_DISPATCHED_DATE.

>
> i take it the mutation is to do with the UPDATE trigger firing
> indefinitely (because the trigger itself has an UPDATE to the same
> table).

As a general rule for RDBMS triggers you can't update/delete/insert on same table in update/delete/insert trigger. If you do that than it is like recurrsion. In your case in update trigger of order_status table you are again updating order_status table, which will again invoke the trigger and it will keep going on. To prevent this Oracle generates this ORA-04091 error.

Hope this helps.

>
>
> i was just wondering if there was another way to tackle this.
>
> thanks for any help.
>
> japs1.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jun 29 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US