触发器 Triggers

[roamer]

  本文介绍以下内容：

• 如何创建和执行触发器
• 删除触发器
• 使用系统目录获取触发器信息

1.触发器

  触发器 (trigger) 是特定事件出现时自动执行 SQL 语句的数据库机制。触发操作的事件 可以是特定表上的 INSERT、UPDATE、DELETE 或 SELECT 语句。触发操作的语句可以指定表或表内的一列或多列。

  触发器事件操作的表被称为触发表(triggering table) 。

  触发器事件出现时，执行触发器动作 (action) 。该动作可以是一个或多个 INSERT、UPDATE、 DELETE 或 EXECUTE PROCEDURE 语句的任意组合。

  触发器是数据库服务器的功能，因此访问数据库的应用程序工具的类型与触发器的执行无关。通过从数据库调用触发器，DBA 可以保证数据在应用程序工具和程序中可以被一致的处理。触发器与存储过程结合使用，因为触发器执行的 SQL 语句可以是 EXECUTE PROCEDURE 语句。

2. 创建触发器

CREATE TRIGGER trigger_name …

附加子句定义：

• 触发器事件——引发触发器执行的事件
• REFERENCING 子句——为触发器动作提供值
• WHEN 子句——条件
• 触发器动作——执行事件对应的操作

2.1 触发器事件

• 触发器事件
  – INSERT
  – DELETE
  – UPDATE
  – SELECT

• 可以为表上的同一事件定义多个触发器

• 可以为同一视图上的同一事件定义多个 INSTEAD OF 触发器

• 远程表(Remote Tables)
  由触发器事件指定的表必须是当前数据库中的表。不能指定远程表。

2.2 触发器动作

触发器动作指定具体执行的动作以及执行时间：

• 触发器事件出现之前：BEFORE (define action here )——在触发器事件执行之前，BEFORE 触发的动作列表执行一次。即使触发器事件不处理任何行， BEFORE 触发器动作仍会被执行。

• 触发器事件处理每一行之后：FOR EACH ROW (define action here )——在触发器事件处理完每行一之后，FOR EACH ROW 触发器动作执行一次。

• 触发器事件完成之后：AFTER (define action here )——在触发器事件执行之后，AFTER 触发的动作列表执行一次。如果触发语句不处理任何行， AFTER 触发的动作列表仍会被执行。

• 动作可以是 SQL 语句或存储过程。

• 不能引用任何触发器动作中所涉及到的触发表，除了 UPDATE 语句更新不在触发表上的列和包含在子查询或存储过程中的 SELECT 语句。

• 远程表
  触发器动作部分的语句可以引用远程表。

2.3 REFERENCING 示例

使用 REFERENCING 子句给动作提供值：

CREATE TRIGGER items_upd
  UPDATE OF total_price ON items 
  REFERENCING NEW AS post OLD AS pre 
  FOR EACH ROW
    (UPDATE ORDERS
      SET order_price = order_price + 
        post.total_price - pre.total_price 
      WHERE order_num = post.order_num);

  上述触发器示例展示了如何创建一个触发器来更新派生值。NEW 和 OLD 相关值是为 orders 表中的相应行更新 order_price 列。

2.4 WHEN条件

  WHEN 条件允许您根据测试结果触发的操作：

CREATE TRIGGER ins_cust_calls 
  INSERT ON cust_calls
  {Flag problems for billing dept. review}
  REFERENCING NEW AS post
  FOR EACH ROW WHEN(post.call_code = 'B')
    (INSERT INTO warn_billing 
      VALUES (post.customer_num));

  示例展示了带条件的触发器动作。出现投诉时，通过把顾客编号置于 warn_billing 表中来标记记账投诉 (call_code = B)。

  可以通过使用 WHEN 子句，指定触发器动作只在特定条件为真时执行。

  当WHEN 条件计算结果为真时，执行紧随其后的触发器动作语句。WHEN 条件计算结果为假或未知时，不执行触发器动作语句。

  可以在 BEFORE、FOR EACH ROW 和 AFTER 关键字之后使用一个或多个 WHEN 条件。每个 WHEN 条件都分开判定；例如：

