Re: Trigger Mutating

On Mon, 23 Nov 1998 12:57:28 -0800, shirushah_at_hotmail.com ("Kamal Shah") wrote:

>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

Okay,
If you try 'before' insert , on creating the trigger, you should solve the problem. You can't issue a select from the same table the trigger was designed to fire on, with the clause 'after'.

Massimiliano Panarese Received on Mon Nov 30 1998 - 17:27:57 CST