Oracle as Workaround of Mutating Tables

The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the “ORA-04091: table name is mutating, trigger/function may not see it” message.

Let’s have a look at the existing workarounds.

The first one, through the package, is ancient and seems to be effective, however, it takes much time to prepare and run it. The second one is simple and performed using compound triggers.

 

 

 

 

 

 

 

When Splinter mutates from a rat into a sensei, the painters will have to automatically turn into ninja. This trigger seems to be suited:

However, when updating the record:

The following error occurs:

ORA-04091: table SCOTT.TURTLES is mutating, trigger/function may not see it

Let’s delete this trigger:

The method 1: Using the package and the instruction-level trigger.

The method 2: Using compound DML triggers (available starting with Oracle 11g).

Let’s try the following:

Even if you faced a more complex case of mutation, you can use the above-mentioned idea as a workaround. In the instruction-level trigger, unlike the row-level trigger, no mutation occurs. You can use either variables (tags, latches, PL SQL tables) in an additional package, or variables that are global for all sections of the compound trigger, which is preferable starting with the version Oracle 11g. So, now you also know kung fu.

You may find additional information about triggers at: Compound DML Triggers

Feel free to add any comments.

Sergey Averin

Sergey Averin

Sergey is a developer of Delphi applications for large databases and server logic for such applications. He mainly focuses on such databases as Oracle and Firebird. Has a deep knowledge of SQL and PL\SQL. Sometimes Sergey writes programming related articles.
Sergey Averin

Latest posts by Sergey Averin (see all)

Sergey Averin

Sergey is a developer of Delphi applications for large databases and server logic for such applications. He mainly focuses on such databases as Oracle and Firebird. Has a deep knowledge of SQL and PL\SQL. Sometimes Sergey writes programming related articles.