hi everybody
can anyone explain me what is mutating trigger.
Printable View
hi everybody
can anyone explain me what is mutating trigger.
Hi, a mutating trigger occurs in oracle when the table that originally fired the triggering event is being accessed in the body of the trigger code, directly or implicitly in a procedure, or in a nested trigger called from the trigger body.
This action is forbidden because the table is in middle of a transaction, and referencing the same table again in the middle of the updating action causes the trigger to mutate. For example:
sql>create table t1 (x int);
sql>create table t2 (x int);
sql>insert into t1 values (1);
sql>select * from t1;
sql>select * from t2;
sql>create or replace trigger t_trigger
after insert on t1
for each row
declare
i pls_integer;
begin
select count(*) into i from t1;
insert into t2 values (i);
end; /
sql>insert into t1 values (1);
sql>select count(*) from t1;
sql>select count(*) from t2;
if u run this one u will get the mutating error, ora-04091.
Cheers, regards
rsy