Oracle12C--复合触发器(五十五)

• 定义
  • 复合触发器既是表级触发器，又是行级触发器；
    • 对于不同级别的触发器，如果要在一张表上完成表级触发器与行级触发器需要编写4个触发器才可以；
    • 通过复合触发器，只需要一个触发器就可以定义全部4个功能；
  • 复合触发器可以捕获的4个操作事件：
    • 触发执行语句之前（BEFORE STATEMENT）；
    • 触发语句中的每一行发生变化之前（BEFORE EACH ROW）；
    • 触发语句中的每一行发生变化之后（AFTER EACH ROW）；
    • 触发执行语句之后（AFTER STARTEMENT）；
  • 复合触发器就像一个多线程的进程操作，可以同时处理多个不同的事件；
    • 在复合触发器中的最小实现要求定义至少一个事件处理块，并且只有DML能触发复合触发器工作；
• 创建语法

CREATE [OR REPLACE] TRIGGER 触发器名称
FOR [INSERT | UPDATE | UPDATE OF 列名称 [,列名称,....] | DELETE] ON表名称
COMPOUND TRIGGER
[BEFORE STATEMENT IS --语句执行前触发（表级）
[声明部分;]
BEGIN
程序主体部分;
END BEFORE STATEMENT;]
[BEFORE EACH ROW IS --语句执行前触发（行级）
[声明部分;]
BEGIN
程序主体部分;
END BEFORE EACH ROW;]
[AFTER STATEMENT IS --语句执行后触发（表级）
[声明部分;]
BEGIN
程序主体部分;
END AFTER STATEMENT;]
[AFTER EACH ROW IS --语句执行后触发（行级）
[声明部分;]
BEGIN
程序主体部分;
END AFTER EACH ROW;]
END;
/

• 举个栗子
  • 示例1：验证复合触发器

CREATE OR REPLACE TRIGGER compound_trigger

FOR INSERT OR UPDATE OR DELETE ON dept

COMPOUND TRIGGER

BEFORE STATEMENT IS --语句执行前触发（表级）

BEGIN

DBMS_OUTPUT.put_line('1、BEFORE STATEMENT .') ;

END BEFORE STATEMENT ;

BEFORE EACH ROW IS --语句执行前触发（行级）

BEGIN

DBMS_OUTPUT.put_line('2、BEFORE EACH ROW .') ;

END BEFORE EACH ROW ;

AFTER STATEMENT IS --语句执行后触发（表级）

BEGIN

DBMS_OUTPUT.put_line('3、AFTER STATEMENT .') ;

END AFTER STATEMENT ;

AFTER EACH ROW IS --语句执行后触发（行级）

BEGIN

DBMS_OUTPUT.put_line('4、AFTER EACH ROW .') ;

END AFTER EACH ROW ;

END ;

/

• 向dept表中增加一条新数据

INSERT INTO dept(deptno,dname,loc) VALUES (99,'MLDNJAVA','北京') ;

• 
• 复合触发器与之前的不同的DML触发器（表级触发器+行级触发器）执行顺序相同；
  • 执行更新前表级触发器：BEFORE STATEMENT；
  • 执行更新前行级触发器：BEFORE EACH ROW；
  • 执行更新后行级触发器：AFTER EACH ROW；
  • 执行更新后表级触发器：AFTER STATEMENT；

• 示例2：在dept表上定义一个复合触发器，如果其执行的是增加操作，且增加的部门名称或位置没有填写时，将部门名称设置为mldnjava，位置设置为"中国"
  • 分析：
    • 如果要在更新前自动设置内容，则应该使用BEFORE EACH ROW事件操作；
    • 可以在此触发器中，使用IS NULL分别判断设置的dname或loc是否为空；
  • 定义复合触发器

CREATE OR REPLACE TRIGGER compound_trigger

FOR INSERT OR UPDATE OR DELETE ON dept

COMPOUND TRIGGER

BEFORE EACH ROW IS --语句执行前触发（行级）

BEGIN

IF INSERTING THEN

IF :new.dname IS NULL THEN

:new.dname := 'MLDNJAVA' ;

END IF ;

IF :new.loc IS NULL THEN

:new.loc := '中国' ;

END IF ;

END IF ;

END BEFORE EACH ROW ;

END ;

/

• 增加一条部门信息

INSERT INTO dept(deptno) VALUES (99) ;

COMMIT ;

• 查询更新后的dept表记录

SELECT * FROM dept ;

• 

• 示例3：对多个事件的操作。
  • 现在对emp表定义一个触发器，此触发器可以完成如下功能：
    • 在周末时间不允许更新emp表数据；
    • 在更新数据时，要求将所有增加的数据自动变为大写；
    • 重新完成之后，新增雇员的工资不得高于公司的平均工资；
  • 分析：
    • 周末时间不允许更新表，则应该在表级更新前触发，在此触发器中，使用SYSDATE取得当前的一周时间数，如果不满足更新条件，就抛出一个异常；
    • 如果要将所有的字母自动变为大写，则需要在行更新前触发；
    • 可以使用AVG()函数计算出公司的平均工资，之后判断新增雇员的工资（:new.sal）是否高于此平均值
  • 代码：

CREATE OR REPLACE TRIGGER emp_compound_trigger

FOR INSERT OR UPDATE OR DELETE ON emp

COMPOUND TRIGGER

BEFORE STATEMENT IS --周末不允许更新

v_currentweak VARCHAR2(20) ;

BEGIN

SELECT TO_CHAR(SYSDATE,'day') INTO v_currentweak FROM dual ;

IF TRIM(v_currentweak) IN ('星期六' , '星期日') THEN

RAISE_APPLICATION_ERROR(-20008,'在周末不允许更新emp数据表！') ;

END IF ;

END BEFORE STATEMENT ;

BEFORE EACH ROW IS

v_avgSal emp.sal%TYPE ;

BEGIN

IF INSERTING OR UPDATING THEN

:new.ename := UPPER(:new.ename) ;

:new.job := UPPER(:new.job) ;

END IF ;

IF INSERTING THEN

SELECT AVG(sal) INTO v_avgSal FROM emp ;

IF :new.sal > v_avgSal THEN

RAISE_APPLICATION_ERROR(-20009,'新进雇员工资不得高于公司平均工资！') ;

END IF ;

END IF ;

END BEFORE EACH ROW ;

END ;

/

• 下面执行一些DML操作。向雇员表中增加一条正确的数据

INSERT INTO emp (empno,ename,job,mgr,sal,comm,deptno,hiredate)

VALUES (9999,'mldn','manager',7566,1680,null,20,SYSDATE) ;

COMMIT ;

增加的雇员信息全部采用了小写字母，但是这些操作会在触发器中进行大写转换

• 查询em表中9999雇员信息

SELECT * FROM emp WHERE empno=9999 ;

效果：

• 向雇员表增加一条雇员信息，工资为5000（错误添加，已经超过了平均工资）

INSERT INTO emp (empno,ename,job,mgr,sal,comm,deptno,hiredate)

VALUES (8888,'lixinghua','manager',7566,5000,null,20,SYSDATE) ;

运行结果：

错误报告:

SQL 错误: ORA-20009: 新进雇员工资不得高于公司平均工资！

ORA-06512: 在 "SS.EMP_COMPOUND_TRIGGER", line 20

ORA-04088: 触发器 'SS.EMP_COMPOUND_TRIGGER' 执行过程中出错