Oracle触发器介绍 语句级触发器

语句级触发器
我们先看一个AFTER-INSERT-STATEMENT触发器：
CREATE OR REPLACE TRIGGER temp_ais
AFTER INSERT ON TEMP
BEGIN
    dbms_output.put_line('executing temp_ais');
END;
看一下下面语句的结果：
SQL> set feedback off
SQL> INSERT INTO temp VALUES (1);          -- insert 1 row
executing temp_ais
SQL> INSERT INTO temp VALUES (1);          -- insert 1 row
executing temp_ais
SQL> INSERT INTO temp SELECT * FROM temp;  -- insert 2 rows
executing temp_ais

每个SQL插入语句将触发一次，行级触发器最后一条语句要触发两次。
一、事件顺序
用Insert 语句级触发器可以做：
    ·可以在表上执行一个合计运算，可以在insert前或后来计算。
    ·可以使用语句级触发器去处理行级触发器控制的数据。
    ·可以给事件发信号。可以仅仅是一个打印语句。也可以是一个email或使用DBMS_ALERT包向其他处理过程发送信号。
                                                                                                  Tasks Performed
                                          Stages                                          -------------------
                               ----------------------------------  ———>   |   Rehect the     |
Fires once          |   BIS function                     |                   |   Transaction    |
per statement    | Statement Level Trigger |                     -------------------
                              ----------------------------------- ———>     -------------------
                                          |                                                      |  Take Action     |
                                          |                                                       -------------------
   insert                            |
   3行记录        |    --------------------------                   |    
     ————>  |    |    Row Trigger       |                  |    -------------------------------
     ————>  |    --------------------------                   |    |每一个行触发器插入一|
     ————>  |                |                                         |    |行触发一次，插入三行|
                         |  ----------------------------------------   |    |触发三次，语句级触发|
                         |  | Oracle enforces constraints |  |    |器在行操作和行数据上|
                         |  ----------------------------------------   |    |不可见                            |
                         |                |                                         |    |                                         |
                         |    --------------------------                    |    -------------------------------
                         |    |    Row Trigger          |                |    
                         |    --------------------------                    |        
                                         |
                                         |                                                         ----------------------
                               ----------------------------------  ————>  |   Rehect the     |
Fires once          |   AIS function                     |                      |   Transaction   |
per statement    | Statement Level Trigger |                       ---------------------
                              ----------------------------------  ————>   --------------------
                                                                                                   |  Take Action  |
                                                                                                   --------------------  

上图显示了语句级出发器的行为。同时也显示了在Before statement触发器和After statement触发器间的行级触发器的触发情况。如果一个update SQL语句更新三行，那么行级触发器触发三次，语句级触发器触发一次。

二、insert 语句级触发器定义语法
语法如下：
CREATE OR REPLACE TRIGGER trigger_name
[AFTER | BEFORE] INSERT ON table_name
DECLARE
    Local declarations
BEGIN
    Body written PL/SQL
END;

语句级和行级触发器在语法上关键的不同在于：FOR EACH ROW字句。在行级触发器中指定这个子句而语句级触发器中不需要指定。

 1）、WHEN(Boolean expression)  所有行触发器可用
 2）、OF column_name clause     仅对update触发器可用
在语句级触发器中：
 ·引用:NEW.COLUMN_NAME and :OLD.COLUMN_NAME是不正确的。
 ·不能使用When(boolean expression)子句中包含OLD.COLUMN_NAME和 NEW.COLUMN_NAME.

可以使用下面的语句：
 CREATE OR REPLACE TRIGGER temp_biuds
 BEFORE INSERT OR UPDATE OR DELETE ON TEMP
 BEGIN
     CASE
     WHEN inserting THEN
         PL/SQL code here
     WHEN updating THEN
         PL/SQL code here
     WHEN deleting THEN
         PL/SQL code here
     END CASE;
 END;
