Re: Mutating Trigger??

Use before insert on each row instead. That is the only exception to the Mutating Table Error for row level. (Mutating Table apply to after update, delete,insert and before update,delete on row level, but not before insert on row level).

Good luck!

>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 - 21:48:51 CST