Mutating Trigger : How to avoid

 Mutating Trigger Error

Mutating table error occurs if we do a DML operation on a row , and in the row level trigger we try to select or perform DML operation on the same row.

If the trigger has only select on same row it can resolve with PRAGMA AUTONOMOUS_TRANSACTION. Here when we select always it will give the value before the update.

create or replace TRIGGER TRG_MUTATE_TEST 
BEFORE UPDATE ON EMPLOYEES
FOR EACH ROW
DECLARE
  v_email   VARCHAR2(100);

BEGIN
  SELECT EMAIL
  INTO   v_email
  FROM   EMPLOYEES
  WHERE EMPLOYEE_ID = :NEW.EMPLOYEE_ID;
  dbms_output.put_line('email id for ' || :NEW.EMPLOYEE_ID || ' is ' ||  :NEW.EMAIL);
  dbms_output.put_line('OLD email id for ' || :NEW.EMPLOYEE_ID || ' is ' ||  v_email);
END;

BEGIN
   UPDATE EMPLOYEES
   SET EMAIL = 'ldehaan@gmail.com'
   WHERE EMPLOYEE_ID = 102;
END;

Error starting at line : 1 in command -
BEGIN
   UPDATE EMPLOYEES
   SET EMAIL = 'ldehaan1@gmail.com'
   WHERE EMPLOYEE_ID = 102;

END;
Error report -
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "HR.TRG_MUTATE_TEST", line 5
ORA-04088: error during execution of trigger 'HR.TRG_MUTATE_TEST'
ORA-06512: at line 2
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

Solution for this is to use PRAGMA AUTONOMOUS_TRANSACTION if the trigger is trying to select from impacted row 

Below is the code

CREATE OR REPLACE TRIGGER TRG_MUTATE_TEST 
AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
DECLARE
  v_email   VARCHAR2(100);
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  SELECT EMAIL
  INTO   v_email
  FROM   EMPLOYEES
  WHERE EMPLOYEE_ID = :NEW.EMPLOYEE_ID;
  dbms_output.put_line('email id for ' || :NEW.EMPLOYEE_ID || ' is ' ||  v_email);
END;

UPDATE EMPLOYEES
SET EMAIL = SUBSTR(EMAIL,1,INSTR(EMAIL,'@')-1) || '1@gmail.com'
WHERE department_id = 20;

email id for 201 is MHARTSTE1@gmail.com
OLD email id for 201 is MHARTSTE@gmail.com
email id for 202 is PFAY1@gmail.com
OLD email id for 202 is PFAY@gmail.com

If in the package or procedure we perform DMLs operations on the table in in the trigger we again perform a DML on same impacted row, PRAGMA AUTONOMOUS_TRANSACTION will not work. For example, in the EMPLOYEES table we want to update the email id for the employees. In the trigger we want to check if the new email id is different from the old email id, we want to update OLD_EMAIL column for the same row. Through a normal update in row level trigger we will get the mutating table error. Below is an example for the same. Note This example is just to demonstrate Mutating trigger. There are better methods to update OLD_EMAIL column in the EMPLOYEES table.

CREATE OR REPLACE TRIGGER TRG_MUTATE_TEST_UPD 
BEFORE UPDATE ON EMPLOYEES
FOR EACH ROW
DECLARE
pragma autonomous_transaction;
   
BEGIN
  IF :OLD.EMAIL <> :NEW.EMAIL
  THEN
    UPDATE EMPLOYEES
    SET OLD_EMAIL = :OLD.EMAIL
    WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
  END IF;
  COMMIT;
END;

BEGIN
  IF :OLD.EMAIL <> :NEW.EMAIL
  THEN
    UPDATE EMPLOYEES
    SET OLD_EMAIL = :OLD.EMAIL
    WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
  END IF;
END;

When update is done, we will get the below error.

Error starting at line : 7 in command -
UPDATE EMPLOYEES
SET EMAIL = EMAIL || '@gmail.com'
WHERE department_id = 20
Error report -
SQL Error: ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "HR.TRG_MUTATE_TEST_UPD", line 7
ORA-04088: error during execution of trigger 'HR.TRG_MUTATE_TEST_UPD'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

PRAGMA AUTONOMOUS_TRANSACTION wont work in this case because the dml in the current transaction is not yet committed and we get a deadlock error.

CREATE OR REPLACE TRIGGER TRG_MUTATE_TEST_UPD 
BEFORE UPDATE ON EMPLOYEES
FOR EACH ROW
DECLARE
pragma autonomous_transaction;
   
