Hi,
I would like to share, some details on “Mutating and Constraining table in Oracle”. Mutating error won’t occur when we go for statement level trigger. In general, it is said, that Mutating error will occur, if we refer the mutating table at the trigger body, in a row level trigger. There is an exceptional case, where the mutating error won’t occur, if we refer the mutating table in the trigger body. It is for before insert, at row level. The concept here is, if we perform a single row insert (by using Values clause), and if we refer the same table (the mutating table) at the trigger body, then we won’t get any mutating table error. Also the insert should be a single row one. So if you perform an multiple row insert(say : insert into tableA select * from table B), then Mutating error will occur. Also if multiple row insert statement, inserts only one record, we will get the mutating error. So in general, only for the before insert statement (with single row) at row level, won’t raise the mutating error.
Related Articles
The following table will explain you about the occurrences of mutating error.
Operation | Event / Trigger Type | Is Mutating | |
insert | before/statement-level | No | |
insert | after/statement-level | No | |
update | before/statement-level | No | |
update | after/statement-level | No | |
delete | before/statement-level | No | |
delete | after/statement-level | No | |
insert | before/row-level | Single row Multi-row | |
No Yes | |||
insert | after/row-level | Yes | |
update | before/row-level | Yes | |
update | after/row-level | Yes | |
delete | before/row-level | Yes | |
delete | after/row-level | Yes | |
Example :
Table Creation :
create table tab_trig(id number(3));
Trigger Creation:
create or replace trigger trig1
before insert on tab_trig
for each row
declare
v_mx tab_trig.id%type;
begin
select nvl(max(id),0) into v_mx from tab_trig;
dbms_output.put_line('Max Value : 'v_mx);
update tab_trig set id=:new.id+7;
select nvl(max(id),0) into v_mx from tab_trig;
dbms_output.put_line('Max Value After Incrementing Id by 7 : 'v_mx);
update tab_trig set id=:new.id-7;
select nvl(max(id),0) into v_mx from tab_trig;
dbms_output.put_line('Max Value After Decrementing Id by 7: 'v_mx);
end;
/
Sample Execution:
SQL> insert into tab_trig values(&id);
Enter value for id: 1
old 1: insert into tab_trig values(&id)
new 1: insert into tab_trig values(1)
Max Value : 0
Max Value After Incrementing Id by 7 : 0
Max Value After Decrementing Id by 7: 0
1 row created.
SQL> /
Enter value for id: 2
old 1: insert into tab_trig values(&id)
new 1: insert into tab_trig values(2)
Max Value : 1
Max Value After Incrementing Id by 7 : 9
Max Value After Decrementing Id by 7: -5
1 row created.
SQL> /
Enter value for id: 3
old 1: insert into tab_trig values(&id)
new 1: insert into tab_trig values(3)
Max Value : 2
Max Value After Incrementing Id by 7 : 10
Max Value After Decrementing Id by 7: -4
1 row created.
