|
Home > Archive > Oracle Server > July 2005 > ORA-04091: table ... is mutating, trigger/function may not see it
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
ORA-04091: table ... is mutating, trigger/function may not see it
|
|
| cookie monster 2005-07-26, 9:23 am |
| 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_CHEC
K
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.
| |
| cookie monster 2005-07-26, 9:23 am |
| oops copy paste error. Trigger really looks like this:
CREATE OR REPLACE TRIGGER MYTABLE_BEF_INS_CHEC
K
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@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_CHEC
K
>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.
>
| |
| fitzjarrell@cox.net 2005-07-26, 11:23 am |
|
cookie monster wrote:[color=darkred
]
> oops copy paste error. Trigger really looks like this:
>
>
> CREATE OR REPLACE TRIGGER MYTABLE_BEF_INS_CHEC
K
> 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@nowhere.com> wrote:
>
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?...2133
7269
It will not be answered again here, when you can find your solution
posted elsewhere.
David Fitzjarrell
| |
| Carlos 2005-07-27, 7:23 am |
| >From metalink:
"There is an exception to this restriction; For single row INSERTs,
constraining tables are mutating for AFTER row triggers, but not for
BEFORE row triggers. INSERT statements that involve more than 1 row are
not considered single row inserts."
"INSERT INTO <table_name> SELECT ..." are not considered single row
inserts, even if they only result in 1 row being inserted. "
Cheers.
Carlos.
|
|
|
|
|