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

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

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

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

例子

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

  1. 当该饰品公司接到订单时，订单的价格和客户未结帐的物料清单价格的总和不能超过提供给该客户的赊购最高限额。
  2. 一份订单可以有几种状态：PENDING、CANCELLED、SHIPPED、DELIVERED 和 COMPLETED。只有以下状态转换才是合法的：
PENDING -> SHIPPED -> DELIVERED 
            -> COMPLETED
                
  
PENDING -> CANCELLED
  1. 只有当订单被取消后才能删除。同时，我们也要将删除的订单的信息记录到另一表中以备审计之用。

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

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

 1<variablename> <type> [DEFAULT <value>]定义了触发器执行业务规则时需要用到的局部变量。 
 2
 3第 9 到 11 行举出了两种 DB2 SQL 过程语言允许的注释形式。您可以使用 /* 和 */ 来表示多行注释，也可以用 '- -'来表示单行注释。 
 4
 5第 10 行，我们从客户表中查询出他的赊购限额。这个查询的谓词 WHERE ord.cust_id=n.cust_id 保证了最多只返回一条结果（否则将抛出一个 SQL 错误）。谓词中的 'n.cust_id' 指出了激活这个触发器的 INSERT 语句中提供的相应列的值。 
 6
 7接着在第 12 行中， 我们用一个 FOR 循环查询该客户的所有订购中未付款的记录，并且定义了一个名为 ord_cursor 的只读游标。该游标查询出所有满足条件的订单的价格和数量，这些订单的状态既不能为 COMPLETED（款项已收到），也不能为 CANCELLED（取消订货）。根据每行返回的结果，我们将未付款的数额加起来就可以计算出目前总共未偿付的款额。 
 8
 9最后在第 16 行将现有的欠款之和加上新订单的价格与提供给该客户的赊购限额相比较。如果已经不能提供满足需求的赊购额，将引发一条 SQLSTATE 设置为 80000（使用 SIGNAL 语句）的应用程序错误，并显示消息“Order Exceeds credit line”。该错误可由应用程序恢复，而插入操作将被拒绝，所有的更改也将回退。这个错误将导致一个SQL 异常，该异常可由其调用应用程序处理。 
10
11**注意：** 目前错误消息的长度限制为 70 个字符。如果消息超出这个长度，将会被自动截掉。 
12
13在上面的示例中，我们假设每次插入只产生一条订购。如果应用程序在一条插入语句中插入多行记录，我们将不得不采用“AFTER”触发器，因为这一系列事件是按以下顺序发生的： 
14
15  1. 用户或者应用程序发出一条 INSERT 语句 
16  2. 在数据被真正插入之前，INSERT 触发器被激活并执行全部操作 
17  3. 如果触发器操作执行完成且没有错误，该行被插入。 
18
19
20
21因为 BEFORE 触发器是在数据被插入之前执行完毕，当一条 INSERT 语句要插入多行时，FOR 循环将不能看到用户或应用程序试图插入的所有行。 
22
23有一些优化方法使触发器执行得更快。这里设计的触发器主要是为了演示怎样使用新的触发器功能而不是着重于其性能。 
24
25Update 触发器    
26  
27除了对 Update 触发器中新数据和现有数据的引用可以被访问之外，Update 触发器和 Insert 触发器十分相似。根据前面的业务规则，我们希望使用触发器来定义有效的状态转换并在所有应用程序中实施。 
28
29**有效状态转换：**   
30PENDING -&gt; SHIPPED -&gt; DELIVERED -&gt; COMPLETED   
31PENDING -&gt; CANCELLED 
32
33下面的触发器可用来实施这些转换：</value></type></variablename>
Published At
Categories with 数据库类
comments powered by Disqus