Insert 触发器

    我们创建第一个触发器来实施业务规则#1： “当饰品公司接到订单时，订单的价格和客户未结帐的物品清单价格的总和不能超过提供给该客户的赊购最高限额。”

  1 : CREATE TRIGGER verify_credit
    2 : NO CASCADE BEFORE INSERT ON orders_t
    3 : REFERENCING NEW AS n
    4 : FOR EACH ROW MODE DB2SQL
    5 : BEGIN ATOMIC
    6 :DECLARE current_due DECIMAL(10,2) DEFAULT 0;
    7 :DECLARE credit_line DECIMAL(10,2);
    9 :/**//*
    * get the customer's credit line
    */
    10:SET credit_line = (SELECT credit
    FROM customer_t c
    WHERE c.cust_id=n.cust_id);
    11:-- sum up the current amount currently due 
    12:FOR ord_cursor AS
    13:SELECT quantity, price
    FROM orders_t ord
    WHERE ord.cust_id=n.cust_id AND
    status not IN ('COMPLETED','CANCELLED') DO
    14:SET current_due = current_due +
    (ord_cursor.price * ord_cursor.quantity);
    15:END FOR;
    16:IF (current_due + n.price * n.quantity) >credit_line THEN
    17:SIGNAL SQLSTATE '80000' ('Order Exceeds credit line');
    18:END IF;
    19: END

     第 1 行的CREATE TRIGGER语句仅仅说明我们正在创建一个名为verify_credit的触发器。

    NO CASCADE BEFORE INSERT ON orders_t意味着触发的操作是在数据实际插入表之前发生的，并且触发器的操作将不会导致激活任何其他触发器。对于所有的 BEFORE触发器，要加上关键字NO CASCADE。

    在引用新数据插入的列时，REFERENCING NEW AS n 指明 n 为必需的限定符。

    FOR EACH ROW 意味着触发器在每行被插入时激活。 另一种 FOR EACH STATEMENT（仅用于“ AFTER ”触发器）则意味着触发器在每条 SQL 语句执行时被激活。换句话说，假如一条 INSERT 语句从其他表选取 10 行插入，使用 FOR EACH ROW 将导致触发器要被激活10次，而如果使用 FOR EACH STATEMENT，触发器的操作则只需要执行一次。MODE DB2SQL 只是一个必须指明的子句。

    第 5 行的BEGIN ATOMIC 到第 19 行的 END 定义了触发器的主体。BEGIN ATOMIC 规定了触发器里的操作要么不执行，要么就要全部执行。如果在触发器操作执行过程中发生了错误，所有操作都将回退以维护数据的完整性。

    第6、7行的DECLARE <variableName> <type> [DEFAULT <value>]定义了触发器执行业务规则时需要用到的局部变量。

    第 9 到 11 行举出了两种 DB2 SQL 过程语言允许的注释形式。您可以使用 /* 和 */ 来表示多行注释，也可以用 '- -'来表示单行注释。

    第 10 行，我们从客户表中查询出他的赊购限额。这个查询的谓词 WHERE ord.cust_id=n.cust_id 保证了最多只返回一条结果（否则将抛出一个 SQL 错误）。谓词中的 'n.cust_id' 指出了激活这个触发器的 INSERT 语句中提供的相应列的值。

    接着在第 12 行中， 我们用一个 FOR 循环查询该客户的所有订购中未付款的记录，并且定义了一个名为 ord_cursor 的只读游标。该游标查询出所有满足条件的订单的价格和数量，这些订单的状态既不能为 COMPLETED（款项已收到），也不能为 CANCELLED（取消订货）。根据每行返回的结果，我们将未付款的数额加起来就可以计算出目前总共未偿付的款额。

    最后在第 16 行将现有的欠款之和加上新订单的价格与提供给该客户的赊购限额相比较。如果已经不能提供满足需求的赊购额，将引发一条 SQLSTATE 设置为 80000（使用 SIGNAL 语句）的应用程序错误，并显示消息“Order Exceeds credit line”。该错误可由应用程序恢复，而插入操作将被拒绝，所有的更改也将回退。这个错误将导致一个SQL 异常，该异常可由其调用应用程序处理。

    注意：目前错误消息的长度限制为 70 个字符。如果消息超出这个长度，将会被自动截掉。

    在上面的示例中，我们假设每次插入只产生一条订购。如果应用程序在一条插入语句中插入多行记录，我们将不得不采用“AFTER”触发器，因为这一系列事件是按以下顺序发生的：

    用户或者应用程序发出一条 INSERT 语句
    在数据被真正插入之前，INSERT 触发器被激活并执行全部操作
    如果触发器操作执行完成且没有错误，该行被插入。

    因为 BEFORE 触发器是在数据被插入之前执行完毕，当一条 INSERT 语句要插入多行时，FOR 循环将不能看到用户或应用程序试图插入的所有行。

    有一些优化方法使触发器执行得更快。这里设计的触发器主要是为了演示怎样使用新的触发器功能而不是着重于其性能。