Oracle PL/SQL进阶编程（第十一弹：DML触发器）

DML触发器时一种用在表上的比较传统的触发器，用的最为频繁。

触发器的执行顺序

由于在一个表上可能定义多个触发器，因此需要首先了解一下触发器的执行顺序。

单行触发器执行顺序

当在某一行上定义了多个触发器时，执行顺序如下：
1. BOFORE语句触发器
2. BEFORE行级触发器
3. 执行DML语句
4. AFTER行级触发器
5. AFTER语句触发器

多行触发器执行顺序

如果触发器影响到多行，那么在每一行上都要执行一次触发器语句。我们以触发器影响到两行到两行，则其执行顺序如下：
1. BEFORE语句触发器
2. 第1行的BEFORE行级触发器
3. 第1行执行DML语句
4. 第1行的AFTER行级触发器
5. 第2行的BEFORE行级触发器
6. 第2行执行DML语句
7. 第2行的AFTER行级触发器
8. AFTER语句触发器

定义DML触发器

之前已经介绍过通用触发器的定义语法，DML触发器定义的更详细的语法如下：

CREATE [OR REPLACE] TRIGGER [schema.]trigger
{BEFORE | AFTER} verb_list ON [schema.]table
[REFERENCING{OLD as old} | {NEW as new} | {PARENT as parent}]
[FOR EACH ROW]
[WHEN (condition)]
plsql_block | call_procedure_statement

代码时最直接的，我们来看一个触发器的代码：

--创建一个emp_log表用来记录对emp表的更改
CREATE TABLE emp_log(
   log_id NUMBER,              --日志自增长字段
   log_action VARCHAR2(100),   --表更改行为，比如新增或删除或更改 
   log_date DATE,              --日志日期
   empno NUMBER(4),            --员工编号
   ename VARCHAR2(10),         --员工名称
   job VARCHAR2(18),           --职别
   mgr NUMBER(4),              --管理者
   hiredate DATE,              --雇佣日期 
   sal NUMBER(7,2),            --工资
   comm NUMBER(7,2),           --提成或分红
   deptno NUMBER(2)            --部门编号
);
--创建一个AFTER行触发器
CREATE OR REPLACE TRIGGER t_emp_log
   AFTER INSERT OR DELETE OR UPDATE ON emp     --触发器作用的表对象以及触发的条件和触发的动作
   FOR EACH ROW                                --行级别的触发器
BEGIN
   IF INSERTING THEN                           --判断是否是INSERT语句触发的
      INSERT INTO emp_log                      --向emp_log表中插入日志记录
      VALUES(
        emp_seq.NEXTVAL,
        'INSERT',SYSDATE,
        :new.empno,:new.ename,:new.job,
        :new.mgr,:new.hiredate,:new.sal,
        :new.comm,:new.deptno );
   ELSIF UPDATING THEN                         --判断是否是UPDATE语句触发的
      INSERT INTO emp_log                      --首先插入新的记录
      VALUES(
        emp_seq.NEXTVAL,
        'UPDATE_NEW',SYSDATE,
        :new.empno,:new.ename,:new.job,
        :new.mgr,:new.hiredate,:new.sal,
        :new.comm,:new.deptno );  
      INSERT INTO emp_log                      --然后插入旧的记录
      VALUES(
        emp_seq.CURRVAL,
        'UPDATE_OLD',SYSDATE,
        :old.empno,:old.ename,:old.job,
        :old.mgr,:old.hiredate,:old.sal,
        :old.comm,:old.deptno );              
   ELSIF DELETING THEN                         --如果是删除记录
      INSERT INTO emp_log
      VALUES(
        emp_seq.NEXTVAL,
        'DELETE',SYSDATE,
        :old.empno,:old.ename,:old.job,
        :old.mgr,:old.hiredate,:old.sal,
        :old.comm,:old.deptno );         
   END IF;
END;

使用语句触发器

如果在创建触发器时，不指定FOR EACH ROW子句，那么创建的触发器就是语句触发器。语句触发器在建立DML操作审计，或者是DML的权限控制时非常有用，这样可以避免未经授权的DML语句操作。

使用BEFORE语句触发器

如下代码创建了一个语句触发器来限制对emp表的DELETE操作只能在正常工作日的8:30-18:00之内，不在这个时间段范围之内的修改都不能进行。

CREATE OR REPLACE TRIGGER t_verify_emptime
   BEFORE INSERT OR DELETE OR UPDATE
   ON emp
