Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-04091: table ... is mutating, trigger/function may not see it
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:
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