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

Home -> Community -> Usenet -> c.d.o.misc -> Re: table mutation (trigger)

Re: table mutation (trigger)

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 31 Jul 1999 12:44:13 GMT
Message-ID: <37a2ee9b.16297093@news.demon.nl>


On Sat, 31 Jul 1999 01:52:28 GMT, Larry Dubov <dubov_at_home.com> wrote:

>Hi there,
>
>Here is a trigger on the table ORDER_DT:
>
>Create or Replace trigger ORDER_DT_INS_BEFORE before
>insert on ORDER_DT
>for each row
>
>declare
>
>begin
>
>:new.CRRNT_FLG := 1;
>
>update ORDER_DT
>set CRRNT_FLG = 0
>where
>ORDER_ID=:new.ORDER_ID and
>CUST_ID=:new.CUST_ID and
>DATE_TYP_ID=:new.DATE_TYP_ID;
>
>end;
>
>/
>
>The trigger works fine when I insert one row using insert into ORDER_DT
>values....
>
> Oracle returns errors, when I try to execute the following:
>
>
>SQL> insert into ORDER_DT
> 2 (ORDER_ID,
> 3 CUST_ID,
> 4 DATE_TYP_ID,
> 5 ORDER_DT_VL)
> 6 select ORDER_ID, CUST_ID, 8, to_date('1/1/99', 'MM/DD/YY')
> 7 from ORDER
> 8 where ORDER_ID > 100 and CUST_ID < 200;
>
>
>insert into ORDER_DT
> *
>ERROR at line 1:
>ORA-04091: table ORDER_DT is mutating, trigger/function may not
>see it
>ORA-06512: at "ORDER_DT_INS_BEFORE", line 4
>ORA-04088: error during execution of trigger 'ORDER_DT_INS_BEFORE'
>
>I would greatly appreciate any help.
>
>Larry

Hi Larry,

I'm not sure what you are trying to accomplish here. First you assing 1 to the flag column (which is the correct way to do it), then you apparently try to update the same row. As the table is already mutating (the trigger fires on insert) you are not allowed to do this.
If you meant to update the same row, use an assingment. If you meant to update a different row you need to create a package with an array in it. This array will buffer the primary key of the table.
Initialize the array to empty, on a before statement trigger. Buffer the keys with a before row trigger. Loop through the array and do you updates with an after statement trigger.

Hth,

Sybrand Bakker, Oracle DBA Received on Sat Jul 31 1999 - 07:44:13 CDT

Original text of this message

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