Xref: alice comp.databases.oracle.server:34639
Path: alice!news-feed.fnsi.net!dca1-hub1.news.digex.net!digex!feeder.qis.net!newsfeed-east.supernews.com!supernews.com!Supernews69!WReNclone!WReNphoon2.POSTED!WReN!not-for-mail
From: shirushah@hotmail.com ("Kamal Shah")
Newsgroups: comp.databases.oracle.server
Subject: Trigger Mutating
Organization: http://www.supernews.com: The World's Usenet/Discussions Start Here
Lines: 50
Message-ID: <y1W62.2104$ri1.1575601@WReNphoon2>
Date: Mon, 23 Nov 1998 12:57:28 -0800
X-Trace: WReNphoon2 912010462 10.0.3.176 (Wed, 25 Nov 1998 08:14:22 PDT)
NNTP-Posting-Date: Wed, 25 Nov 1998 08:14:22 PDT

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