Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating", and raises an error since Oracle should never return inconsistent data.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
example:
create table t(x numner); /
create or replace trigger t_af_trigg before insert into t for each row declare n integer; begin select count(*) into n from t; dbms_output.put_line('there are ' || n || ' rows in t'); end; /
Mutating" means "changing". A mutating table is a table
ReplyDeletethat is currently being modified by an update, delete, or
insert statement. When a trigger tries to reference a table
that is in state of flux (being changed), it is
considered "mutating", and raises an error since Oracle
should never return inconsistent data.
Another way this error can occur is if the trigger has
statements to change the primary, foreign or unique key
columns of the table off which it fires. If you must have
triggers on tables that have referential constraints, the
workaround is to enforce the referential integrity through
triggers as well.
example:
create table t(x numner);
/
create or replace trigger t_af_trigg
before insert into t
for each row
declare
n integer;
begin
select count(*) into n from t;
dbms_output.put_line('there are ' || n || ' rows in t');
end;
/
it will show an error as error maximum no of recursive sql levels
ReplyDelete