Compound Trigger

ORA-04091:  Mutating, Trigger Error in Oracle with Example

Topic Introduction: In this tutorial, we will discuss the mutating table error in Oracle and fix it using a compound trigger.

When a table is mutating, it is changing. If the change is taking place and we try to make another change in the middle of the first change, Oracle will issue a mutating table error with the error code ORA-04091.

Specifically, the error results from the following operations:

1. We update data to a table.

1. A row-level trigger associated with the table automatically fires and makes another change to the table.

Mutating table error example: We will use HR.EMPLOYEES table for this experiment.
  EMPLOYEE_ID
  FIRST_NAME
  LAST_NAME
  EMAIL
  PHONE_NUMBER
  HIRE_DATE
  JOB_ID
  SALARY
  COMMISSION_PCT
  MANAGER_ID

  DEPARTMENT_ID

Here we want to update the salary for an employee. But the company has a binding that we can give a salary greater than 5 times of lowest salary, and the system automatically assigns 5 times of lowest salary to the employee if we want to update a salary greater than 5 times of lowest salary.

CREATE OR REPLACE TRIGGER EMPLOYEES_SAL_POLICY 

    AFTER INSERT OR UPDATE 

    ON EMPLOYEES

    FOR EACH ROW 

DECLARE 

    L_MAX_SAL   EMPLOYEES.SALARY%TYPE; 

BEGIN 

    -- get the lowest salary without zero 

    SELECT MIN (SALARY) * 5 

        INTO L_MAX_SAL 

        FROM EMPLOYEES

        WHERE SALARY > 0;

    

    -- check with the new salary

    IF L_MAX_SAL < :NEW.SALARY 

    THEN 

        UPDATE EMPLOYEES 

        SET SALARY = L_MAX_SAL 

        WHERE EMPLOYEE_ID = :NEW.EMPLOYEE_ID; 

    END IF; 

END;

/

This statement  the credit limit of the customer 1 to 12000:

Update Statement for increase salary of EMPLOYEE_ID 200 where Present salary is 4400 to 20100. In this table, the lowest salary is 2100

UPDATE EMPLOYEES

SET EMPLOYEES = 20100

WHERE EMPLOYEE_ID = 200; 

The update action fires the trigger and Oracle issues the following mutating table error: ORA-04091: table OT.CUSTOMERS are mutating, trigger/function may not see it

The update statement changes the data of the Employees table. The trigger fires and attempts to make another change while the first change is in the progress, which results in an error.

Fixing The Mutating Table Error: 

CREATE OR REPLACE TRIGGER EMPLOYEES_SAL_POLICY    

    FOR UPDATE OR INSERT ON EMPLOYEES    

    COMPOUND TRIGGER     

    TYPE R_EMPLOYEES_TYPE IS RECORD (    

        EMPLOYEE_ID   EMPLOYEES.EMPLOYEE_ID%TYPE, 

        SALARY  EMPLOYEES.SALARY%TYPE    

    );    

    TYPE T_EMPLOYEES_TYPE IS TABLE OF R_EMPLOYEES_TYPE  

        INDEX BY PLS_INTEGER;    

    T_EMPLOYEE   T_EMPLOYEES_TYPE;    

    AFTER EACH ROW IS    

    BEGIN  

        T_EMPLOYEE (T_EMPLOYEE.COUNT + 1).EMPLOYEE_ID :=    

            :NEW.EMPLOYEE_ID;    

        T_EMPLOYEE (T_EMPLOYEE.COUNT).SALARY := :NEW.SALARY;

    END AFTER EACH ROW;    

    AFTER STATEMENT IS    

        L_MAX_SAL   EMPLOYEES.SALARY%TYPE;    

    BEGIN      

        SELECT MIN (SALARY) * 5    

            INTO L_MAX_SAL    

            FROM EMPLOYEES

            WHERE SALARY > 0;

        FOR INDX IN 1 .. T_EMPLOYEE.COUNT    

        LOOP                                      

            IF L_MAX_SAL < T_EMPLOYEE (INDX).SALARY    

            THEN    

                UPDATE EMPLOYEES    

                SET SALARY = L_MAX_SAL    

                WHERE EMPLOYEE_ID = T_EMPLOYEE (INDX).EMPLOYEE_ID;    

            END IF;    

        END LOOP;    

    END AFTER STATEMENT;    

END; 

Trigger Effects:

1. Declare an array of customer records that includes customer id and credit limit.
2. Collect affected rows into the array in the row-level trigger.
3. Update each affected row in the statement level trigger.

Note 1. Use this compound trigger code if the database 11g and later.