DB2開發系列之四——觸發器

1、觸發器型別

1）BEFORE 觸發器：在對錶插入或更新之前執行該觸發器，允許使用CALL 和 SIGNAL SQL 語句；

2）BEFORE DELETE 觸發器：在刪除操作之前執行該觸發器；

3）AFTER 觸發器：在更新、插入或刪除操作之後執行。該觸發器用於更新反映表間關係和一致性的其他表中的資料，還用於確保資料完整性。AFTER 觸發器通常用於在特定情況下向使用者生成報警；

4）INSTEAD OF 觸發器：該觸發器支援對不支援插入、更新和刪除操作的檢視執行這些操作；

2、建立觸發器所需的許可權

1）使用觸發器的模式ID必須擁有以下許可權之一：

      i)對定義 BEFORE 或 AFTER 觸發器的表擁有 ALTER 許可權；

      ii)對定義 INSTEAD OF 觸發器的檢視擁有 CONTROL 許可權；

      iiI)對定義 INSTEAD OF 觸發器的檢視擁有所有權；

      iv)對定義觸發器的表或檢視的模式擁有 ALTERIN 許可權；

      v)SYSADM 或 DBADM 授權；

2）以及以下任意一種許可權：

      i)IMPLICIT_SCHEMA 資料庫授權（如果觸發器的隱式或顯式模式名稱不存在）；

      ii)對模式的 CREATEIN 許可權（如果觸發器的模式名稱引用現有的模式）；

3）假定授權 ID 沒有 SYSADM 和 DBADM 許可權並且觸發器存在，此 ID 必須擁有以下所有許可權：

      i)對定義觸發器的表擁有 SELECT 許可權 — 用於轉換變數和/或表；

      ii)對在觸發的操作條件中引用的任意表或檢視的 SELECT 許可權；

      iii)呼叫觸發的指定 SQL 語句所需的許可權；

3、建立觸發器語句

1）語法

  .-NO CASCADE-.
>>-CREATE TRIGGER--trigger-name--+-+------------+--BEFORE-+----->
                                 +-AFTER------------------+
                                 '-INSTEAD OF-------------'
>--+-INSERT--------------------------+--ON--+-table-name-+------>
   +-DELETE--------------------------+      '-view-name--'
   '-UPDATE--+---------------------+-'
             |     .-,-----------. |
             |     V             | |
             '-OF----column-name-+-'
>--+------------------------------------------------------------------+-->
   |              .-------------------------------------------------. |
   |              V  (1)    (2)          .-AS-.                     | |
   '-REFERENCING------------------+-OLD--+----+--correlation-name-+-+-'
                                  |      .-AS-.                   |
                                  +-NEW--+----+--correlation-name-+
                                  |            .-AS-.             |
                                  +-OLD TABLE--+----+--identifier-+
                                  |            .-AS-.             |
                                  '-NEW TABLE--+----+--identifier-'
>--+-FOR EACH ROW--------------+--| triggered-action |--------->
   |  (3)                      |
   '--------FOR EACH STATEMENT-'
triggered-action
|--+-------------------------------------+---------------------->
   |  (4)                                |
   '--------WHEN--(--search-condition--)-'

2）觸發器有三個主要元件：

      i)觸發器事件；

      ii)觸發器條件；

      iii)觸發器操作；

3）示例：

      i)簡單的 AFTER INSERT 觸發器
      CREATE TRIGGER new_emp
      AFTER INSERT ON employee
      REFERENCING NEW AS n
      FOR EACH ROW
      INSERT INTO audit_emp VALUES (n.empno, 'Insert',0.0, current user, current timestamp)

      ii)簡單的 AFTER DELETE 觸發器
     CREATE TRIGGER purge_emp
     AFTER DELETE ON employee
     REFERENCING OLD AS o
     FOR EACH ROW
     INSERT INTO audit_emp VALUES (o.empno, 'Delete',0.0, current user, current timestamp)
      iii)簡單的 AFTER UPDATE 觸發器
     CREATE TRIGGER update_emp
     AFTER UPDATE OF salary ON employee
     REFERENCING OLD AS o NEW AS n
     FOR EACH ROW
     WHEN (n.salary <> o.salry)
      INSERT INTO audit_emp VALUES (o.empno,'Update',n.salary,current user, current timestamp)
      iv)簡單的 BEFORE UPDATE 觸發器
      CREATE TRIGGER update_bemp
      BEFORE UPDATE ON employee
      REFERENCING OLD AS o NEW AS n
      FOR EACH ROW
      WHEN (n.salary = 60000.00)
      SET n.salary = 75000.00)

