Re: trigger - multiple columns

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 27 Nov 1998 14:24:21 GMT
Message-ID: <3661b5e4.10401336@192.86.155.100>

A copy of this was sent to shirushah_at_hotmail.com (if that email address didn't require changing) On Thu, 26 Nov 1998 20:08:34 GMT, you wrote:

>Here is data:
>
>emp
>
>enum ename deptno sal
>1245 TOM 20 4000
>3456 JOHN 10 3000
>
>my_dept
>
>deptno dname
>20 Marketing
>10 Sales
>
>
>I need to update MY_DEPT after an employee in EMP is transferred to another
>department or has a change in salary. How can I do this in one trigger?

Something like the following trigger will maintain the tot_sal column in a dept table for inserts, updates and deletes on an emp table (not just updates, not just inserts, not just deletes but any one or all of the three):

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

>How does one coordinate such conditional changes? I have this:
>create or replace trigger trig_emp_update
>after update of deptno
>or update of sal
>on emp
>for each row
>begin
>update my_dept
>set tot_sal = NVL(tot_sal,0) + :new.sal
>where deptno = :new.deptno;
>
>update my_dept
>set tot_sal = NVL(tot_sal,0) - :old.sal
>where deptno = :old.deptno;
>
>end trig_emp_update;
>
>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 Fri Nov 27 1998 - 08:24:21 CST