PeopleSoft Learning

I would like to share, few more details on “Mutating and Constraining table in Oracle” written by Sasi. 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.

The following table will explain you about the occurrences of mutating error.

-----------------------------------------------------------------

OPERATION TYPE 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.

Thanks

Vivek Member - Cloud Nine