CODEWITHSUNDEEP
oracle-databaseplsqltriggers
J M
J M

Reputation: 77

Mutating table error while executing row level after update trigger

I have a use case where I need to place a trigger on a table(TABLE1) whenever its column COL1 is set to 1. When this trigger executes, it should update a column of another table(TABLE2). This column gets it value from a complex query which takes input from TABLE1. Here is my trigger code:(Compiled Successfully)

CREATE OR REPLACE TRIGGER MY_TRIGGER
AFTER UPDATE ON TABLE1
FOR EACH ROW
WHEN (NEW.COL1='1')
DECLARE
    Var1 Number;
    Var2 Number;
BEGIN
    SELECT COL3,COL2 INTO Var1,Var2 FROM TABLE1;
    UPDATE TABLE2 T2
    SET T2.COL1 = (Complex query joining multiple tables which takes Var1 as input and gives one column value as output- This value has to be set to T2.COL1)
    WHERE T2.COL2 = Var2;
END;

In my application, TABLE1.COL1 keeps changing at run time due to different activities and I have to capture it and update my TABLE2.COL1. In this trigger there is no update being performed on TABLE1 on which trigger is defined. Still I am getting below mutating error whenever an update happens on TABLE1 at runtime. Also when my trigger is enabled the application level activities are also not letting the TABLE1 to update and giving this error. Please help me to resolve this issue.

Getting SQL Error: ORA-04091: table DBUSERNAME.TABLE1 is mutating, trigger/function may not see it
ORA-04088: error during execution of trigger 'DBUSERNAME.MY_TRIGGER'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it

Upvotes: 0

Views: 955

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

Don't select columns from the table you're modifying, it is mutating (as you already know). Use :new!

Here's an example based on Scott's EMP table.

This is a table I'll use as a target of UPDATE statement in the trigger:

SQL> create table test as select empno, sal from emp;

Table created.

Trigger: note lines #6 and #7:

SQL> create or replace trigger my_trigger
  2    after update on emp
  3    for each row
  4  begin
  5    update test t2 set
  6      t2.sal = :new.sal
  7      where t2.empno = :new.empno;
  8  end;
  9  /

Trigger created.

Testing:

SQL> select empno, sal from emp where ename = 'KING';

     EMPNO        SAL
---------- ----------
      7839       5000

SQL> update emp set sal = 5001 where ename = 'KING';

1 row updated.

SQL> select * from test where empno = 7839;

     EMPNO        SAL
---------- ----------
      7839       5001

SQL>

Upvotes: 4

Related Questions