在DB2中使用SQL过程语言来操作触发器-技术开发专区

在DB2中使用SQL过程语言来操作触发器

作者：IBM 编辑：魏国亮 2006-08-22 20:19

【IT168 技术文档】首先，触发器是与表相关联的对象，它定义了一些在 INSERT、UPDATE 或者是 DELETE 时自动发生的操作。下面是一些可能需要用到触发器的例子：

    当插入时，在允许插入操作发生之前验证或者操纵数据。
    当更新时，通过比较新值与原值来验证操作的正确性。如果您用表中的一列来存储状态信息并且希望定义有效的状态转换时，这样做特别有用。
    执行删除操作之后，自动地将日志信息插入另一个用于审计追踪的表中。

触发器能在数据库层面上可用于集中业务规则的实施，使所有的应用程序和用户不必再去检查数据的有效性。同样地，如果业务规则发生改变，这些改变在数据库层上就被集中了，而不需要通过所有正在执行的应用程序来传播。

例子

为了说明上述功能，下面的例子为一个虚构的饰品公司的数据库创建表和触发器，该数据库保存了公司的订单和客户信息。要求实施以下业务规则：

当该饰品公司接到订单时，订单的价格和客户未结帐的物料清单价格的总和不能超过提供给该客户的赊购最高限额。
一份订单可以有几种状态：PENDING、CANCELLED、SHIPPED、DELIVERED 和 COMPLETED。只有以下状态转换才是合法的：

    PENDING -> SHIPPED -> DELIVERED
    -> COMPLETED
    PENDING -> CANCELLED

只有当订单被取消后才能删除。同时，我们也要将删除的订单的信息记录到另一表中以备审计之用。

我们将定义下面的表来说明这个例子。

  create table customer_t (
    cust_id INT NOT NULL PRIMARY KEY,
    company_name VARCHAR(100),
    credit DECIMAL(10,2))
    create table product_t (
    product_id INT NOT NULL PRIMARY KEY,
    product_name VARCHAR(100))
    create table orders_t (
    order_id INT NOT NULL PRIMARY KEY,
    cust_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    status CHAR(9) NOT NULL,
    FOREIGN KEY (cust_id) REFERENCES customer_t,
    FOREIGN KEY (product_id) REFERENCES product_t)
    create table delete_log_t (
    Text varchar(1000))

我们还将定义以下序列对象（DB2 UDB 7.2 中新增的）来生成唯一的 ID:

    create sequence cust_seq
    create sequence prod_seq
    create sequence ord_seq

接下来，我们将插入下列数据：

    insert into customer_t values
    (NEXTVAL FOR cust_seq, 'Nancys Widgets', 100)

    insert into product_t values
    (NEXTVAL FOR prod_seq, 'Blue Widgets')

最后，我们将创建触发器来实施前面定义的业务逻辑，并解释该过程。

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 循环将不能看到用户或应用程序试图插入的所有行。

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

Update 触发器

除了对 Update 触发器中新数据和现有数据的引用可以被访问之外，Update 触发器和 Insert 触发器十分相似。根据前面的业务规则，我们希望使用触发器来定义有效的状态转换并在所有应用程序中实施。

有效状态转换：

PENDING -> SHIPPED -> DELIVERED -> COMPLETED
PENDING -> CANCELLED

下面的触发器可用来实施这些转换：

  1 : CREATE TRIGGER verify_state
    2 : NO CASCADE BEFORE UPDATE ON orders_t
    3 : REFERENCING OLD AS o NEW AS n
    4 : FOR EACH ROW MODE DB2SQL
    5 : BEGIN ATOMIC
    6 :IF o.status='PENDING' and n.status IN ('SHIPPED','CANCELLED') THEN
    7 :-- valid state 
    8 :ELSEIF o.status='SHIPPED' and
    9 :n.status ='DELIVERED' THEN
    10:-- valid state 
    11:ELSEIF o.status='DELIVERED' and
    12:n.status = 'COMPLETED' THEN
    13:-- valid state 
    14:ELSE
    15:SIGNAL SQLSTATE '80001' ('Invalid State Transition');
    16:END IF;
    17: END

本例中，触发器名为 verify_state。它是在对表 orders_t 进行更新之前被激活的。另一个不同就是我们用 o 和 n 作为列名限定符分别引用旧值和新值。

第 5 行到第 16 行中的这种状态转换方式是直接的。如果转换没有执行，我们则假定有错误并引发一个应用程序错误，发出消息“Invalid State Transition”。操作将被拒绝。当然，判断逻辑也可写成如下形式：

   IF NOT((o.status='PENDING' and n.status IN ('SHIPPED','CANCELLED')) OR
    (o.status='SHIPPED' and n.status = 'DELIVERED' OR
    (o.status='DELIVERED' and n.status = 'COMPLETED')) THEN
    SIGNAL SQLSTATE '80001' ('Invalid State Transition')
    END IF;

...前面的写法是为了意思更清晰并能充分说明 IF/THEN/ELSE 结构的句法。

Delete 触发器

对于最后一条业务规则，我们将说明触发器的更简单形式，这在 DB2 UDB 7.2 之前就已经可用了。先将业务规则分为以下两部分：

3a)“如果订单没有被取消就不能被删除。”
3b)“记录已删除的订单信息以备审计。”

