Trigger Mutating

From: Kamal Shah <shirushah_at_hotmail.com>
Date: Mon, 23 Nov 1998 12:57:28 -0800
Message-ID: <y1W62.2104$ri1.1575601@WReNphoon2>

This trigger should fire after a new employee is inserted into the EMP table. The trigger should update the tot_sal data in the MY_DEPT table for the inserted department only.

Info in the table:
emp - ename, sal, deptno
Tom, 1000, 15
John, 2000, 15
Don, 1500, 10

my_dept - dname, deptno, tot_sal
Sales, 15, 3000
R&D, 10, 1500

This is what I have done:

SQL>
create or replace trigger trig_new_emp_in after insert on emp
for each row
begin

   	update my_dept
  	set tot_sal =
          	   (select sum(sal)
                    from emp
                    where deptno = :new.deptno)
   	where deptno = :new.deptno;

end trig_new_emp_in;

This is my insert into the emp table which will fire the trigger SQL> insert into emp (empno, ename, sal, deptno) values (6969, 'Pam', 1000, 10)

--This is the error
ERROR at line 1:

ORA-04091: table SYSTEM.EMP is mutating, trigger/function may not see it
ORA-06512: at %22SYSTEM.TRIG_NEW_EMP_IN%22, line 2
ORA-04088: error during execution of trigger 'SYSTEM.TRIG_NEW_EMP_IN'

What am I doing wrong, as this is not working?

Thanks so much for your time & help.
Shiru

-**** Posted from Supernews, Discussions Start Here(tm) ****- http://www.supernews.com/ - Host to the the World's Discussions & Usenet Received on Mon Nov 23 1998 - 14:57:28 CST