BEGIN
  IF :OLD.EMAIL <> :NEW.EMAIL
  THEN
    UPDATE EMPLOYEES
    SET OLD_EMAIL = :OLD.EMAIL
    WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
  END IF;
  COMMIT;
END;

Error starting at line : 7 in command -
UPDATE EMPLOYEES
SET EMAIL = EMAIL || '@gmail.com'
WHERE department_id = 20
Error report -
SQL Error: ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "HR.TRG_MUTATE_TEST_UPD", line 7
ORA-04088: error during execution of trigger 'HR.TRG_MUTATE_TEST_UPD'
00060. 00000 -  "deadlock detected while waiting for resource"
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
           involved. Retry if necessary.

Create a pkg. In the pkg create an associative array having PK and EMAIL column(could be any column) you want to update. Define a counter variable in the pkg. In statement level before trigger initialize this counter variable to 0. In row level trigger after update for each row, store the primary key info and the old and new email information in the array variable. In statement level after trigger, in a loop on the array, check if new and old email are not matching then update OLD_EMAIL.

CREATE OR REPLACE PACKAGE PKG_MUTATE_TEST AS 

  TYPE t_old_email_rec IS RECORD
  (
    n_employeed_id  EMPLOYEES.EMPLOYEE_ID%TYPE,
    v_old_email_id  EMPLOYEES.EMAIL%TYPE,
    v_new_email_id  EMPLOYEES.EMAIL%TYPE
  );
  
  TYPE t_old_email_tab IS TABLE OF t_old_email_rec INDEX BY PLS_INTEGER;
  /* TODO enter package declarations (types, exceptions, methods etc) here */ 
  rec_email_tab   t_old_email_tab;
  
  emp_index  BINARY_INTEGER := 0;

END PKG_MUTATE_TEST;
/

CREATE OR REPLACE TRIGGER TRG_MUTATE_INIT
BEFORE UPDATE ON EMPLOYEES
BEGIN
    
    PKG_MUTATE_TEST.emp_index := 0;
END;

CREATE OR REPLACE TRIGGER "TRG_MUTATE_FOR_EACH_ROW" AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
DECLARE

BEGIN
  PKG_MUTATE_TEST.emp_index := PKG_MUTATE_TEST.emp_index + 1; 
  PKG_MUTATE_TEST.rec_email_tab(PKG_MUTATE_TEST.emp_index).n_employeed_id := :old.EMPLOYEE_ID;
  PKG_MUTATE_TEST.rec_email_tab(PKG_MUTATE_TEST.emp_index).v_old_email_id := :old.EMAIL;
  PKG_MUTATE_TEST.rec_email_tab(PKG_MUTATE_TEST.emp_index).v_new_email_id := :new.EMAIL;
END;
/

create or replace TRIGGER "TRG_MUTATE_AFTER_STMT" AFTER UPDATE ON EMPLOYEES
DECLARE

BEGIN
    FOR i IN 1 .. PKG_MUTATE_TEST.emp_index
    LOOP --{
      dbms_output.put_line('OLD email id for ' || PKG_MUTATE_TEST.rec_email_tab(I).n_employeed_id
       || ' IS ' || PKG_MUTATE_TEST.rec_email_tab(i).v_old_email_id);
      dbms_output.put_line('NEW email id for ' || PKG_MUTATE_TEST.rec_email_tab(I).n_employeed_id
       || ' IS ' || PKG_MUTATE_TEST.rec_email_tab(i).v_new_email_id); 
      IF PKG_MUTATE_TEST.rec_email_tab(i).v_old_email_id <> PKG_MUTATE_TEST.rec_email_tab(i).v_new_email_id
      THEN
        UPDATE EMPLOYEES
        SET OLD_EMAIL = PKG_MUTATE_TEST.rec_email_tab(i).v_old_email_id
        WHERE EMPLOYEE_ID = PKG_MUTATE_TEST.rec_email_tab(i).n_employeed_id;
      END IF;  
    END LOOP; --}
END;
/

BEGIN
UPDATE EMPLOYEES
SET EMAIL = SUBSTR(EMAIL,1,INSTR(EMAIL,'@')-2) || '@gmail.com'
WHERE department_id = 20;
END;

OLD email id for 201 IS MHARTSTE1@gmail.com
NEW email id for 201 IS MHARTSTE@gmail.com
OLD email id for 202 IS PFAY1@gmail.com
NEW email id for 202 IS PFAY@gmail.com