下面是实施 3a 的触发器：

    1 : CREATE TRIGGER restrict_delete
    2 : NO CASCADE BEFORE DELETE ON orders_t
    3 : REFERENCING OLD AS o
    4 : FOR EACH ROW MODE DB2SQL
    5 : WHEN (o.status <> 'CANCELLED')
    6 :SIGNAL SQLSTATE '80003' ('Cannot Delete an order that has not been cancelled')

“AFTER” 触发器

对于规则 3b，我们将用一个 AFTER 触发器来记录表orders_t中的删除操作。

    1 : CREATE TRIGGER log_delete
    2 : AFTER DELETE ON orders_t
    3 : REFERENCING OLD AS o
    4 : FOR EACH ROW MODE DB2SQL
    5 :INSERT INTO delete_log_t VALUES (
    'rder #?|| CHAR (o.order_id) ||
    'as deleted on ?|| CHAR(CURRENT TIMESTAMP));

该 delete 触发器和前面两个触发器的不同之处在于，这里没有用到 BEGIN ATOMIC 和 END。因为如果在触发器中只有一条 SQL 语句，就不是必需的。上面这个触发器显然没有记录太多有用的信息来支持审计，但演示了如何通过触发器使对一个表的插入操作来触发完成对另一个表的插入。任何时候执行删除操作且满足WHEN 子句中的条件都将激活该触发器。如果你将 WHEN 子句完全删掉（“AFTER”触发器上面），该触发器将一直处于激活状态。

测试规则

先测试业务规则 1，我们将插入两份来自于 Nancy 的饰品订单。这位客户的赊购最高限额仅为 $100，因此第一份订单被成功插入，而第二份则操作失败。（每份订单的价格均为 $90。）

   Insert into orders_t values (nextval for ord_seq, 1, 1, 9, 10.0, 'ENDING')
    Insert into orders_t values (nextval for ord_seq, 1, 1, 9, 10.0, 'ENDING')

我们可以在前面操作的基础上测试业务规则 2。鉴于订单的有效状态转换（前面已提到），状态为“PENDING”的订单可转换为“SHIPPED”。一旦订单物品已经发送，就不能被取消了。下面的第一个更新操作可以成功，但第二个将无法通过。

    Update orders_t set status='HIPPED' where order_id=1
    Update orders_t set status='ANCELLED' where order_id=1

我们只需试图去删除刚才插入的订单就可以测试业务规则 3。下面的删除语句将失败，因为该订单的状态不是“CANCELLED”。而且由于前面的这个触发器触发失败，用于记录删除操作的 AFTER DELETE 触发器也不会被激活。

Delete from orders_t where order_id=1

如果要测试这个用于记录删除操作的触发器，我们可以插入一份不超过 Nancy 最高赊购限额的订单，然后取消并删除它。

    Insert into orders_t values (nextval for ord_seq, 1, 1, 1, 10.0, 'PENDING')
    Update orders_t set status='CANCELLED' where order_id=(prevval for ord_seq)
    Delete from orders_t where order_id=(prevval for ord_seq)
    Select * from delete_log_t

性能技巧

BEFBEFORE 触发器应该用于修改用户输入的值或用于生成新值，如生成主键。在 AFTER 触发器中试图修改转换表中的行更为复杂。

DB2 是一个功能强大的关系引擎。然而，目前它并没有优化过程逻辑（控制）语句和其他 SQL 语句。

例如 verify_credit 触发器也可以改写成下面这样：

    1 : CREATE TRIGGER verify_credit
    2 : NO CASCADE BEFORE INSERT ON orders_t
    3 : REFERENCING NEW AS n
    4 : FOR EACH STATEMENT MODE DB2SQL
    5 : WHEN ((SELECT SUM(price * quantity) FROM orders_t
    6 :WHERE cust_id = n.cust_id
    7 :AND status NOT IN ('COMPLETED', 'CANCELLED'))
    8 :+ n.price * n.quality
    9 :> (SELECT credit FROM customer_t WHERE cust_id=n.cust_id))
    10: SIGNAL SQLSTATE '80000' ('Order Exceeds credit line')

当你需要比较新值与旧值时，对于更新来说，最好选用 FOR EACH ROW 触发器，而不是 FOR EACH STATEMENT 触发器。使用 FOR EACH ROW 触发器可以获得更好的性能，因为每一行都包含用于比较的值，即可完成触发器的操作，而不需要使用存储在转换表中的所有新值和旧值。

其他技巧

    支持 WHILE 循环。
    GET DIAGNOSTICS <var : int> = ROW_COUNT 可用于确定在触发器主体中最近调用的 update，delete 或 insert 语句影响了多少行记录。
    不支持 SELECT .... INTO 语法。要将多列属性值传给多个变量应使用 use SET (x,y) = (SELECT x_col, y_col FROM mytable)。
    避免使用递归触发器。递归触发器是指该触发器会被自身主体中相同的语句激活。例如，如果我们在表 mytable 上定义一个 DELETE 触发器，而其主体中也包含一个 mytable 上的DELETE 语句，这就是一个递归触发器。若不小心编码，就会产生问题。即使需要用到递归，也应限于使用单个迭代。
    如果在一个表上定义多个触发器（也就是说，两个 BEFORE INSERT 触发器被定义），他们将按照被创建的次序来被执行。当然，BEFORE 触发器总是在 AFTER 触发器之前被激活而不必考虑其创建次序。表的其他约束条件（如主/外键约束，唯一性约束以及检查约束）同样会在 BEFORE 触发器之后，AFTER 触发器之前被检查。

结束语

触发器对于实施业务规则十分有效，而这些业务规则恰恰是数据库应用程序的中心。

关注我们