Ora-04091 table is mutating trigger/function may not see it

oracle

I have two tables say tab1, tab2
here tab1 is the parent table of tab2, Both have triggers enabled say tab1_trg, tab2_trg. Also in tab2_trg there is a SELECT on tab1.

Now I try to delete some records in tab1, it throws error:
ora-04091 table is mutating trigger/function may not see it
ora-04088 error during execution of trigger tab2_trg

I got the issue that I can not select a table that is being changed at the same time.

But I am not getting this error while constraints are disabled, error is occurring only when constraints are enabled.
Any idea ?

Thanks in Advance

Best Answer

This is normal if you have your FK constraint defined with the ON DELETE CASCADE option. When you delete from tab1, that will cause deletes from tab2 also because of the constraint definition. If you disable the constraint, rows from tab2 will not be deleted, the trigger on tab2 will not even run. Below is an example:

create table t1 (t1_id number, value varchar2(10), constraint pk_t1 primary key (t1_id));
create table t2 (t2_id number, t1_id number, constraint fk_t1 foreign key (t1_id) references t1(t1_id) on delete cascade);

insert into t1 select rownum, 'HELLO' from dual connect by level<= 5;
insert into t2 select rownum, rownum from dual connect by level <= 5;
commit;


create or replace trigger t2_trg 
after delete or insert or update
on t2
for each row
declare 
  n number;
begin
  select count(*) into n from t1;
end;
/

If you try to delete now, you will get the infamous table is mutating error:

SQL> delete from t1 where t1_id = 1;
delete from t1 where t1_id = 1
            *
ERROR at line 1:
ORA-04091: table BP.T1 is mutating, trigger/function may not see it
ORA-06512: at "BP.T2_TRG", line 4
ORA-04088: error during execution of trigger 'BP.T2_TRG'

Now with the constraint disabled:

SQL> alter table t2 disable constraint fk_t1;

Table altered.

SQL> delete from t1 where t1_id = 1;

1 row deleted.

Obviously, this works.

You should rethink/rewrite your trigger or constraint considering the above.

Related Question