PLSQL入门与精通（第42章：利用触发器随时保持导出列的值的同步）

周六，开了一下午会。晚上闲暇继续。

前几次我们对触发器进行了深入的学习，这次我们学习触发器另外一个例子：“使用触发器及时更新导出列的值”。
导出列是指可以从其他数据表总导出的列，这个列可以使另外一张表的合计值，平均值。这个合计值、平均值要始终和
原表数据保持一直。

例如，有2张表，部门表、员工表，如果员工表有部门和工资列，部门表中有该部门员工的工资合计列，则该列为员工表的导出列。如果是设置导出列的时候，如果原数据被更新，导出列也需要相应地更新。
也就是说，员工表变动的情况下，部门工资合计也会发生变化的，部门表的工资合计列也需要随之更新。

如果用触发器自动化了处理，导出列的值总是能保持与原始数据实时同步的状态。

具体例子：

在部门表（DEPT）上设置工资合计列（GSAL）。

SQL> ALTER TABLE DEPT ADD ( GSAL NUMBER); --设置为导出列

表格已修改。

确认表格定义。

SQL> DESC DEPT
名称 NULL 类型
----------------------------------------------------------------------------
DEPTNO NOT NULL NUMBER(2) ←部门NO
DNAME VARCHAR2(14) ←部门名
LOC VARCHAR2(13) ←部门所在地
GSAL NUMBER ←工资合计列

DEPT表中追加了工资合计列（GSAL）。

接下来把GSAL列设置为：EMP表的各部门的工资合计值。

SQL> UPDATE DEPT
2 SET GSAL = (SELECT SUM(SAL) FROM EMP
3 WHERE DEPTNO = DEPT.DEPTNO);

4行已更新。

查询一下DEPT表：

SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC GSAL

10 ACCOUNTING NEW YORK 7450
20 RESEARCH DALLAS 12375
30 SALES CHICAGO 9400
40 OPERATIONS BOSTON

GSAL列设定了值。

另外，部门40里边没有员工，所以GSAL列值为NULL。

GSAL列的值是员工表（EMP）表中工资的合计值，是当前值，但是如果EMP表发生变化，这个值不是最新的值，不能和EMP表的工资实时同步。

因此，需要设计一个实时同步的触发器。

详细如下:

SQL> CREATE OR REPLACE TRIGGER EMP_SAL_SUM
2 //
3. 触发器：员工表变动后，部门表工资合计列（GSAL）也要同步
4 //
5 AFTER
6 INSERT OR DELETE OR UPDATE OF SAL, DEPTNO
7 ON EMP
8 FOR EACH ROW
9 BEGIN

10 //
11 --EMP表的INSERT时，使用该行的：NEW.SAL，合计到相关部门的
12 --GSAL值里边
13 //
14 IF INSERTING THEN
15 UPDATE DEPT
16 SET GSAL = NVL(GSAL,0) + :NEW.SAL
17 WHERE DEPTNO = :NEW.DEPTNO;

18 //
19 --EMP表到DELETE时，相关部门的合计值里边减去
20 --该行的：OLD.SAL的值
21 //
22 ELSIF DELETING THEN
23 UPDATE DEPT
24 SET GSAL = NVL(GSAL,0) - :OLD.SAL
25 WHERE DEPTNO = :OLD.DEPTNO;

26 ELSE
27 //
28 --对EMP表进行UPDATE时，如果部门编号未发生变化
29 --工资合计值GSAL工资进行变化：NEW与OLD之差加到合计值里边
30 //
31 IF :NEW.DEPTNO = :OLD.DEPTNO THEN
32 UPDATE DEPT
33 SET GSAL = NVL(GSAL,0) + :NEW.SAL - :OLD.SAL
34 WHERE DEPTNO = :NEW.DEPTNO;

35 //
36 --如果对EMP表的部门编号进行更新时
37 --工资合计值：原部门的值-：OLD.SAL 新部门的工资合计值+新部门的值：NEW.SAL
38 //
39 ELSE
40 UPDATE DEPT
41 SET GSAL = NVL(GSAL,0) - :OLD.SAL
42 WHERE DEPTNO = :OLD.DEPTNO;

43 UPDATE DEPT
44 SET GSAL = NVL(GSAL,0) + :NEW.SAL
45 WHERE DEPTNO = :NEW.DEPTNO;
46 END IF;
47 END IF;
48 END;
49 /

触发器已创建。

让我们确认一下结果。

SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC GSAL

10 ACCOUNTING NEW YORK 7450
20 RESEARCH DALLAS 12375
30 SALES CHICAGO 9400
40 OPERATIONS BOSTON

部门10的工资合计是7450。

另外，假如有一个公司职员编号7782所属部门是10，叫CLARK。
SQL> SELECT EMPNO, ENAME ,SAL ,DEPTNO FROM EMP WHERE EMPNO = 7782;
EMPNO ENAME SAL DEPTNO
----------------------------------------------------
7782 CLARK 2450 10

如果员工编号7782的CLARK的工资增加50，那么根据触发，部门10的工资合计也应该增加50。

我们试试看：
SQL> UPDATE EMP SET SAL = SAL + 50 WHERE EMPNO = 7782;
一行已更新。

查询部门表：
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC GSAL
-----------------------------------------------------------------------
10 ACCOUNTING NEW YORK 7500←增加了50
20 RESEARCH DALLAS 12375
30 SALES CHICAGO 9400
40 OPERATIONS BOSTON

按部门统计现在EMP表的工资。
SQL> SELECT DEPTNO, SUM(SAL) FROM EMP
2 GROUP BY DEPTNO
3 ORDER BY DEPTNO;

DEPTNO SUM(SAL)
--------------------------------
10 			7500
20	 		12375
30 			9400

和部门表的工资合计列是实时同步的。

如果触发有效，部门表的工资合计列应该经常实时同步。
这样使用触发器可以自动维护导出列，很方便。

但是，如果有这样的触发器的话，在更新员工表的时候，一行一行的触发器就会被启动，所以负担也会变高，性能变差。
设计的时候好处坏处要考虑好之后，再决定是否应该实施该触发器。

这就是触发器的新的使用例子。本次到此为止。谢谢。