Mutating table exception is not occurring

user_9724335 0
Viewed 101

I have created a trigger and was expecting a mutating table error in below case but didn't get one through normal insert but getting an error while inserting using a query. I am not sure which concept I am missing here.

drop table temp;
create table temp (id number,name varchar2(500),is_active number);

create or replace trigger temp_trg before insert  on temp
for each row
declare 
v_count number;
begin
 select count(1) into v_count from temp;
 update temp set is_active=0 where is_active=1 and id=:new.id;
end;
/

select * from temp;

insert into temp values (1,'xyz',1);
insert into temp values (1,'xyz',1);
insert into temp select 1,'xyz',1 from dual;
database-trigger plsql oracle
2 Answers

getting an error while inserting using a query.

Mutating table occurs when we query the table which owns the trigger. Specifically it happens when Oracle can't guarantee the outcome of the query. Now when you insert a single row into the table Oracle can predict the outcome of the query when the FOR EACH ROW trigger fires, because it's a single row.

But with an INSERT FROM query Oracle is confused: should the count be the final figure including all the rows selected by the query or just a rolling count? The answer seems straightforward in this case but it's easy to imagine other queries where the answer is not clear cut. Rather than evaluate each query on its predictability Oracle enforces a plain fiat and hurls ORA-04091 for all query driven inserts.

user_1463250

The restriction on Mutating Tables applies to all triggers that use FOR EACH ROW clause except when either of the following is true:

  • the trigger fires on BEFORE event, i.e. the data wasn't actually changed
  • it is known that only one row will be affected - INSERT ... VALUES is the only DML that meets this condition
user_65710200
Related