1. 首页
  2. IT资讯

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 '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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8484829/viewspace-2116684/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/185036.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code