DB2开发系列之四——触发器

时间:2023-03-09 19:08:31
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