在DB2中创建第一个触发器

　　当特定事件在 IBM® DB2® Universal Database™ 数据库中发生时，您就可以激活 触发器来执行其他一些操作。在本文中，您将在触发器的世界里遨游，看看如何通过触发器来增强数据库中的业务规则。您还将学习如何使用 DB2 UDB Version 8.1 的控制中心来帮助您创建一个应用于简单业务场景的简单触发器。

　　什么是触发器

当一个指定的 SQL 操作（如 DELETE，INSERT，或者是 UPDATE 操作）作用于某张表时，一个定义了一组操作的触发器就可以被激活。触发器并不像参照完整性约束和检查约束那样，我们甚至可以使用对其他表来进行更新。

　　业务场景

将一项技术应用于真实世界的一个场景总是有益的。出于教学的目的，让我们在一个银行相关环境中研究触发器，在该模拟环境中，我们仅仅建立了一张表。再次强调，这是被简化了的！我们将要做的是，运用触发器来促进银行提供的透支保护。例如，一个银行客户有一个支票帐户（checking account）和一个储蓄帐户（saving account）。当从支票帐户中取款的金额超过了该帐户的余额时，就会发生一次自动的转帐（叫做透支保护），即自动从客户的储蓄帐户转帐过来。当然，这必须符合一定的条件，即储蓄帐户中必须有足够多的钱来补偿透支的金额。

　　开始

像上面所提及的，我们的银行仅仅包含一张表。在这张表中，我们将存入客户的支票帐户和储蓄帐户的余额等信息。每个客户通过其社会保险号码来标识。下面是对该表的描述：

　　表 1. 对 ACCTTABLE 的描述

Column Name	Column Type	Nullable?
SSN*	Varchar(11)	NO
LastName	Varchar(30)	NO
FirstName	Varchar(30)	NO
SavingBalance	Decimal (Precision: 7, Scale: 2)	NO
CheckingBalance	Decimal (Precision: 7, Scale: 2)	NO

* 表示主键

　　请使用 DB2 命令行处理器为上面的表创建一个数据库。将数据库命名为 bnkdb。

　　db2 => create database bnkdb

　　接下来，连接到该数据库。我假设您已经在您的机器上有了一个用户名为 db2admin ， 密码为 db2admin 的帐号。

　　db2 => connect to bnkdb user db2admin using db2admin

　　现在，创建 accttable 表：

db2 => create table accttable(ssn varchar(30) not null primary key, 
lastname varchar(30) not null, firstname varchar(30) not null, 
savingbalance decimal(7,2) not null, checkingbalance decimal(7,2) not null)

　　现在向所创建的表中加入两条记录：

db2 => insert into accttable values
('111-11-1111','Bhogal','Kulvir',1500.00,1000)

db2 => insert into accttable values
('222-22-2222','Guy','Someother',2000.00,4000)

　　触发器可以在对表的一次 INSERT、 DELETE 或者 UPDATE 操作 之前或 之后启动。在我们的例子中，您将创建一个在对ACCTTABLE 表执行 UPDATE 操作之前启动的触发器。在触发器术语中，INSERT、 DELETE 或者 UPDATE 这些使得触发器启动的事件被称作 触发事件。触发器的启动是在触发事件之前还是之后则称为触发器的 激活时间。

　　使用 Control Center 创建触发器

  打开 DB2 Control Center 开始创建触发器，展开您创建的数据库（即 bnkdb），鼠标右键点击 Triggers 选项并且选择 Create.....

　　图 1. 创建触发器

 

　　在 Create Trigger 屏幕中，可以指定触发器所在的模式。请选择 DB2ADMIN 模式。记住，触发器是与表相关的，所以我们需要选择相关表的模式。然后请再次选择 DB2ADMIN 模式：

　　图 2. 选择模式

 

　　在同一个屏幕中, 需要指定一个触发器的名字。将触发器命名为 OVERDRAFT。而且，需要指定与该触发器相关的表的名字。这里选择您创建的 ACCTTABLE。

　　图 3. 选择您创建的表

 

　　在 Time to trigger action区域中，选择 Before。

　　图 4. 选择 Before

 

　　在 Operation that causes the trigger to be executed区域中选择 Update of columns 操作并且指定被操作列为 CHECKINGBALANCE：

　　图 5.

 

　　点击 Triggered action标签页来创建该触发器：

　　图 6. 构建触发器

 

　　指定临时变量

DB2 UDB 能够跟踪在启动触发器的那条语句之前和之后的一行的状态。请在 Correlation name for the old rows 一栏中填入 OLDROW， 在 Correlation name for the new rows 一栏中填入 NEWROW ：

　　图 7. 指定 NEWROW

 

　　注意，您也许无法指定其中的一个 correlation name，因为它依赖于引起触发器启动的特定操作和激活时间的组合。例如，假设您的触发器选择的 Time to trigger action 是 Before，触发事件是 DELETE 语句。在这种情况下，我们就无法指定一个 "Correlation name for the new rows"。为什么呢？因为在执行了一个删除操作以后，新行是不存在的。

　　因为您创建的触发器是在 UPDATE 之前被激活，所以不能编辑 Temporary table for the old rows 和 Temporary table for the new rows选项。

　　您将注意到，在这种情况下（一个在 UPDATE 之前被激活的触发器），您只能指定触发器针对 每 行而不是针对每个 语句触发。

 　8. 触发器触发于每行

 

