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.
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'
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."
Admin on Nov 10, 2019 at 12:03 am
If you like dEexams.com and would like to contribute, you can write your article here or mail your article to email@example.com . See your article appearing on the dEexams.com main page and help others to learn.