三、语句级组合

   ·使用错误码来更新Errors包
   ·包商业规则逻辑放到一个约束包中
   ·编写before或after语句级触发器
  
  1）、第一步是声明错误码和错误信息。Errors包更新包含了-20002和-2003两个错误码
 CREATE OR REPLACE PACKAGE errors IS
     eng_dept_sal CONSTANT PLS_INTEGER := -20001;
     app_error_02 CONSTANT PLS_INTEGER := -20002;
     app_error_03 CONSTANT PLS_INTEGER := -20003;
 
     eng_dept_sal_txt CONSTANT VARCHAR2(100) :=
     'The salary exceeds the ENGL maximum of $10,000.00';
 
     app_error_02_txt CONSTANT VARCHAR2(100) :=
     'No additions if the budget exceeds $55,000.00';
 
     app_error_03_txt CONSTANT VARCHAR2(100) :=
     'Budget cannot be over $60,000.00';
 END errors;
  2）、把商业逻辑封装在约束包中。
    CREATE OR REPLACE PACKAGE professors_cons IS
     PROCEDURE constrain_budget
         (limit NUMBER,err_code PLS_INTEGER,err_text
         VARCHAR2);
 END professors_cons;
 
 CREATE OR REPLACE PACKAGE BODY professors_cons IS
     PROCEDURE constrain_budget
         (limit NUMBER,err_code PLS_INTEGER,err_text
         VARCHAR2)
     IS
         budget_sum NUMBER;
     BEGIN
         SELECT SUM(salary) INTO budget_sum FROM
         professors;
         IF budget_sum > limit THEN
             RAISE_APPLICATION_ERROR(err_code, err_text);
         END IF;
     END constrain_budget;
 END professors_cons;
  3）、定义before和after触发器
    CREATE OR REPLACE TRIGGER professors_bis
 BEFORE INSERT OR UPDATE ON professors
 BEGIN
     professors_cons.constrain_budget
         (55000, errors.budget_err_1,
         errors.budget_err_1_txt);
 END;
 
 CREATE OR REPLACE TRIGGER professors_ais
 AFTER INSERT OR UPDATE ON professors
 BEGIN
     professors_cons.constrain_budget
         (60000, errors.budget_err_2,
         errors.budget_err_2_txt);
 END;

四、处理行获得的数据
行级触发器可以在全局临时表中存储:OLD 和 :NEW 字段值。全局临时表范围仅是事务。通过复制:OLD 和 :NEW 值，商业规则的处理被延期到语句级触发器上。有时是必须的，因为商业规则是复杂的，需要从表中查询，包括表被更新。
1)、首先需要一个全局临时表，它在行级触发器上用于存储数据。
CREATE global temporary TABLE professors_g
 (prof_name     VARCHAR2(10),
  specialty     VARCHAR2(20),
  hire_date     DATE,
  salary        NUMBER(7,2),
  tenure        VARCHAR2(3),
  department    VARCHAR2(10)) ON COMMIT DELETE ROWS;
2)、为这张表编写存储过程，放于包Professors_cons里.如下：
CREATE OR REPLACE PACKAGE professors_cons IS
    PROCEDURE load_temp_table
        (v_prof_name  professors.prof_name%TYPE,
         v_specialty  professors.specialty%TYPE,
         v_hire_date  professors.hire_date%TYPE,
         v_salary     professors.salary%TYPE,
         v_tenure     professors.tenure%TYPE,
         v_department professors.department%TYPE);

    PROCEDURE dump_temp_table;

END professors_cons;
包体为：
CREATE OR REPLACE PACKAGE BODY professors_cons IS

    PROCEDURE load_temp_table
        (v_prof_name  professors.prof_name%TYPE,
         v_specialty  professors.specialty%TYPE,
         v_hire_date  professors.hire_date%TYPE,
         v_salary     professors.salary%TYPE,
         v_tenure     professors.tenure%TYPE,
         v_department professors.department%TYPE)
    IS
    BEGIN
        INSERT INTO professors_g VALUES
            (v_prof_name, v_specialty, v_hire_date,
             v_salary, v_tenure, v_department);
    END load_temp_table;

    PROCEDURE dump_temp_table IS
    BEGIN
        FOR rec in (SELECT * FROM professors_g) LOOP
            dbms_output.put_line(
                rec.prof_name||' '||rec.specialty||' '||
                rec.hire_date||' '||rec.salary||' '||
                rec.tenure||' '||rec.department);
        END LOOP;
    END dump_temp_table;
END professors_cons;

3)、下面是一个after delete 行触发器。当它触发时，通过Professors_cons插入临时表一行数据。
CREATE OR REPLACE TRIGGER professors_adr
AFTER DELETE ON professors
FOR EACH ROW
BEGIN
    professors_cons.load_temp_table
        (:old.prof_name, :old.specialty, :old.hire_date,
         :old.salary, :old.tenure, :old.department);
END;

下一个是after delete语句级触发器，使用约束包打印删除的行信息。
CREATE OR REPLACE TRIGGER professors_ads
AFTER DELETE ON professors
BEGIN
    professors_cons.dump_temp_table;
END;

delete SQL语句后面是语句级触发器的输出：

SQL> DELETE FROM professors;

Blake Mathematics 08-aug-2003 02:06:27 10000 YES MATH
Milton Am Hist 09-aug-2003 02:06:27 10000 YES HIST
Wilson English 06-aug-2003 02:06:27 10000 YES ENGL
Jones Euro Hist 12-jul-2003 02:06:28 10000 YES HIST
Crump Ancient Hist 12-jul-2003 02:06:28 10000 YES HIST

5 rows deleted.