DB2触发器_db2 trigger-CSDN博客

转自：http://changfakong.diandian.com/post/2012-02-29/14915882

触发器

触发器（Trigger）是一种根据数据库内容变化而自动执行的程序，它定义于一张表上，当该表中的数据发生变化的时候，如果满足了触发器中预定义的条件，就会引发触发器的执行，进而去做其他操作。

1 触发器的分类

DB2 中的触发器分为前触发器、后触发器和替代触发器三种。(NO CASCADE BEFORE；AFTER；INSTEAD OF)

一个触发器涉及到两个动作，一个是能够激活触发条件的数据变化，即触发器执行的原因，另一个是触发器中指定的操作，即触发器执行的结果。前触发器是先执行触发器中指定的操作，再执行引发触发器动作的操作，即先果后因。后触发器正好相反，先执行激活触发器的那个操作，再执行触发器中指定的操作，即先因后果。替代触发器是专门定义于视图之上的触发器，把原本在视图当中无法执行的操作（如对只读视图执行插入操作）替换为一系列具体可以对基本表执行的操作。

2 触发器的创建与删除

本节通过一个例子讲解前触发器和后触发器的创建。替代触发器的内容请参见《 DB2 深度解析——高级 DBA 和开发者》。

例创建触发器。

由于经济形势不景气，央行采取一系列措施进行宏观调控，其中一个主要的手段是调整利率。假设央行的数据库中存在以下两张表：

CREATE TABLE CurrentRate (

id CHAR(4) NOT NULL PRIMARY KEY,

name VARCHAR(20) NOT NULL,

rate DECIMAL(5,2) NOT NULL,

status VARCHAR(9) )

该表表示当前利率，id 为编号，name 为利率种类的名称，例如“one year”表示定期一年的利率，rate 表示利率的值，精确到小数点后两位，status 表示利率的状态，可以取以下几种值：Highest 表示达到了历史上最高利率，Lowest 表示达到了历史上最低利率，Rising 表示比上次利率高，Dropping 表示比上次利率低，Steady 表示和上次相比利率稳定。

CREATE TABLE RateHistory (

id CHAR(4) NOT NULL,

name VARCHAR(20) NOT NULL,

rate DECIMAL(5,2) NOT NULL,

changeTime TIMESTAMP )

该表记录利率变化的历史信息，前三个字段的含义与 CurrentRate 表相同，最后一个字段 changeTime 表示利率调整的时间。

当进行利率调整时，会使用 UPDATE 语句对 CurrentRate 的 rate 字段进行调整，设计两个触发器，一个用于更新 CurrentRate 表的 status 字段，一个用于把新的利率信息计入到历史表中。

CREATE TRIGGER RateStatus

NO CASCADE BEFORE

UPDATE OF rate ON CurrentRate

REFERENCING OLD AS oldrate NEW AS newrate

FOR EACH ROW

MODE DB2SQL

SET newrate.status= CASE WHEN newrate.rate>=(select max(rate) from ratehistory where id=newrate.id) THEN 'Highest' WHEN newrate.rate<=(select min(rate) from ratehistory where id=newrate.id) THEN 'Lowest' WHEN newrate.rate>oldrate.rate THEN 'Rising' WHEN newrate.rate<oldrate.rate THEN 'Dropping' WHEN newrate.rate=oldrate.rate THEN 'Steady'

END

在第一行指定了触发器的名称，第二行 BEFORE 指定该触发器是前触发器，也就是说，该触发器中指定的动作（SET 赋值命令以及 CASE 语句部分）会在对利率表执行 UPDATE 操作之前进行，第二行的 NO CASCADE 表示触发器不会级联（一个触发器所做的动作引发另一个触发器的执行），前触发器都不会产生级联，所以必须加上 NO CASCADE 关键字，第三行的 UPDATE OF rate ON CurrentRate 表示触发条件，即当在 CurrentRate 表的 rate 字段上执行了 UPDATE 操作，就会激活该触发器，第四行指定了旧行和新行的相关名，在触发器被激活执行的过程中，表中每一行更改之前和更改之后的数据可能都会被引用，在此指定的旧行相关名表示当前行在操作之前的值，新行的相关名表示当前行在操作之后的值，第五行指定该触发器对表中的每一行都执行一次（还有一种触发器能够对表只执行一次操作），第六行表示创建触发器使用的是 DB2 自带的 SQL PL 语言（目前版本的 DB2 只支持这一种语言编写的触发器）。

从 SET 语句开始是触发器的程序体，表示当触发器被激活时所做的操作。该语句指定了对当前行的新值中 status 字段进行重新赋值，根据不同的条件分别赋给 Highest，Lowest，Rising、Dropping 和 Steady 五种不同的值。

最后，END 表示触发器程序体结束。

CREATE TRIGGER RecordHistory

AFTER

UPDATE OF rate ON CurrentRate

REFERENCING NEW AS newrate FOR EACH ROW

MODE DB2SQL

BEGIN ATOMIC INSERT INTO RateHistory VALUES (newrate.id, newrate.name, newrate.rate, current timestamp) ;

END

当执行完对利率的更新操作之后，应该将此次的更新记录到利率历史表中，采用后触发器来实现该功能。第一行指定触发器名称，第二行指定该触发器是后触发器，即当对 CurrentRate 表操作完成之后才执行此次触发器指定的操作，第三行指定触发条件是对 CurrentRate 表的 rate 字段执行 UPDATE 操作，第四行指定新行的相关名，第五行指定对表的每一行执行一次触发器规定的操作，第六行指定编写触发器程序的语言，BEGIN 和 END 之间的是程序体，执行了一个对历史表的插入操作，其中插入的最后一个字段就是当前的时间戳。(atomic是将该部分程序块指定为一个整体,其中任何一个语句失败,则整个程序块都相当于没做,包括包含在atomic块内的已经执行成功的语句也相当于没做，有点类似于transaction。)

删除触发器使用 DROP TRIGGER 语句。

--------------------------------------------

触发器2：http://blog.csdn.net/tuning_optmization/article/details/9717359

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 'http://posample.org/order.xsd')
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

-----------------------触发器3

http://www.ibm.com/developerworks/cn/data/library/techarticles/0308bhogal/0308bhogal.html