Loading...

Friday, September 24, 2010

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

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Today i learned something new about the mutating trigger error and in particular i learned that under certain conditions, without implementing any workaround, the trigger will work without issuing such exception.

I was reviewing some code written by other people and i stumbled upon the following trigger definition:
CREATE OR REPLACE
TRIGGER "BIU_MYTABLE"
BEFORE INSERT OR UPDATE
ON "MYTABLE"
FOR EACH ROW
DECLARE
v_newval NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
IF :new."ID" IS NULL
THEN
SELECT "MYTABLE_SEQ".NEXTVAL INTO v_newval FROM DUAL;

IF v_newval = 1
THEN
SELECT NVL (MAX ("ID"), 0) INTO v_newval FROM "MYTABLE";

v_newval := v_newval + 1;

LOOP
EXIT WHEN v_incval >= v_newval;

SELECT "MYTABLE_SEQ".NEXTVAL INTO v_incval FROM DUAL;

END LOOP;
END IF;

:new."ID" := v_newval;
END IF;

END;
My first reaction was to raise my flag and say: hey, watch out, this code is working by pure chance, simply because the sequence is greater than 1 and the inner code is never run (besides the fact that this method of updating the sequence value is certainly questionable for tables containing very large values for ID).
My gut feeling was that ORA-04091 would be returned after dropping and recreating the sequence starting with 1 and attempting to insert a new row into the table.
After talking with the people who are in charge of this program, they assured me that they never came across any problems, even when they migrated the data initially, so i set out to make a little test on my own.

With some surprise i saw that running a statement like
insert into mytable (col_1, col_2) values('test 1','test 2');
did not return any errors.

How can it be ?!?
I knew i cannot SELECT from the table being updated, unless i adopt a much more elaborated approach.

After some research on the web, i came across the following OTN thread, where Justin Cave explains how he found out about before insert triggers selecting from the same table. As Justin noted, this curious case is explained by Tom Kyte in an older thread on AskTom, and in the end the rule of thumb can be summarized as follows:

a before insert trigger may select from the same table being inserted if the original statement is in the form:
insert into mytable (col_1, col_2) values('test 1','test 2');
the same trigger will fail if the original statement is in the form:
insert into mytable (col_1, col_2) select 'test 1','test 2' from dual;

SQL Error: ORA-04091: table TEST.MYTABLE is mutating, trigger/function may not see it
ORA-06512: at "TEST.BIU_MYTABLE", line 11
ORA-04088: error during execution of trigger 'TEST.BIU_MYTABLE'
In my opinion this isn't good news, because having some code that works depending on the method of inserting rows is not going to make your application very robust.

And as a final touch, my customer claims that the code of this trigger has been generated by the migration assistant of SQL Developer.

See message translations for ORA-04091 and search additional resources.

1 comment:

Buzz Killington said...

That is crazy. I can't believe I never knew about that one.

Also kind of scary that it could succeed and the developer/SQL Developer was oblivious as to why it would be expected to fail.

Thanks for the post.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles