Mutating trigger by example

 

Mutating triggers / tables

 

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify.

A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). 

In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

 

 

CREATE OR REPLACE TRIGGER equitable_salary_trg

   AFTER INSERT OR UPDATE

   ON employees

   FOR EACH ROW

DECLARE

   l_max_allowed   employees.salary%TYPE;

BEGIN

   SELECT MIN (salary) * 25

     INTO l_max_allowed

     FROM employees;

 

   IF l_max_allowed < :NEW.salary

   THEN

      UPDATE employees

         SET salary = l_max_allowed

       WHERE employee_id = :NEW.employee_id;

   END IF;

END equitable_salary_trg;

 

Execute the following statement to check if the trigger works

BEGIN 

   UPDATE employees 

      SET salary = 100000 

    WHERE last_name = 'King'; 

END;

 

OK, we get that, right? I am both selecting from and trying to update the EMPLOYEES table in a row-level trigger. That's the no-no.

Getting Around ORA-04091 with PL/SQL Packages