1

I have a trigger set to fire after an update of a specific column. I want to update the value of a specific column after a specific value from another column changes to another specific value.

I'm getting the errors:

ORA-04091: table tableName is mutating, trigger/function may not see it
ORA-06512: at "triggerName", line 14
ORA-04088: error during execution of trigger 'triggerName'

I've rewritten this as a before update and after update, as well as tried storing the logic in a function, and using "pragma autonomous_transaction", but the error still gets thrown. 


BEFORE update of columnName1 ON tableName
FOR EACH ROW
BEGIN

    if :new.columnName1 = 3 AND :old.columnName1 = 1 then    
    update tablename
        set columnName2= MOD(sequenceName.NextVal, 5) + 1  
        where tableName.columnName2 = :old.columnName2;
    end if;
END;
/

I don't understand why the entire table is labelled as "mutating" when I am not updating the column affected by the update the trigger is responding to. Surely, you should be able to update the value of a column in a table if another value in the table changes, or am I crazy here?

Note: Only one entry would be affected at a time. In my application logic, I update the status of some person in the database. I want the database to do some logic only on a specific status change, and I want to avoid using API calls for the logic here, as you can see, it is simply one line of logic in PLSQL.

Thanks

Otis Sistrunk
  • 43
  • 4
  • Possible duplicate of [ORACLE After update trigger: solving ORA-04091 mutating table error](https://stackoverflow.com/questions/6915325/oracle-after-update-trigger-solving-ora-04091-mutating-table-error) – OldProgrammer Jul 28 '19 at 02:34
  • @OldProgrammer You are correct, albeit this thread has actual code. Mark as a duplicate if you wish – Otis Sistrunk Jul 28 '19 at 18:58

1 Answers1

1

Perhaps you don't really want to update the table but just change a value in the specific row being updated.

If so, eschew the update and just set the value:

BEFORE update of columnName1 ON tableName
FOR EACH ROW
BEGIN

    if :new.columnName1 = 3 AND :old.columnName1 = 1 then    
       :new.columnName2 := MOD(sequenceName.NextVal, 5) + 1 ; 
    end if;
END;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786