FOR EACH ROW
WHEN (post.call_code = "B")
 INSERT INTO warn_billing VALUES(post.customer_num), 
WHEN (post.call_code = "C")
 INSERT INTO complaints VALUES(post.customer_num)

  条件可以包含布尔表达式，例如 BETWEEN、IN、 IS NULL、LIKE 和 MATCHES。可以将子查询作为条件的一部分。条件也可以包含关键字，例如 TODAY、USER、CURRENT 和 SITENAME。

  WHEN 子句不可用于 SELECT 触发器。

3. 级联触发器

  正如上述示例所示，执行一个触发器可以触发另一个触发器被执行。删除 customer 行将触发 del_cust 触发器执行。del_cust 触发器从 orders 表中删除一行，反过来触发 del_orders 触发器。这些触发器完成时，DELETE 语句以如下顺序执行：

• DELETE customer
• DELETE orders
• DELETE items
• DELETE cust_calls

  在级联删除成为 CREATE TABLE 语句的特性之前，经常使用该技术。级联删除能够定义引用约束，即父行被删除时，数据库服务器自动删除子行。

  ON SELECT 触发器不支持级联。

  如上述示例所示，可以在触发器内通过在一行中使用两个破折号 (- -)进行注释。也可以用两个大括号 ({})注释括号范围内的代码。使用两个破折号进行注释是 ANSI 兼容的方法。

4. 多个触发器

  表上的多个触发器可以包含相同或不同的列

CREATE TRIGGER trig1
  UPDATE OF item_num, stock_num ON items
    REFERENCING OLD AS pre NEW AS post
    FOR EACH ROW(EXECUTE PROCEDURE proc1());

CREATE TRIGGER trig2
  UPDATE OF manu_code ON items
    BEFORE(EXECUTE PROCEDURE proc2());

CREATE TRIGGER trig3
  UPDATE OF order_num, stock_num ON items
    BEFORE(EXECUTE PROCEDURE proc3());

  在上面的示例中，items表上的更新触发器 trig3 在其列表中包含stock_num，这也是 trig1 中的触发列。

4.1 多个触发器的执行顺序

• 触发器执行的顺序取决于 syscolumns 表里的列顺序。系统目录表里的列顺序取决于它们在 CREATE TABLE 语句中出现的顺序。

• 如果在相同的列或多列上设置了多个触发器，则无法保证执行顺序

CREATE TABLE taba (a int, b int, c int, d int);

CREATE TRIGGER trig1 UPDATE OF a, c ON taba
AFTER (UPDATE tabb SET y = y + 1);

CREATE TRIGGER trig2 UPDATE OF b, d ON taba
AFTER (UPDATE tabb SET z = z + 1);

UPDATE taba SET (b, c) = (b + 1, c + 1);

  在上述示例中，首先执行列 a 和 c 的 trig1，接下来执行列 b 和 d 的 trig2。在本例中，两个触发器中最小的列号是列 1 （a），下一个是列 2 （b）。

5. 视图上的 INSTEAD OF 触发器

• 当触发器的 DML 引用指定视图时启动
  – 可以插入、更新或删除

• INSTEAD OF 触发器动作替换触发事件：
  – 触发器事件未执行
  – 改为执行触发器操作

• 可能为每种类型的触发事件定义了多个 INSTEAD OF 触发器

  一个视图可以为每种类型的INSERT、DELETE或UPDATE触发事件定义任意数量的 INSTEAD OF 触发器。
  INSTEAD OF 触发器将触发器事件替换为视图上的指定触发器操作，而不是执行触发的插入、删除或更新操作。

5.1 INSTEAD OF 触发器示例

• 创建表dept与emp

CREATE TABLE dept ( 
  deptno INTEGER PRIMARY KEY, 
  deptname CHAR(20), 
  manager_num INT 
); 

