Re: Mutating Trigger??

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 25 Nov 1998 12:57:57 GMT
Message-ID: <73gusl$fga$4@news02.btx.dtag.de>

Hi,

try:

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

        update my_dept
        set tot_sal = NVL(tot_sal,0) + :new.sal
        where deptno = :new.deptno;

end trig_new_emp_in;

Won't this satisfy your needs?

shirushah_at_my-dejanews.com schrieb:
>
> 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
> &#137;
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Wed Nov 25 1998 - 06:57:57 CST