Oracle trigger muating error

Question

CREATE OR REPLACE TRIGGER Testtriger
    after insert ON table2 referencing new as new old as old
    for each row
declare 
    flagtemp varchar2(1);
begin
    select flag into flagtemp from table2 where docid = :new.docid;
    --if :new.cardtypeflag = 'T' then
    update table1 set col1 = 'F' , col2= 'T', inactive = 'T', col3 = 'T' 
    where    tabid = :new.docid;
    --end if;
end;
/

This trigger is giving mutating error, please help to fix it.

http://dba.stackexchange.com/questions/5432/what-are-the-causes-and-solutions-for-mutating-table-errors — Gurwinder Singh, Jan 25 '17 at 05:24
Possible duplicate of [ORACLE After update trigger: solving ORA-04091 mutating table error](http://stackoverflow.com/questions/6915325/oracle-after-update-trigger-solving-ora-04091-mutating-table-error) — Frank Schmitt, Jan 25 '17 at 07:50

score 1 · Answer 1 · answered Jan 25 '17 at 07:48

Your trigger contains a select executed against the table which has the trigger:

select flag into flagtemp from table2 where docid = :new.docid;

Oracle database operations require predictable state. The outcome of this select is unpredictable because you are in mid-transaction: the data has not been applied yet. So the trigger fails with the mutation error.

In this case it seems all you need to do is

flagtemp := :new.flag;

Or, even better, do away with the assignment, as you don't use the local variable in the rest of the trigger.

Oracle trigger muating error

1 Answers1