BEGIN
   IF DELETING                     --使用谓词判断是否为DELETING操作，仅删除时才判断
   THEN
      --判断当前操作的日期
      IF    (TO_CHAR (SYSDATE, 'DAY') IN ('星期六', '星期日'))
         OR (TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00')
      THEN
         --触发异常，将导致整个事务被回滚。
         raise_application_error (-20001, '不能在非常时间段内操纵emp表');
      END IF;
   END IF;
END;

使用AFTER语句触发器

如下代码创建了一个用来记录审计信息的表，再创建一个AFTER语句触发器，在该触发器中添加审计信息。

CREATE TABLE audit_table(   
   table_name VARCHAR2(20),    --统计表名称
   ins_count INT,              --INSERT语句执行次数
   udp_count INT,              --UPDATE语句执行次数
   del_count INT,              --DELETE语句执行次数
   start_time DATE,            --开始时间
   end_time DATE               --结束时间
);

CREATE OR REPLACE TRIGGER t_audit_emp
   AFTER INSERT OR UPDATE OR DELETE
   ON emp                                --在emp表中定义AFTER触发器
DECLARE
   v_temp   INT;                         --定义一个临时的变量来统计记条数
BEGIN
   SELECT COUNT (*)                      --向v_temp表中插入EMP表的记录条数
     INTO v_temp
     FROM audit_table
    WHERE table_name = 'EMP';
   IF v_temp = 0
   THEN
      --向audit_table表中插入一条记录，将审记记录数量保留为0。
      INSERT INTO audit_table  VALUES ('EMP', 0, 0, 0, SYSDATE, NULL);
   END IF;
   CASE                                  --使用PL/SQL的CASE语句判断DML类型
      WHEN INSERTING  THEN               --如果是INSERT语句执行
         UPDATE audit_table              --更新ins_count字段
            SET ins_count = ins_count + 1,
                end_time = SYSDATE
          WHERE table_name = 'EMP';
      WHEN UPDATING THEN                 --如果是UPDATE语句执行
         UPDATE audit_table
            SET udp_count = udp_count + 1,--更新udp_count字段
                end_time = SYSDATE
          WHERE table_name = 'EMP';
      WHEN DELETING THEN
         UPDATE audit_table                --如果是DELETE语句执行
            SET del_count = del_count + 1,--更新del_count字段
                end_time = SYSDATE
          WHERE table_name = 'EMP';
   END CASE;
END;

使用OLD和NEW谓词

当使用 行触发器时，可以使用OLD和NEW谓词来获取语句执行前后的行记录。这两个谓词的结构非常相似，但是不是真正的PL/SQL结构类型。

以下是一些常见的规则：
- 在INSERT语句上激发触发器时，OLD结构是不包含任何值的。
- 在UPDATE语句上激发触发器时，OLD结构包含更新之前记录的值，NEW包含在更新之后记录的值。
- 在DELETE语句上激发触发器时，NEW结构不包含任何值，OLD包含已经被删除的记录。
- NEW和OLD也包含了ROWID伪列，这个伪列在OLD和NEW中具有相同的值。
- 不能更改OLD结构的值，但是可以更改NEW结构的值。不过在AFTER行触发器中也不能改变NEW，因为此时DML语句已经执行。通常来说，NEW记录仅仅在行级别前的触发器中被更改，OLD记录永远不能被修改，只能对其读取。
- 在触发器内部，不能将NEW或OLD结构作为一个记录参数传递给过程或函数，只能传递单个的字段。
- 当在匿名块或触发器的内部使用NEW和OLD谓词时，必须在前面加上冒号，如:NEW.empno或:OLD.empno这种格式，在其他部分使用时是不需要冒号的。
- 在 NEW和OLD结构中不能进行记录级别的操作 ，比如直接为记录赋值时非法的，:NEW := NULL;这样的语句是错误的，只能对谓词的每个字段进行操作。

使用REFERENCING子句

可以使用REFERENCING来更改默认的谓词名称，使得代码更具有可读性或编写代码更顺手：
REFERENCING [OLD AS old_name] [NEW AS new_name]

看一个例子：

CREATE OR REPLACE TRIGGER t_vsal_ref
   BEFORE UPDATE ON emp     --触发器作用的表对象以及触发的条件和触发的动作
   REFERENCING OLD AS emp_old NEW AS emp_new
   FOR EACH ROW             --行级别的触发器
   WHEN(emp_new.sal>emp_old.sal)    --触发器条件
DECLARE
   v_sal   NUMBER;          --语句块的声明区
BEGIN
   IF UPDATING ('sal') THEN --使用条件谓词判断是否是sal列被更新
      v_sal := :emp_new.sal - :emp_old.sal; --记录工资的差异
      DELETE FROM emp_history 
            WHERE empno = :emp_old.empno;      --删除emp_history中旧表记录
      INSERT INTO emp_history              --向表中插入新的记录
           VALUES (:emp_old.empno, :emp_old.ename, :emp_old.job, :emp_old.mgr, :emp_old.hiredate,
                   :emp_old.sal, :emp_old.comm, :emp_old.deptno);
      UPDATE emp_history                   --更新薪资值
         SET sal = v_sal
       WHERE empno = :emp_new.empno;
   END IF;
END;

使用WHEN子句

在WHEN子句中可以使用不带冒号的NEW和OLD，如WHEN (NEW.sal>OLD.sal)。

使用条件谓词

条件谓词主要用来确定触发触发器的DML语句的类型，条件谓词有INSERTING、UPDATING、DELETING，都返回一个布尔值True或False。
在触发体中可以使用如下：

IF INSERTING THEN ... END IF;
IF UPDATING THEN ... END IF;

UPDATING有一个重载的版本接收特定的列名，以便根据更新的列来进行进一步的处理，如：

CREATE OR REPLACE TRIGGER t_comm_sal
   BEFORE UPDATE ON emp     --触发器作用的表对象以及触发的条件和触发的动作
   FOR EACH ROW             --行级别的触发器
BEGIN
   CASE 
   WHEN UPDATING('comm') THEN          --如果是对comm列进行更新     
      IF :NEW.comm<:OLD.comm THEN      --要求新的comm值要大于旧的comm值
         RAISE_APPLICATION_ERROR(-20001,'新的comm值不能小于旧的comm值');
      END IF;
   WHEN UPDATING('sal') THEN           --如果是对sal列进行更新
      IF :NEW.sal<:OLD.sal THEN        --要求新的sal值要大于旧的sal值
         RAISE_APPLICATION_ERROR(-20001,'新的sal值不能小于旧的sal值'); 
      END IF;
   END CASE;        
END;

控制触发顺序

可以使用FOLLOWS来控制顺序，如：

--创建第一个触发器
CREATE OR REPLACE TRIGGER one_trigger
   BEFORE INSERT
   ON trigger_data
   FOR EACH ROW
BEGIN
   :NEW.trigger_id := :NEW.trigger_id + 1;
   DBMS_OUTPUT.put_line('触发了one_trigger');
END;
--创建与第1个触发器具有相同类型相同触发时机的触发器
CREATE OR REPLACE TRIGGER two_trigger
   BEFORE INSERT
   ON trigger_data
   FOR EACH ROW
   FOLLOWS one_trigger          --让该触发器在one_trigger后面触发
BEGIN
   DBMS_OUTPUT.put_line('触发了two_trigger');
   IF :NEW.trigger_id > 1
   THEN
      :NEW.trigger_id := :NEW.trigger_id + 2;
   END IF;
END;

实际上，使用了FOLLOWS语句后，在两个触发器之间创建了依赖，使得two_trigger依赖于one_trigger，可以通过user_dependencies查看依赖关系。

不过一般不推荐使用使用FOLLOWS来控制顺序，因为使用一个触发器就已经不是一个很好的解决方案了，同时使用两个触发器就更不推荐了。

触发器限制

在编写触发器代码时，要注意不能对触发器所应用的基表中读取或修改数据，尽管这样做在建立触发器时不会报错，但是在执行相应的触发器时会显示错误消息。因此，要注意：
- 通常，行级别的触发器不能读或写触发器所作用的基表，这个限制仅应用在行级别触发器上，语句级的触发器可以自由读写触发器基表。
- 如果在触发器中使用自治事务，并在触发体中提交事务，则可以查询基表的内容，但是不能对基表进行任何的修改操作。

使用自治事务

默认情况下，DML触发器与触发其执行的DML语句在一个事务范围内，因此：
- 如果在触发器中抛出了一个异常，将导致整个事务回滚。
- 如果在触发体中使用了DML操作，比如下向日志表中插入日志记录，那么这些DML操作也属于主事务的一部分，因此触发体中的任何意外操作也会导致整个事务回滚。
- 在触发体中不能使用COMMIT或ROLLBACK语句，因为这回影响到主事务的执行。

如果在编写触发器时，需要独立主事务进行处理，比如不管DML语句是否成功执行，都需要将对日志的操作保存到数据库中，此时可以使用自治事务，对主事务不会有影响。
与在过程和函数中使用自治事务一样，使用PRAGMA AUTONOMOUS_TRANSACTION编译提示，如：

CREATE OR REPLACE TRIGGER t_emp_comm
   BEFORE UPDATE ON emp     --触发器作用的表对象以及触发的条件和触发的动作
   FOR EACH ROW             --行级别的触发器
   WHEN(NEW.comm>OLD.comm)    --触发器条件
DECLARE   
   v_comm   NUMBER;          --语句块的声明区
   PRAGMA AUTONOMOUS_TRANSACTION; --自治事务      
BEGIN
   IF UPDATING ('comm') THEN --使用条件谓词判断是否是comm列被更新
      v_comm := :NEW.comm - :OLD.comm; --记录工资的差异
      DELETE FROM emp_history 
            WHERE empno = :OLD.empno;      --删除emp_history中旧表记录
      INSERT INTO emp_history              --向表中插入新的记录
           VALUES (:OLD.empno, :OLD.ename, :OLD.job, :OLD.mgr, :OLD.hiredate,
                   :OLD.sal, :OLD.comm, :OLD.deptno);
      UPDATE emp_history                   --更新薪资值
         SET comm = v_comm
       WHERE empno = :NEW.empno;
   END IF;
   COMMIT;                                 --提交结束自治事务
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;                            --发生任何异外回滚自治事务
END;