Mutating Trigger??

From: <shirushah_at_my-dejanews.com>
Date: Wed, 25 Nov 1998 02:56:56 GMT
Message-ID: <73frlh$pdu$1@nnrp1.dejanews.com>

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 "SYSTEM.TRIG_NEW_EMP_IN", line 2
ORA-04088: error during execution of trigger 'SYSTEM.TRIG_NEW_EMP_IN'

If I create it as a statment level trigger, i.e I remove the line 'for each row' then I get this error:

ERROR at line 1:
ORA-04082: NEW or OLD references not allowed in table level triggers

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

Thanks so much for your time & help.
Shiru


-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Nov 24 1998 - 20:56:56 CST