Home » SQL & PL/SQL » SQL & PL/SQL » Doubt in for each row trigger
Doubt in for each row trigger [message #243866] Sun, 10 June 2007 05:34 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
The scenario is like this

SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 N                                                  NUMBER

SQL> desc emp1
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 L                                                  VARCHAR2(55)
 N                                                  NUMBER


CREATE OR REPLACE TRIGGER before_insert 
      before INSERT ON employees 
      FOR EACH ROW 
DECLARE 
      l VARCHAR(55); 
      n integer; 
BEGIN 
      l := 'Before'; 
      select count(*) into n from employees; 
      INSERT INTO emp1 VALUES(l, n); 
END; 
/ 
Trigger created.

CREATE OR REPLACE TRIGGER after_insert 
      after INSERT ON employees 
      FOR EACH ROW 
DECLARE 
      l VARCHAR(55); 
      n integer; 
BEGIN 
      l := 'After'; 
      select count(*) into n from employees; 
      INSERT INTO emp1 VALUES(l, n); 
END; 
/ 
Trigger created.

insert into employees values(10);
insert into employees values(10)
            *
ERROR at line 1:
ORA-04091: table APPS.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "APPS.AFTER_INSERT", line 6
ORA-04088: error during execution of trigger 'APPS.AFTER_INSERT'

SQL> alter trigger after_insert disable;

Trigger altered.

SQL> insert into employees values(10);

1 row created.


Why is that before_insert trigger which is also a for each row trigger is not mutating but after_insert which is a for each row trigger is mutating ?

Re: Doubt in for each row trigger [message #243869 is a reply to message #243866] Sun, 10 June 2007 09:32 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no mutating table error in your before insert trigger because you insert one value and so the table is not yet mutating when it reaches the before trigger: it is in the previous state. Oracle is smart enough to see that.
You get an error in the after trigger because the table is not in the same state as when the insert started.
If you try to "insert select" you'll get a mutating error in the before trigger because the table changes during the insert/select.

Regards
Michel
Previous Topic: about lockings in database
Next Topic: formating numbers
Goto Forum:
  


Current Time: Wed Dec 07 22:07:46 CST 2016

Total time taken to generate the page: 0.05348 seconds