4、觸發器的高階用法

 1）INSTEAD OF 觸發器

  --示例

    CREATE TABLE "DB2INST1"."EMPLOYEES"
    (
      "EMPNO"    CHAR(6) NOT NULL     ,
      "FIRSTNME" VARCHAR(12) NOT NULL ,
      "LASTNAME" VARCHAR(15) NOT NULL ,
      "PHONENO"  CHAR(4)              ,
      "SALARY"   DECIMAL(9,2)
      )

    CREATE VIEW employeev AS
    SELECT empno, firstnme, lastname, phoneno
    FROM employees

   CREATE TRIGGER new_emp1
    INSTEAD OF INSERT ON employeev
    REFERENCING NEW AS n
    FOR EACH ROW
    INSERT INTO employees VALUES (n.empno, n.firstnme, n.lastname, n.phoneno, 0)

 2）用觸發器處理 XML 資料

  --示例

    CREATE TRIGGER new_order
    BEFORE INSERT ON purchaseorder
    REFERENCING NEW AS N
    FOR EACH ROW
      SET (n.porder) =  xmlvalidate(n.porder
      ACCORDING TOXMLSCHEMA URI ')
 3）使用 SQL PL 語句擴充套件觸發器主體

      i)語法

					General Syntax Diagram for CREATE TRIGGER cont.
SQL-procedure-statement
|--+-CALL----------------------------------------------+--------|
   +-Compound SQL (Dynamic)----------------------------+
   +-FOR-----------------------------------------------+
   +-+-----------------------------------+--fullselect-+
   | |       .-,-----------------------. |             |
   | |       V                         | |             |
   | '-WITH----common-table-expression-+-'             |
   +-GET DIAGNOSTICS-----------------------------------+
   +-IF------------------------------------------------+
   +-INSERT--------------------------------------------+
   +-ITERATE-------------------------------------------+
   +-LEAVE---------------------------------------------+
   +-MERGE---------------------------------------------+
   +-searched-delete-----------------------------------+
   +-searched-update-----------------------------------+
   +-SET Variable--------------------------------------+
   +-SIGNAL--------------------------------------------+
   '-WHILE---------------------------------------------'

      ii)從觸發器中呼叫儲存過程

      CREATE PROCEDURE write_audit( IN p_empno   CHAR(6),
                              IN p_txt     CHAR(6),
                              IN p_salary  DECIMAL(9,2),
                              IN p_user    CHAR(8),
                              IN p_curtime TIMESTAMP )
      BEGIN
      INSERT INTO audit_emp  VALUES ( p_empno, p_txt, p_salary, p_user, p_curtime )
      END

      CREATE TRIGGER new_emp
      AFTER INSERT ON employee
      REFERENCING NEW AS n
      FOR EACH ROW
      CALL write_audit( n.empno, 'Insert', 0.0, current user, current timestamp)

      iii)使用 SQL PL 的 BEFORE INSERT 觸發器

      CREATE TRIGGER business_rules
      BEFORE INSERT ON empprojact
      REFERENCING NEW AS n
      FOR EACH ROW
      BEGIN ATOMIC
      -- Business Rule One (Project ending date Can't be NULL)
      IF (n.emendate IS NULL) THEN
      SET n.emendate = CURRENT date;
      END IF;

      -- Business Rule Two (Project ending date Can't end in last month of the year)
      IF (n.emendate BETWEEN '2009-12-01' AND '2009-12-31') THEN
     SIGNAL SQLSTATE '90000'
        SET MESSAGE_TEXT = 'Business Rule violation - 90000';
      END IF;

     END