CREATE TABLE emp ( 
  empno INTEGER PRIMARY KEY, 
  empname CHAR(20), 
  deptno INTEGER REFERENCES dept(deptno), 
  startdate DATE 
) ; 

ALTER TABLE dept ADD CONSTRAINT 
  (FOREIGN KEY (manager_num) REFERENCES emp(empno));

• 在dept和emp表上创建视图manager_info，其中包括每个部门的所有经理

CREATE VIEW manager_info AS
  SELECT d.deptno, d.deptname, e.empno, e.empname
    FROM dept d, emp e
    WHERE d.manager_num = e.empno;

• 在视图manager_info上创建INSTEAD OF触发器manager_info_insert，，旨在通过manager_info视图将行插入到 dept 和 emp 表中

CREATE TRIGGER manager_info_insert
  INSTEAD OF INSERT ON manager_info
  REFERENCING NEW AS n
    FOR EACH ROW 
      (EXECUTE PROCEDURE instab(n.deptno, n.empno));

CREATE PROCEDURE instab (dno INT, eno INT) 
  INSERT INTO dept(deptno, manager_num) 
    VALUES(dno, eno); 
  INSERT INTO emp (empno, deptno) 
    VALUES (eno, dno); 
END PROCEDURE;

• 触发器事件，此时INSERT并不在视图上执行，而改为执行存储过程instab

INSERT INTO manager_info(deptno, empno) VALUES (8, 4232);

  此触发 INSERT 语句不会被执行，但此事件会导致改为执行触发器操作，调用instab存储过程。存储过程中的 INSERT 语句将新值插入到manager_info视图的 emp 和 dept 基表中。

6. 触发器及存储过程

  在触发器里进行复杂处理的常见方法是让它调用一个或多个存储过程。但是，如果存储过程被用作触发器操作的一部分，有某些限制条件。

• 存储过程可由触发器调用。

• 从触发器调用的存储过程不包含：
  — BEGIN WORK
  —COMMIT WORK
  —ROLLBACK WORK
  —SET CONSTRAINTS

• 包含在 CREATE TRIGGER 语句里的存储过程的返回结果不能多于一行（如，使用 RETURN WITH RESUME 语句）。否则将会出现以下错误消息：
  686:Procedure (xxx) has returned more than one row.

7. 触发器存储过程

• 触发器过程是一个 SPL 例程，执行过程只能从触发器定义的 Action 子句的FOR EACH ROW部分调用该例程。

• 使用EXECUTE PROCEDURE调用触发器时必须包含WITH TRIGGER REFERENCES语句

• 此类过程必须在定义过程的 CREATE PROCEDURE 语句中包含REFERENCING子句和 FOR 子句。

• 此REFERENCING子句声明相关变量的名称，该过程可用于在发生触发器事件时引用行中的旧列值，或者

• 触发器修改行后列的新值。

• FOR 子句指定定义触发器的表或视图。

• 以下语句在 tab1 上定义一个插入触发器，该触发器从FOR EACH ROW”部分调用 proc1 作为其触发操作，并执行 INSERT 操作激活此触发器：

CREATE TRIGGER ins_trig_tab1 INSERT ON tab1
  REFERENCING NEW AS post
  FOR EACH ROW
    (EXECUTE PROCEDURE proc1()
       WITH TRIGGER REFERENCES);

8. 过程触发器和布尔运算符

• 布尔运算符检查触发事件的 DML：

CREATE TABLE tab1 ( 
  col1 INT, 
  col2 INT 
) ;

CREATE TABLE tab2 ( 
  col1 INT 
) ;

CREATE TABLE temptab1 ( 
  old_col1 INT, 
  new_col1 INT, 
  old_col2 INT, 
  new_col2 INT 
) ;

CREATE PROCEDURE proc1()
    REFERENCING OLD AS o NEW AS n FOR tab1;
  IF (INSERTING) THEN
    LET n.col1 = n.col1 + 1;
    INSERT INTO temptab1 VALUES(0,n.col1,1,n.col2);
  END IF
  IF (UPDATING) THEN
    INSERT INTO temptab1 
      values(o.col1,n.col1,o.col2,n.col2);
  END IF
  IF (SELECTING) THEN
    INSERT INTO temptab1 VALUES(o.col1,0,o.col2,0);
  END IF
  IF (DELETING) THEN
    DELETE FROM temptab1 WHERE temptab1.col1 = o.col1;
  END IF
