Mutating table error
----------------------



Case 1: function
-----------------
SQL> create table t1_ (c1 number);

Table created.

SQL> insert into t1_ values(1);

1 row created.

SQL> insert into t1_ values(2);

1 row created.

SQL> insert into t1_ values(3);

1 row created.

SQL> create or replace function getmax_ return int as
  2  i_max number:=0;
  3  begin
  4  select max(c1) into i_max from t1_;
  5  return(i_max) ;
  6  end;
  7  /

Function created.

SQL> create or replace procedure ins_  as
  2  i_num number:=0;
  3  begin
  4  insert into t1_ select getmax_ from dual;
  5  end;
  6  /

Procedure created.

SQL> exec ins_;
BEGIN ins_; END;

*
ERROR at line 1:
ORA-04091: table JYANG.T1_ is mutating, trigger/function may not see it
ORA-06512: at "JYANG.GETMAX_", line 4
ORA-06512: at "JYANG.INS_", line 4
ORA-06512: at line 1


SQL> 

drop table t1_;
drop function getmax_;
drop procedure ins_;



Case 2: trigger
--------------------
Solution is to not use 'row trigger' but use statement trigger. To update a mutating table,
use temporary table or PL/SQL table.



SQL> create table t1_ (c1 number);

Table created.

SQL> insert into t1_ values(1);

1 row created.

SQL> insert into t1_ values(2);

1 row created.

SQL> 
SQL> create or replace trigger trg1_ 
  2  before insert or update or delete
  3  on t1_
  4  for each row
  5  declare
  6  icount number;
  7  begin
  8    select count(1) into icount from t1_;
  9  end;
 10  /

Trigger created.

SQL> insert into t1_ values(3);

1 row created.

SQL> delete from t1_;
delete from t1_
            *
ERROR at line 1:
ORA-04091: table JYANG.T1_ is mutating, trigger/function may not see it
ORA-06512: at "JYANG.TRG1_", line 4
ORA-04088: error during execution of trigger 'JYANG.TRG1_'

SQL> update t1_ set c1=c1+1;
update t1_ set c1=c1+1
       *
ERROR at line 1:
ORA-04091: table JYANG.T1_ is mutating, trigger/function may not see it
ORA-06512: at "JYANG.TRG1_", line 4
ORA-04088: error during execution of trigger 'JYANG.TRG1_'


SQL>