Re: Mutating Trigger??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 25 Nov 1998 13:57:00 GMT
Message-ID: <365c09e6.1388246@192.86.155.100>

A copy of this was sent to shirushah_at_my-dejanews.com (if that email address didn't require changing) On Wed, 25 Nov 1998 02:17:42 GMT, you 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;
>

why read the EMP table in the trigger at all? You have all of the information you need with the :old and :new values.

Something like the following trigger will maintain the tot_sal column in a dept table for inserts, updates and deletes on an emp table:

SQL> create or replace trigger MAINTAIN_DEPT_SALARIES   2 after INSERT OR UPDATE OR DELETE ON EMP   3 for each row
  4 begin

  5     if ( inserting OR updating ) then
  6        update dept set tot_sal = tot_sal + :new.sal
  7         where deptno = :new.deptno;
  8     end if;
  9     if ( deleting OR updating ) then
 10        update dept set tot_sal = tot_sal - :old.sal
 11         where deptno = :old.deptno;
 12     end if;

 13 end;
 14 /
Trigger created.

So, now you can:

SQL> select * from emp;

ENAME SAL DEPTNO
---------- ---------- ----------

SMITH             800         20
ALLEN            1600         30
WARD             1250         30
JONES            2975         20
MARTIN           1250         30
BLAKE            2850         30
CLARK            2450         10
SCOTT            3000         20
KING             5000         10
TURNER           1500         30
ADAMS            1100         20
JAMES             950         30
FORD             3000         20
MILLER           1300         10

14 rows selected.

SQL> select * from dept;

    DEPTNO DNAME TOT_SAL
---------- -------------- ----------

        10 ACCOUNTING           8750
        20 RESEARCH            10875
        30 SALES                9400
        40 OPERATIONS              0

Lets transfer everyone to a new department:

SQL> update emp set deptno = decode( deptno, 10, 20, 20, 30, 30, 40 );

14 rows updated.

SQL> select * from emp;

ENAME SAL DEPTNO
---------- ---------- ----------

SMITH             800         30
ALLEN            1600         40
WARD             1250         40
JONES            2975         30
MARTIN           1250         40
BLAKE            2850         40
CLARK            2450         20
SCOTT            3000         30
KING             5000         20
TURNER           1500         40
ADAMS            1100         30
JAMES             950         40
FORD             3000         30
MILLER           1300         20

14 rows selected.

SQL> select * from dept;

    DEPTNO DNAME TOT_SAL
---------- -------------- ----------

        10 ACCOUNTING              0
        20 RESEARCH             8750
        30 SALES               10875
        40 OPERATIONS           9400

Lets hire someone and fire someone:

SQL> insert into emp values ( 'New', 5000, 10 );

1 row created.

SQL> delete from emp where deptno = 20 and rownum = 1;

1 row deleted.

SQL> select * from emp;

ENAME SAL DEPTNO
---------- ---------- ----------

SMITH             800         30
ALLEN            1600         40
WARD             1250         40
JONES            2975         30
MARTIN           1250         40
BLAKE            2850         40
SCOTT            3000         30
KING             5000         20
TURNER           1500         40
ADAMS            1100         30
JAMES             950         40
FORD             3000         30
MILLER           1300         20
New              5000         10

14 rows selected.

SQL> select * from dept;

    DEPTNO DNAME TOT_SAL
---------- -------------- ----------

        10 ACCOUNTING           5000
        20 RESEARCH             6300
        30 SALES               10875
        40 OPERATIONS           9400

>-- 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
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Nov 25 1998 - 07:57:00 CST