END PROCEDURE;

  此触发过程说明触发的操作可以是与触发事件不同的 DML 操作。尽管此过程在insert触发器调用时插入行，并在delete调用时删除行，但如果由select触发器或update触发器调用，它也会执行 INSERT 操作。

  此示例中的 proc1 触发器过程使用仅在触发器例程中有效的布尔条件运算符。

  仅当从 INSERT 触发器的FOR EACH ROW操作调用该过程时，插入运算符才返回 true。也可以从触发事件为 UPDATE、SELECT 或 DELETE 的其他触发器调用此过程。因为在相应类型的触发事件的触发操作中调用该存储过程，则UPDATING, SELECTING和DELETING运算符将返回 true （t）。

• 以下语句将激活执行 proc1过程的ins_trig_tab1触发器。：

INSERT INTO tab1 VALUES(111,222); inserts values (112, 222)

  由于触发器过程将 col1 的新值递增 1，因此插入的值是（112 和 222），而不是原始触发事件（INSERT）指定的值 111。

9. 中止操作

使用存储过程回滚触发事件：

CREATE PROCEDURE stop_processing()
  RAISE EXCEPTION -745;
END PROCEDURE;

CREATE TRIGGER trig1 INSERT ON tab1
  REFERENCING NEW AS new_val
FOR EACH ROW WHEN (new_val.col2 > 20)
  (EXECUTE PROCEDURE stop_processing());

  存储过程语言有一个 RAISE EXCEPTION 的语句，可以通过抛出错误来中止存储过程（如果没有使用 ON EXCEPTION 语句在存储中捕获并处理错误）并将程序控制权返回给应用程序。RAISE EXCEPTION 语句可用于中止触发器事件和触发器动作。如果创建的数据库拥有日志记录，应用程序就可以回滚事务。

  错误编号-745 由触发器使用。用户将接收的错误消息为：

745:Trigger execution has failed.

  应用程序代码负责在触发 SQL 语句和发起 ROLLBACK WORK 之后检查错误。

  注意任何错误代码都可用在触发器调用的 RAISE EXCEPTION 语句中。您不必使用错误 -745。

10. 在应用程序里捕获错误

4GL示例代码：

BEGIN WORK
INSERT INTO tab1 VALUES(1,30)
IF(sqlca.sqlcode < 0) THEN
  DISPLAY 'error ',sqlca.sqlcode,
     ' on insert statement' ROLLBACK WORK
ELSE
COMMIT WORK
END IF

  错误 -745（或从触发器动作产生的任何其他 Sinoregal DS 动态服务器错误）会在 sqlca 结构体中被返回至应用程序。由应用程序开发者检查 SQL 语句执行之后的 sqlcode。 如上述部分代码示例所示，如果出现错误，开发者可以回滚事务。

10.1 自定义错误信息

使用错误代码 -746 展示顾客消息：

CREATE PROCEDURE stop_processing_col2() 
  RAISE EXCEPTION -746,0,
    'Error, col2 exceeds 20.'; 
END PROCEDURE;

CREATE TRIGGER trig1 INSERT ON tab1
  REFERENCING NEW AS new_val
  FOR EACH ROW WHEN(new_val.col2 > 20)
    (EXECUTE PROCEDURE stop_processing_col2());

  在 RAISE EXCEPTION 语句中使用错误 -746，自定义返回至用户的错误消息。RAISE EXCEPTION 语句的第三个参数是错误消息。自定义的文本内容被置于 sqlca 结构的 sqlerrm 字段中。可在第三个参数里放置任何带引号的文本，它将会作为错误消息被返回至应用程序。文本消息长度限制为小于 72 个字符。

11. 删除触发器

• 使用DROP TRIGGER 语句从数据库里删除触发器。

