ORA-04091: table ... is mutating, trigger/function may not see it

From: cookie monster <nospam_at_nowhere.com>
Date: Tue, 26 Jul 2005 16:10:15 +0200
Message-ID: <ppgce1lcrinm2skk7teqrq41jp25d0b5sv@4ax.com>

Hi,

I keep getting this error message for my trigger when performing an insert..select statement (standard plain old insert works):

ORA-04091: table ... is mutating, trigger/function may not see it

The trigger is as follows:

CREATE OR REPLACE TRIGGER MYTABLE_BEF_INS_CHECK BEFORE INSERT ON MYTABLE
DECLARE
  v_o_id number(10);
BEGIN
  IF :new.TYP = 'O' THEN

     SELECT 1
	 INTO v_o_id
	 FROM MYTABLE WHERE TYP = 'O' AND ID=:new.ID;

  END IF;
END; The thing is the trigger works fine for a standard insert like:

insert into MYTABLE( id,typ)
values(426672,'O')

No problem the above works. But when I try an insert select..it fails.

insert into MYTABLE( id,typ)
SELECT x_id, 'O' from AnyOtherTable;

the above will fail.

Anyone an idea why this is the case??

thx.
cookie. Received on Tue Jul 26 2005 - 09:10:15 CDT