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.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
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? Easy enough in PL/SQL, right in a database trigger (see links at bottom of post for discussions on whether or not you should put logic like this in your database 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;

Well....maybe not. I execute the following block:

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

and I see this error:

ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "EQUITABLE_SALARY_TRG", line 4

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

The solution, conceptually, is simple enough. If I can do task X in the row level trigger, save whatever information I need to perform X on that row in a to-do list (a collection, perhaps?). Then define an AFTER STATEMENT trigger that goes through the to-do list, and executes the desired logic for each row.

The traditional (now, out-of-date) solution is to define a package that contains a collection defined at the package level. Package-level variables have session scope. So I can add information to the collection within the row-level trigger, and it will still be there when I bubble up to the statement-level trigger.

Here's my package specification:

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;

Huh. I don't see any collection there. Right. You shouldn't. If you put the collection in the specification, it can be modified by any schema with EXECUTE authority on the package, in whatever way anyone wants to mess with that collection. Well, that's no good. So I "hide" the list in the body and "expose" it through the procedures in the spec.

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;
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;

WHILE (g_emp_info.COUNT > 0)
LOOP
l_index := g_emp_info.FIRST;

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;

See? Aren't you glad I wrote that, so you didn't have to? :-) Well, it gets better - as in lots of that code is unnecessary. But before I get to that, let's finish up the old-style approach. We need to rebuild the triggers!

1. Before getting started, make sure no one is going to muck with those rows. And make sure the package-based collection is empty.


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;

2. For each insert or update to employees, add the necessary information to the to-do list.

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;

3. Create a statement-level trigger to apply the rule.

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

And now the update statement will work without raising any ORA-04091 errors!

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

Yep. That's a lot of code to write and deal with to get around this problem. So several years back, the PL/SQL team decided to make things easier for their users with....

The Compound DML Trigger

Straight from the doccompound DML trigger created on a table or editioning view can fire at multiple timing points. Each timing point section has its own executable part and optional exception-handling part, but all of these parts can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error. Two common uses of compound triggers are: (1) To accumulate rows destined for a second table so that you can periodically bulk-insert them; (2) To avoid the mutating-table error (ORA-04091).

The compound trigger more allows you to define variables which persist through the execution of the steps defined in the compound trigger. And that's the aspect of this feature that makes things so much easier when it comes to mutable table errors.

Using this feature, 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. Take a look:

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;
END AFTER EACH ROW;

AFTER STATEMENT IS
l_max_allowed employees.salary%TYPE;
BEGIN
SELECT MIN (salary) * 25
INTO l_max_allowed
FROM employees;

FOR indx IN 1 .. g_row_level_info.COUNT
LOOP
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;

Much simpler  - all relevant code in one place.

More reliable - you don't have to worry about managing the session-persistent collection.

Less code - always a nice thing, as long as the "less code" is also understandable and easy to maintain.

You might also find these resources helpful:

ORACLE-BASE: Trigger Enhancements in Oracle Database 11g Release 1
ORACLE-BASE: Should you use triggers at all? (Facts, Thoughts and Opinions)
Toon Koopelars: Triggers Considered Harmful, Considered Harmful