DBA_ERRORS and error lines reported for TRIGGERS

As you probably know, the lines shown in views DBA_ERRORS/ALL_ERRORS/USER_ERRORS in the case of triggers are wrong.

But they are not totally wrong, they are just shifted by the amount of lines between the line containing the keyword TRIGGER and either DECLARE or BEGIN, whichever occurs first.

See the example below for an error reported by the dba_errors view on line 2 caused by missing grants on the table used in the variable declaration:

TRIGGER GAGREA2.TR_ASSEGNAZIONE_AI
   AFTER INSERT ON ASSEGNAZIONE
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
DECLARE
   LI_ID_ASSEGNAZIONE_DOM_RICGEN             GAGREA2.DOMANDA_ASG_SPECIFICHE.ID_ASSEGNAZIONE_DOM_RICGEN%TYPE;
BEGIN

   GAGREA2.PKG_TRIGGER.SET_ID_ASSEGNAZIONE_DOM_RICGEN ( :NEW.ID_DOMANDA, LI_ID_ASSEGNAZIONE_DOM_RICGEN );

END TR_ASSEGNAZIONE_AI;

So, the real line number can be obtained adding the number of the line containing DECLARE (or BEGIN if DECLARE is missing) minus 1, that is 2 + 5 - 1 = 6.

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.