Skip to main content

Get rid of mutating table trigger errors with the compound trigger

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

Comments

  1. won't this give a recursive trigger errors.
    because the driving dml is a update and the trigger also contains update on same table employees.

    ReplyDelete
  2. Steven,
    Compound trigger is definitely the best option for this problem. At the same time, why package based solution has to be so complex? What do you think about the following approach:

    CREATE TABLE emp AS SELECT * FROM scott.emp;

    CREATE OR REPLACE PACKAGE pkg_emp_cache AS
    max_sal emp.sal%TYPE;
    END;
    /

    CREATE OR REPLACE TRIGGER trg_bef_ins_upd_emp
    BEFORE INSERT OR UPDATE ON emp
    BEGIN
    SELECT 25*MIN(sal) INTO pkg_emp_cache.max_sal
    FROM emp;
    END;
    /

    CREATE OR REPLACE TRIGGER trg_bef_ins_upd_row_emp
    BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW
    BEGIN
    :NEW.sal:=LEAST(pkg_emp_cache.max_sal, :NEW.sal);
    DBMS_OUTPUT.PUT_LINE('New Salary of ' || :NEW.ename || ' is set to ' || :NEW.sal);
    END;
    /

    Now, let's test it:

    --Single row update:

    UPDATE emp
    SET sal = 30000
    WHERE ename = 'KING'

    1 row(s) updated.
    New Salary of KING is set to 20000

    --Multiple row update:

    UPDATE emp
    SET sal = 10*sal
    WHERE deptno=30

    6 row(s) updated.
    New Salary of BLAKE is set to 20000
    New Salary of ALLEN is set to 16000
    New Salary of WARD is set to 12500
    New Salary of MARTIN is set to 12500
    New Salary of TURNER is set to 15000
    New Salary of JAMES is set to 9500

    ReplyDelete
    Replies
    1. This approach is also fine..

      Delete
    2. Zahar,

      I am so sorry that I did not reply to this earlier.

      Yes, for this requirement your code is a simpler and perfectly adequate solution!

      Delete
    3. Hi,
      How can you be sure these two triggers will fire in desired order? Both of them are BEFORE INSERT OR UPDATE.
      Kind regards

      Delete
    4. Actually, one is a statement level trigger and the other is row level.

      Delete
  3. None of these worked for me. My triggers task is to fill a stock when it is empty. But it fills before the stock could get the 0 value. And I have a procedure which tries to buy up all items on the stock, while it is not 0. This way your solutions cause me infinite loop.

    ReplyDelete
    Replies
    1. I am happy to help you, but this doesn't really give me enough to work with. I suggest you work up an example and post it on LiveSQL.oracle.com. You can make it unlisted. Then post the link here or send it to me steven dot feuerstein at oracle dot com, and I will take a look.

      Delete
  4. Very good example on COMPOUND TRIGGERS! Thanks, Steven!

    Only should notice that the solution is maybe incomplete for the given task - it deals with the new inserts of bigger salaries, but it doesn't with new inserts of smaller ones. I mean, what if the new salary of an employee is the new MINIMAL salary? Shouldn't we adjust salaries of all other employees then for them to be not greater than 25x of the new minimal salary?

    ReplyDelete
  5. Very good point, Denis. Yes, I would say that if you were going to copy my code and use it in your own application for this requirement, you should make sure to adjust salaries of all other employees, as well.

    ReplyDelete
  6. Very good, thanks! for the above.

    ReplyDelete
  7. Hi Steven,

    I am pretty new to pl/sql world and i am looking for help to write a compound trigger for a user story. Can you please let me know how to proceed like where do i start posting my scenarios and the code i have written so far.

    ReplyDelete
    Replies
    1. I hope it goes smoothly! My suggestion is to create an unlisted script on LiveSQL.oracle.com. You can then send me the link and I can take a look.

      Delete
  8. Thank you very much for the link Steven . I started reading your blogs and they are just like bible for pl/sql . You are helping a lot to people like us who started this journey.

    Here is the link

    https://livesql.oracle.com/apex/livesql/s/jatca3n0hd7iop3cxiew5mzvr

    ReplyDelete
  9. Hello Steven Below is my user story. posting here as the livesql link all the notes got appended.


    I got a column on the EMP table as DEPT_NAME which has 10 employees in the same department. I am adding a new column LAST_DAY which can have either Y/N.

    1) When new record is inserted LAST_DAY value should be 'Y' .
    2) If a matching department name exists for that employee and if the last_day for that deparment is 'Y' then new record shoudd get the LAST_DAY value as 'Y'.
    3) If a matching department name exists for that employee and if the last_day for that deparment is 'N'then new record shoudld get the LAST_DAY value as 'N'.
    4) If few records are 'Y' and 'N' on any depart_name then LAST_DAY should default to 'Y'.

    For Updating:

    1) updating new field LAST_DAY from 'Y' to 'N' then all the records which are in that same department should also flip to 'N'.
    2) updating new field LAST_DAY from 'N' to 'Y' then all the records which are in that same department should also flip to 'Y'.

    ReplyDelete
  10. Hi Steven,
    Can we implement multi level approval logic using compound level triggers? Like for am issue raised it needs to go to 3 approvers via email and every approver once approves, it would pass to next approvers?
    This is an actually requirement so wondering if compound triggers would help

    ReplyDelete
    Replies
    1. I don't immediately see how a compound trigger would help with workflow logic.

      Perhaps a reader of this post will have other ideas.

      Delete
  11. Nicely explained Steven
    Can you please add your thoughts for below
    When we use before timing in row level trigger and try to read the table from which the trigger was fired mutating error will not occur, what do you think about it?

    ReplyDelete
    Replies
    1. Sounds interesting.

      I will be able to give you my thoughts when you provide a script on livesql.oracle.com demonstrating your idea!

      Delete
    2. Hi Steven,
      Please check out my script and let me know
      https://livesql.oracle.com/apex/livesql/s/kghwvq23z0z0m7wrum6pp4m6x
      does it make sense?

      Delete
    3. Not really.

      Are you wondering why you do not get a mutating table error since you are querying from the table in a row level insert trigger?

      That's because MTEs do not occur when doing single row inserts.

      Generally though I don't see why you would want to use logic like this. Just create a unique index and let the database do the checking for you.

      Delete
    4. yes that make sense.... Thanks for detailed reply :)

      I came across above trigger example where table already have some duplicate values and after certain point requirement has been raised for no more duplicates on the table.....
      so I thought that trigger is reading table before execution of dml transaction so that could be the reason I am not getting MTEs....

      Delete
  12. Thanks Steven for this nice article. Appreciated!!

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p