table mutation (trigger)

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 Received on Fri Jul 30 1999 - 20:52:28 CDT