SQL Error: ORA-04091: table is mutating, trigger/function may not see it

Question

I am getting the 'SQL Error: ORA-04091: table is mutating, trigger/function may not see it" when i am updating the data in the table.

DDL:

CREATE TABLE STUDENT_DIM (STUD_ID NUMBER,CURR_STUD_NAME VARCHAR2(30),PREV_STUD_NAME VARCHAR2(30), CURR_DOJ DATE, PREV_DOJ DATE, CURRR_DEPT_NAME VARCHAR2(30),PREV_DEPT_NAME VARCHAR2(30));
INSERT INTO STUDENT_DIM(STUD_ID,CURR_STUD_NAME,CURR_DOJ,CURRR_DEPT_NAME) VALUES(1,'VINOTH','01-AUG-2017','CSE');
INSERT INTO STUDENT_DIM(STUD_ID,CURR_STUD_NAME,CURR_DOJ,CURRR_DEPT_NAME) VALUES(2,'SURESH','11-SEP-2017','ECE');
INSERT INTO STUDENT_DIM(STUD_ID,CURR_STUD_NAME,CURR_DOJ,CURRR_DEPT_NAME) VALUES(3,'RAM','01-MAY-2018','IT');

Trigger:

CREATE OR REPLACE TRIGGER STUDENT_DIM_HIST BEFORE UPDATE ON STUDENT_DIM FOR EACH ROW
BEGIN
CASE WHEN UPDATING('CURR_STUD_NAME') THEN UPDATE STUDENT_DIM SET PREV_STUD_NAME = :OLD.CURR_STUD_NAME WHERE STUD_ID = :OLD.STUD_ID;
     WHEN UPDATING('CURR_DOJ')       THEN UPDATE STUDENT_DIM SET PREV_DOJ = :OLD.CURR_DOJ WHERE STUD_ID = :OLD.STUD_ID;
     WHEN UPDATING('CURRR_DEPT_NAME') THEN UPDATE STUDENT_DIM SET PREV_DEPT_NAME = :OLD.CURRR_DEPT_NAME WHERE STUD_ID = :OLD.STUD_ID;
END CASE;
END;

Update:

UPDATE STUDENT_DIM SET CURR_STUD_NAME = 'RAM KUMAR' WHERE STUD_ID = 3;

Answer 1

Your trigger tries to reference the table that owns the trigger, during manipulation(update) of the same table, and the problem raises due to this.

Use some assignments instead, as in the following :

CREATE OR REPLACE TRIGGER STUDENT_DIM_HIST BEFORE UPDATE ON STUDENT_DIM FOR EACH ROW
BEGIN
CASE WHEN UPDATING('CURR_STUD_NAME')  THEN :NEW.PREV_STUD_NAME := :OLD.CURR_STUD_NAME;
     WHEN UPDATING('CURR_DOJ')        THEN :NEW.PREV_DOJ       := :OLD.CURR_DOJ;
     WHEN UPDATING('CURRR_DEPT_NAME') THEN :NEW.PREV_DEPT_NAME := :OLD.CURRR_DEPT_NAME;
END CASE;
END;

Answer 2

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.

1. 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.

2. 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.

3. Re-work the trigger syntax - Avoid mutating tables with a combination of row-level and statement-level triggers.

4. 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.