Use Compound Trigger to Simplify Mutating Table Error Resolution

View All Scripts Login to Run Script

Script Name Use Compound Trigger to Simplify Mutating Table Error Resolution
Description The compound trigger, added in 11.1, allows you to define variables which persist through the execution of the steps defined in the compound trigger. One very useful application of this feature is to simplify the code needed to avoid mutating table trigger errors.
Area PL/SQL General / PL/SQL Triggers
Contributor Steven Feuerstein
Created Wednesday November 18, 2015

Statement 1

CREATE TABLE employees AS SELECT * FROM hr.employees

Table created.

Statement 2

The rule is: "Your new salary cannot be more than 25x the lowest salary in the company. Your salary will be automatically set to the maximum allowed, if this rule is broken." So just check to see if that rule is violated, right? Well....maybe not. See next step.

Create Row-Level Trigger Referencing Employees Table

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;

Trigger created.

Statement 3

SELECT employee_id, first_name, salary 
  FROM hr.employees 
 WHERE last_name = 'King'


EMPLOYEE_ID FIRST_NAME SALARY
156 Janette 10000
100 Steven 24000

2 rows selected.

EMPLOYEE_ID	FIRST_NAME	SALARY
156	Janette	10000
100	Steven	24000

Statement 4

SELECT MIN (salary) 
  FROM hr.employees


MIN(SALARY)
2100

MIN(SALARY)
2100

Statement 5

This step simply shows you that the trigger I defined in the previous step will not work, since it references the employees table.

Oh no! Mutating Table Trigger Error!

BEGIN  
   UPDATE employees  
      SET salary = 100000  
    WHERE last_name = 'King';  
END;

ORA-04091: table SQL_IMGMWWSYJIADSBASGSEZBXHAN.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "SQL_IMGMWWSYJIADSBASGSEZBXHAN.EQUITABLE_SALARY_TRG", line 4
ORA-04088: error during execution of trigger 'SQL_IMGMWWSYJIADSBASGSEZBXHAN.EQUITABLE_SALARY_TRG' ORA-06512: at "SQL_IMGMWWSYJIADSBASGSEZBXHAN.EQUITABLE_SALARY_TRG", line 4
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-04091

Statement 6

DROP TRIGGER equitable_salary_trg

Trigger dropped.

Statement 7

CREATE OR REPLACE PACKAGE equitable_salaries_pkg 
IS 
   PROCEDURE initialize; 
 
   PROCEDURE add_employee_info ( 
      employee_id_in IN employees.employee_id%TYPE 
    , salary_in IN employees.salary%TYPE 
   ); 
 
   PROCEDURE make_equitable; 
END equitable_salaries_pkg;

Package created.

Statement 8

The basic idea with this pre-11.1 and compound trigger solution is to build a package that relies on a global array to keep track of rows modified, then provide a procedure to execute at the statement level (at which point mutating table errors do not occur). This procedure iterates through all rows changed, and applies the rule.

Package to Manage Changed Rows

CREATE OR REPLACE PACKAGE BODY equitable_salaries_pkg   
IS   
   TYPE id_salary_rt IS RECORD (   
      employee_id   employees.employee_id%TYPE   
    , salary        employees.salary%TYPE   
   );   
   
   TYPE g_emp_info_t IS TABLE OF id_salary_rt   
      INDEX BY PLS_INTEGER;   
   
   g_emp_info                 g_emp_info_t;   
   g_corrections_in_process   BOOLEAN      := FALSE;   
   
   PROCEDURE initialize   
   IS   
   BEGIN   
      g_emp_info.DELETE;   
   END initialize;   
   
   PROCEDURE finished_corrections   
   IS   
   BEGIN   
      g_corrections_in_process := FALSE;   
   END finished_corrections;   
   
   PROCEDURE starting_corrections   
   IS   
   BEGIN   
      g_corrections_in_process := TRUE;   
   END starting_corrections;   
   
   FUNCTION corrections_in_process   
      RETURN BOOLEAN   
   IS   
   BEGIN   
      RETURN g_corrections_in_process;   
   END corrections_in_process;   
   
   PROCEDURE add_employee_info (   
      employee_id_in IN employees.employee_id%TYPE   
    , salary_in IN employees.salary%TYPE   
   )   
   IS   
      l_index   PLS_INTEGER := g_emp_info.COUNT + 1;   
   BEGIN   
      IF NOT corrections_in_process   
      THEN   
         g_emp_info (l_index).employee_id := employee_id_in;   
         g_emp_info (l_index).salary := salary_in;   
            
         DBMS_OUTPUT.PUT_LINE ('add_employee_info: '  
                                || g_emp_info (l_index).employee_id   
                                || '-'   
                                || g_emp_info (l_index).salary   
                               );   
      END IF;   
   END add_employee_info;   
   
   PROCEDURE make_equitable   
   IS   
      l_max_allowed   employees.salary%TYPE;   
      l_index         PLS_INTEGER;   
   BEGIN   
      IF NOT corrections_in_process   
      THEN   
         starting_corrections;   
   
         SELECT MIN (salary) * 25   
           INTO l_max_allowed   
           FROM employees;   
   
         DBMS_OUTPUT.PUT_LINE  ('make_equitable max allowed ' || l_max_allowed);   
   
         WHILE (g_emp_info.COUNT > 0)   
         LOOP   
            l_index := g_emp_info.FIRST;   
            --   
            DBMS_OUTPUT.PUT_LINE ('make_equitable emp id and salary: '  
                                   || g_emp_info (l_index).employee_id   
                                   || '-'   
                                   || g_emp_info (l_index).salary   
                                  );   
   
            IF l_max_allowed < g_emp_info (l_index).salary   
            THEN   
               UPDATE employees   
                  SET salary = l_max_allowed   
                WHERE employee_id = g_emp_info (l_index).employee_id;   
            END IF;   
   
            g_emp_info.DELETE (g_emp_info.FIRST);   
         END LOOP;   
   
         finished_corrections;   
      END IF;   
   END make_equitable;   
