1. Create a simplest table
2. add a before insert trigger which just counts the table rows
3. insert a row and ORA-04091 table is mutating, trigger/function may not see it is thrown:

-- 1. create table
create table cdt (a number)

-- 2. create before insert trigger
CREATE OR REPLACE TRIGGER TRIG_CDT
  before insert on cdt
  for each row
declare
  cnt number;
begin
  select count(*)
    into cnt
    from cdt;
end TRIG_CDT;

-- 3. insert
insert into cdt(a)
select 1 from dual

the above throws:

ORA-04091: table CDT is mutating, trigger/function may not see it
ORA-06512: at "TRIG_CDT", line 4
ORA-04088: error during execution of trigger 'TRIG_CDT'

However, if insert using values instead of select it works:

insert into cdt(a) values(1)

CodePudding user response：

Mutating tables are very odd problems. The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger. In this case you can make it work if you declare your trigger as an autonomous transaction separating the table from the transaction.
More about the problem here : http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm
More about how to avoid it here : https://oracle-base.com/articles/9i/mutating-table-exceptions

CREATE OR REPLACE TRIGGER TRIG_CDT
  before insert on cdt
  for each row
declare
  cnt number;
  pragma autonomous_transaction;
begin
  select count(*)
    into cnt
    from cdt;
end TRIG_CDT;