DROP TRIGGER  trig_name;

• 删除表会使在触发器事件子句中引用该表的触发器被删除。

• 更改表或删除列时，该列将从触发器事件里的触发器列列表中删除。在触发器动作中引用该表的触发器将不会被删除。必须自己找到并删除这些触发器。

12. 游标和触发器

• INSERT 游标：
  当行被刷新到数据库服务器时，每个 INSERT 语句将执行完整的触发器。

• UPDATE 游标：
  每个 UPDATE WHERE CURRENT OF 语句执行完整的触发器。

  使用插入游标可以提升性能，因为在发送至数据库服务器之前，它将部分插入信息缓存在应用程序内存中。执行 FLUSH 语句或缓冲区已满时，将插入的行一起刷新到数据库服务器中。将数据刷新到数据库服务器时，每行都会使触发器完整执行，在触发器看来它们都是一条单独的 INSERT 语句。SELECT游标也会为每行执行触发器。
  游标里的 UPDATE 或 DELETE 语句与单独的 UPDATE 或 DELETE 语句的表现不同。使用 WHERE CURRENT OF 子句的每个 UPDATE 或 DELETE 都会执行整个触发器。例如，如果游标更改了五行， BEFORE、FOR EACH 和 AFTER 触发器动作被执行了五次，每行一次。

12.1 UPDATE 游标示例

使用4GL代码示例：

WHENEVER ERROR CONTINUE
UPDATE customer
  SET customer.*= gr_customer.*
  WHERE CURRENT OF lockcust
  IF sqlca.sqlcode < 0 THEN 
    ERROR 'Error number ',
      sqlca.sqlcode USING '-<<<<', 
      ' has occurred.'
    ROLLBACK WORK 
ELSE
  MESSAGE 'Customer updated.'
COMMIT WORK

  上述展示的4GL 代码片段是通过使用游标执行UPDATE 语句的示例。CURRENT OF 关键字用于更新一个游标活动集合的当前行。在上述类似的情况下，如果UPDATE 语句激活了触发器，那么将会为每条UPDATE语句执行整个触发器（BEFORE、FOR EACH ROW 和 AFTER 操作），即使它发生在游标内。

13. 触发器和约束检查

• 在触发器动作执行期间约束检查被延迟执行

• 执行触发器完后，所有约束的都会被检查。

  如果为数据库启用了日志记录，当触发器动作开始执行时，数据库服务器将延迟所有约束的检查，直到触发器动作完成。所有约束都将在触发器动作完成后进行检查。这相当于在语句前运行 SET CONSTRAINTS ALL DEFERRED 和在语句后运行 SET CONSTRAINTS constraint_list IMMEDIATE。如果约束违例出现，将会产生错误。

14. 触发器的系统目录表

  两张专门用于存储触发器信息的系统目录表：

• systriggers :维护关于触发器的混杂信息。

• systrigbody :包含触发器的英文文本和用于执行触发器动作的代码。

15. 管理触发器

• 如果删除表，将会删除所有关联的触发器

• 如果删除数据库，将会删除所有触发器

• 建议像管理应用程序代码一样管理触发器

  触发器由SQL 语句创建，这使其易于创建和删除。触发器包含数据的重要规则，但是在谈到合适的源代码维护方法时，触发器却往往很容易被忽略。
  如果 DBA 在没有保存与数据库相关联的触发器的情况下，无意地删除了数据库（可能稍后重新创建），那么触发器将一起被删除。

16. 触发器与安全

• 如果没有 WITH GRANT OPTION，用户必须在触发器事件和动作涉及到的表上都具有权限。

• 如果存储过程是触发器动作的一部分，触发器用户应该通过WITH GRANT OPTION被授予 EXECUTE 权限，让所有用户都能执行存储过程。如果没有 WITH GRANT OPTION，每个用户都必须有执行该存储过程的权限。

• 此外，用户需要在存储过程里执行 SQL 语句的权限。创建一个DBA 存储过程允许任何用户在存储过程里执行语句。