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 REPLACEMy 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).
BEFORE INSERT OR UPDATE
FOR EACH ROW
v_newval NUMBER := 0;
v_incval NUMBER := 0;
IF :new."ID" IS NULL
SELECT "MYTABLE_SEQ".NEXTVAL INTO v_newval FROM DUAL;
IF v_newval = 1
SELECT NVL (MAX ("ID"), 0) INTO v_newval FROM "MYTABLE";
v_newval := v_newval + 1;
EXIT WHEN v_incval >= v_newval;
SELECT "MYTABLE_SEQ".NEXTVAL INTO v_incval FROM DUAL;
:new."ID" := v_newval;
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;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.
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'
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.