　　引起触发事件的语句可能会同时影响数据库中的多行。"For each Row" 选项意味着触发器将在每一行被修改时激活。另一方面，"For each statement" 选项（"before" 型触发器是不允许的）则意味着触发器定义的操作只在调用一次 SQL 语句后执行一次。

　　可以点击 Show SQL按钮来看看底层的 SQL 语句到目前为止是什么样子：

　　图 9. Show SQL 框

 

　　构建触发动作

　　现在该创建触发动作了。我们的业务规则是支票帐户的余额必须低于 0 才能激活该触发器。也就是说，我们需要指定 search-condition 为 NEWROW.CHECKINGBALANCE<0 。我们之所以指定 NEWROW.CHECKINGBALANCE 是因为需要分析在 update 操作之后支票帐户的余额将会是多少。

　　创建触发器主体

现在我们将要在 Triggered Action 文本区域中替换 triggered-SQL-statement （参见下面）。

　　图 10. 创建触发器语句

 

　　用来替换的代码如下:

declare overage decimal (7,2); 
set overage = (NEWROW.CHECKINGBALANCE*-1); 
if overage>OLDROW.SAVINGBALANCE then SIGNAL SQLSTATE '70001' 
('Overdraft Protection Unsuccessful'); 
  else set newrow.savingbalance = 
  oldrow.savingbalance-overage, newrow.checkingbalance = 0; 
end if;

　　让我们一句一句地仔细研究一下这段代码。在触发器主体中，可以声明将要在主体中使用的变量。我们使用下面这行代码来声明变量： decimal(7,2)declare overage decimal (7,2) ; 这样就定义了一个类型为 decimal(7,2) 、 名为 overage 的变量。

　　下一步我们将 overage 变量的值设置为 (NEWROW.CHECKINGBALANCE*-1) ;

　　我们将使用该算式计算出我们想要从支票帐户取出的超额(overage)的数目。指定 NEWROW.CHECKINGBALANCE 是因为我们需要分析支票帐户的余额在 update 操作发生后是多少。

　　set overage = (NEWROW.CHECKINGBALANCE*-1);

　　发出错误条件信号

  如果违反了您在触发器中定义的业务规则，就可以使用 SIGNAL 语句来抛出一个错误条件信号。在我们的例子中，不允许有人拥有的支票帐户余额为负数。如果有人想要将支票帐户的余额列更新为一个负数，我们就可以试着看看在储蓄帐户中是否有足够多的钱来补偿这个负数。如果没有，那么就可以发出一条 SQL 状态为 '70001' 的信息 "'Overdraft Protection Unsuccessful"。

　　认识到包含 SIGNAL 语句的效果是很重要的。SIGNAL 语句回滚由触发语句（也就是我们的 update 语句）尝试的更改。SIGNAL 语句也将回滚在触发器内发生的更改。此外，假设我们是使用 Java™ 应用程序来与数据库进行交互的，并且试图执行一次会触发我们的触发器并违反业务规则的 update 操作。Java 应用程序将接受我们所指定的 SQLSTATE 以及值为 -438 的 SQLCODE。在这行中我们使用 SIGNAL SQLSTATE 属性：

if overage>OLDROW.SAVINGBALANCE then SIGNAL SQLSTATE '70001' 
('Overdraft Protection Unsuccessful');

　　该行说明，如果我们的 overage 比储蓄帐户的余额数目还要大，那么就需要抛出一个危险信号。

　　转帐

如果储蓄帐户的余额数目足够补偿超出的数目，这时就会发生转帐。如果满足这种条件，我们将对新行作两处修改：

1. 修改 "new row" 的 savingbalance 列，将其减去 overage 以促成透支转帐。
2. 将支票帐户的余额设置为 0。我们使用下面的代码来完成：

else set newrow.savingbalance = oldrow.savingbalance-overage,             
newrow.checkingbalance = 0; end if;

　　最终结果

可以再次通过 Show SQL按钮来看看最后的结果：

　　图 11.显示 SQL

 

　　在点击 Close之后，您将看到 OVERDRAFT 触发器已经创建好了：

　　图 12. 创建 OVERDRAFT 触发器

 

　　测试

可以通过一个 update 语句来进行测试。打开命令行编写下面的语句：

db2=> update accttable set checkingbalance = -500 where ssn='111-11-1111'

　　根据我们创建的业务逻辑，这个 update 操作将启动该触发器，由于支票帐户透支，该触发器将从 savingbalance 列取出 500.00 到支票帐户。因此，SSN 为 111-11-1111 的帐户的 checkingbalance 会变成 0.00 而 savingbalance 将变成 1000.00（原来的余额 1500 － 透支的 500）。下面的查询验证了该结果：

　　图 13. 查询

 

　　尾声

    您已经在一个假想的业务场景中创建了一个 DB2 触发器。触发器是 DB2 数据库的一个非常强大的特性，您可以使用它将业务逻辑分化到关系数据库这边。如果考虑到有多个应用程序都将与同一个数据库进行交互，您就会发现这种分化是非常强大的。在一个大型企业中，您可能多次遇到过这样的情况，即不知道要创建的是怎样一个将与数据库交互的应用程序。与其只是希望这些应用程序遵守被认为是您的组织机构的戒律的业务规则，还不如使用触发器作为您工具箱中的一种工具，以确保现在和将来与数据库进行交互的所有的应用程序强制遵守这些业务规则。一个触发器只能关联一个表，而不能关联一个视图。您也许可以考虑使用 INSTEAD OF 触发器来与视图交互。

分享到：

IT程序员35岁之后的三条活路 | db2 基础

• 2008-01-10 09:27
• 浏览 1409
• 评论(0)
• 查看更多