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

From: cookie monster <nospam_at_nowhere.com>
Date: Tue, 26 Jul 2005 16:58:38 +0200
Message-ID: <5sjce1leq1gd8unkmuq8qfteubca6241cc@4ax.com>

oops copy paste error. Trigger really looks like this:

CREATE OR REPLACE TRIGGER MYTABLE_BEF_INS_CHECK BEFORE INSERT ON MYTABLE
FOR EACH ROW
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; On Tue, 26 Jul 2005 16:10:15 +0200, cookie monster <nospam_at_nowhere.com> wrote:

>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:58:38 CDT