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 Solutions

NOT getting a mutating table error in trigger

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
  2    col1 number,
  3    col2 number
  4  )
  5  ;

Table created

SQL> create or replace trigger TR_TB_TR_TEST
  2  before insert on TB_TR_TEST
  3  for each row
  4  begin
  5    SELECT max(col1) + 1 INTO :NEW.col1
  6      FROM TB_TR_TEST;
  7    UPDATE TB_TR_TEST SET col2 = 5000;
  8  end;
  9  /

Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);

1 row inserted

SQL> insert into TB_TR_TEST(col1, col2) values(3,5);

1 row inserted

SQL> commit;

Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
  2    select 1, 2
  3      from dual;

insert into TB_TR_TEST(col1, col2)
  select 1, 2
    from dual

ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'

Error in trigger: table is mutating

Replace

select portfoliono into mportfoliono from sipholder where accno=:new.accno;

with

mportfoliono := new.portfoliono;

no need to use a SELECT for values of the updated row.

Best Answer

Related Solutions

NOT getting a mutating table error in trigger

Error in trigger: table is mutating

Related Question