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: ORA-04091: table ... is mutating, trigger/function may not see it

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

From: <fitzjarrell_at_cox.net>
Date: 26 Jul 2005 08:17:04 -0700
Message-ID: <1122391024.870202.318120@g14g2000cwa.googlegroups.com>

cookie monster wrote:
> 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.
> >

This has been dicussed many times in the past (a google search through the newsgroup would return any number of hits) as well as at asktom.oracle.com:

http://asktom.oracle.com/pls/ask/f?p=4950:8:17833192286385107492::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:469621337269

It will not be answered again here, when you can find your solution posted elsewhere.

David Fitzjarrell Received on Tue Jul 26 2005 - 10:17:04 CDT

Original text of this message

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