Mutating Trigger

Oracle PL/SQL Mutating Triggers

oracle_pl/sql_mutating_triggers

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.

A mutation table is defined as a table that is changing. But in dealing with triggers, it is a table that has the possibility of changing.

What this means to a trigger is that if the trigger reads a table, it can not change the table that it read from. This does not impact the exclusive use of :OLD and :NEW.

It says that if the trigger reads the table (such as using a SELECT query), that changes (even using :NEW) will fail. This can also happen when a trigger on a parent table causes an insert on a child table referencing a foreign key.

If we are trying this oracle server returns mutating error oracle-4091: table is mutating.

This error is called mutating error, and this trigger is called mutating trigger, and table is called mutating table.

Mutating errors are not occurred in statement level trigger because through these statement level trigger when we are performing DML operations, data automatically committed into the database, whereas in row level trigger when we are performing transaction, data is not committed and also again we are reading this data from the same table then only mutating errors are occurred.

Demonstration of Mutating Table Error

Create a row level trigger on EMP table.

CREATE OR REPLACE TRIGGER emp_sal_trg
AFTER INSERT OR UPDATE
ON emp
FOR EACH ROW
DECLARE
    l_max_allowed emp.sal%TYPE;
BEGIN
    SELECT MIN(sal) * 10 INTO l_max_allowed FROM emp;
    IF l_max_allowed < :NEW.sal
    THEN 
        UPDATE emp SET sal = l_max_allowed
        WHERE empno= :NEW.empno;
    END IF;
END emp_sal_trg;
SELECT MIN(sal) FROM EMP;
 
MIN(SAL) ------- 800

Now update the sal in emp table using below block.

UPDATE emp SET sal = 1100 WHERE empno= 7944;
 
ORA-04091: table DE.EMP is mutating, trigger/function may not see it ORA-06512: at "DE.EMP_SAL_TRG", line 4 ORA-04088: error during execution of trigger 'DE.EMP_SAL_TRG'

Avoiding Mutating triggers

  • Don't use triggers -
    The best way to avoid the mutating table error is not to use triggers. While the object-oriented Oracle provides "methods" that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary.
  • Use statement-level trigger
  • Use an "after" or "instead of" trigger -
    If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the currency issues associated with a mutating table. For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating.
  • Use autonomous transactions -
    You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.
  • Using compound trigger -
    CREATE OR REPLACE TRIGGER emp_sal_trg
    FOR INSERT OR UPDATE
    ON emp
    COMPOUND TRIGGER
    TYPE r_emp_type IS RECORD(
    empno  emp.empno%TYPE,
    sal emp.sal%TYPE
    );
    TYPE t_emp_type IS TABLE OF r_emp_type INDEX BY PLS_INTEGER;
        t_emp t_emp_type;
    
        AFTER EACH ROW IS
        BEGIN
            t_emp(t_emp.COUNT+1).empno :=:NEW.empno;
            t_emp(t_emp.COUNT+1).sal :=:NEW.sal;
        END AFTER EACH ROW;
        AFTER STATEMENT IS
            l_max_allowed emp.sal%TYPE;
        BEGIN
        SELECT MIN(sal) * 10 INTO l_max_allowed FROM emp;
    
        FOR idx IN 1.. t_emp.COUNT
            LOOP
                IF l_max_allowed < t_emp(idx).sal
                THEN
                    UPDATE emp SET sal = l_max_allowed
                    WHERE empno = t_emp(idx).empno;
                END IF;
            END LOOP;
        END AFTER STATEMENT;
    END emp_sal_trg;
    

    Now run the below statement again

    UPDATE emp SET sal = 800 WHERE empno = 7944;
    

There are exceptions about views being inherently updateable. The exceptions (or restrictions) include views that use aggregate functions; group functions; use of the DISTINCT keyword; use of GROUP BY, CONNECT BY or START WITH clauses; and use of some joins. In many cases, use of the INSTEAD-OF trigger feature allows you to work around these restrictions.

INSTEAD-OF triggers are also useful for Forms developers because forms are commonly based on views. The INSTEAD-OF trigger, being a "real" trigger, and not a true form trigger, is stored on the server."


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.