END equitable_salaries_pkg;

Package Body created.

Statement 9

CREATE OR REPLACE TRIGGER equitable_salaries_bstrg 
   before INSERT OR UPDATE  
   ON employees 
BEGIN 
   LOCK TABLE employees IN EXCLUSIVE MODE; 
   equitable_salaries_pkg.initialize; 
END;

Trigger created.

Statement 10

Row-Level Trigger: register the employee

CREATE OR REPLACE TRIGGER equitable_salaries_rtrg  
   AFTER INSERT OR UPDATE OF salary  
   ON employees  
   FOR EACH ROW  
BEGIN  
   equitable_salaries_pkg.add_employee_info (:NEW.employee_id, :NEW.salary);  
END;

Trigger created.

Statement 11

At the statement level, we get the lowest salary, compute the new equitable maximum, and then apply it to any salaries which exceeded that.

Statement-Level: Process Changed Rows

CREATE OR REPLACE TRIGGER equitable_salaries_astrg  
   AFTER INSERT OR UPDATE   
   ON employees  
BEGIN  
   equitable_salaries_pkg.make_equitable;  
END;

Trigger created.

Statement 12

No error! All references to triggering table now moved to statement level...but we needed that big, fat, complicated package. Can we do better? Yes!

Try the Update Again

BEGIN  
   UPDATE employees  
      SET salary = 100000  
    WHERE last_name = 'King';  
  
   ROLLBACK;  
END;

add_employee_info: 100-100000
add_employee_info: 156-100000
make_equitable max allowed 52500
make_equitable emp id and salary: 100-100000

Statement 13

DROP TRIGGER equitable_salaries_bstrg

Trigger dropped.

Statement 14

DROP TRIGGER equitable_salaries_rtrg

Trigger dropped.

Statement 15

DROP TRIGGER equitable_salaries_astrg

Trigger dropped.

Statement 16

DROP PACKAGE equitable_salaries_pkg

Package dropped.

Statement 17

In one program unit, I can combine all the different trigger events and code, plus they share scope like the subprograms of a package body. So I declare a variable in the compound trigger and reference it in both trigger events. Much simpler, much less code. More reliable.

Ah, The Simplicity of the Compound Trigger!

CREATE OR REPLACE TRIGGER equitable_salary_trg 
FOR UPDATE OR INSERT ON employees 
COMPOUND TRIGGER 
 
   TYPE id_salary_rt IS RECORD ( 
      employee_id   employees.employee_id%TYPE 
    , salary        employees.salary%TYPE 
   ); 
 
   TYPE row_level_info_t IS TABLE OF id_salary_rt  INDEX BY PLS_INTEGER; 
 
   g_row_level_info   row_level_info_t; 
 
   AFTER EACH ROW IS 
   BEGIN 
      g_row_level_info (g_row_level_info.COUNT + 1).employee_id := 
           :new.employee_id; 
      g_row_level_info (g_row_level_info.COUNT).salary := :new.salary; 
 
      DBMS_OUTPUT.put_line ('add_employee_info ' 
         || g_row_level_info.COUNT 
         || ': ' 
         || g_row_level_info (g_row_level_info.COUNT).employee_id 
         || '-' 
         || g_row_level_info (g_row_level_info.COUNT).salary 
         ); 
   END AFTER EACH ROW; 
 
   AFTER STATEMENT IS 
      l_max_allowed   employees.salary%TYPE; 
   BEGIN 
      SELECT MIN (salary) * 25 
        INTO l_max_allowed 
        FROM employees; 
 
      DBMS_OUTPUT.put_line  ( 
          'make_equitable max allowed ' || l_max_allowed 
           || ' Count = ' || g_row_level_info.COUNT  ); 
 
      FOR indx IN 1 .. g_row_level_info.COUNT 
      LOOP 
         DBMS_OUTPUT.put_line ('make_equitable emp id and salary: ' 
                                   || g_row_level_info (indx).employee_id 
                                   || '-' 
                                   || g_row_level_info (indx).salary 
                                  ); 
 
         IF l_max_allowed < g_row_level_info (indx).salary 
         THEN 
            UPDATE employees 
               SET salary = l_max_allowed 
             WHERE employee_id = g_row_level_info (indx).employee_id; 
         END IF; 
      END LOOP; 
   END AFTER STATEMENT; 
END equitable_salary_trg;

Trigger created.

Statement 18

Still No Error, Just One Trigger, No Package

BEGIN  
   UPDATE employees  
      SET salary = 100000  
    WHERE last_name = 'King';  
  
   ROLLBACK;  
END;

add_employee_info 1: 100-100000
add_employee_info 2: 156-100000
make_equitable max allowed 52500 Count = 2
make_equitable emp id and salary: 100-100000
add_employee_info 1: 100-52500
make_equitable max allowed 52500 Count = 1
make_equitable emp id and salary: 100-52500
make_equitable emp id and salary: 156-100000
add_employee_info 1: 156-52500
make_equitable max allowed 52500 Count = 1
make_equitable emp id and salary: 156-52500

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database