簡(jiǎn)介
當(dāng)特定事件在 IBM® DB2® Universal Database™ 數(shù)據(jù)庫(kù)中發(fā)生時(shí)，您就可以激活 觸發(fā)器來(lái)執(zhí)行其他一些操作。在本文中，您將在觸發(fā)器的世界里遨游，看看如何通過(guò)觸發(fā)器來(lái)增強(qiáng)數(shù)據(jù)庫(kù)中的業(yè)務(wù)規(guī)則。您還將學(xué)習(xí)如何使用 DB2 UDB Version 8.1 的控制中心來(lái)幫助您創(chuàng)建一個(gè)應(yīng)用于簡(jiǎn)單業(yè)務(wù)場(chǎng)景的簡(jiǎn)單觸發(fā)器。
什么是觸發(fā)器
當(dāng)一個(gè)指定的 SQL 操作（如 DELETE，INSERT，或者是 UPDATE 操作）作用于某張表時(shí)，一個(gè)定義了一組操作的觸發(fā)器就可以被激活。觸發(fā)器并不像參照完整性約束和檢查約束那樣，我們甚至可以使用對(duì)其他表來(lái)進(jìn)行更新。
業(yè)務(wù)場(chǎng)景
將一項(xiàng)技術(shù)應(yīng)用于真實(shí)世界的一個(gè)場(chǎng)景總是有益的。出于教學(xué)的目的，讓我們?cè)谝粋€(gè)銀行相關(guān)環(huán)境中研究觸發(fā)器，在該模擬環(huán)境中，我們僅僅建立了一張表。再次強(qiáng)調(diào)，這是被簡(jiǎn)化了的！我們將要做的是，運(yùn)用觸發(fā)器來(lái)促進(jìn)銀行提供的透支保護(hù)。例如，一個(gè)銀行客戶有一個(gè)支票帳戶（checking account）和一個(gè)儲(chǔ)蓄帳戶（saving account）。當(dāng)從支票帳戶中取款的金額超過(guò)了該帳戶的余額時(shí)，就會(huì)發(fā)生一次自動(dòng)的轉(zhuǎn)帳（叫做透支保護(hù)），即自動(dòng)從客戶的儲(chǔ)蓄帳戶轉(zhuǎn)帳過(guò)來(lái)。當(dāng)然，這必須符合一定的條件，即儲(chǔ)蓄帳戶中必須有足夠多的錢(qián)來(lái)補(bǔ)償透支的金額。
開(kāi)始
像上面所提及的，我們的銀行僅僅包含一張表。在這張表中，我們將存入客戶的支票帳戶和儲(chǔ)蓄帳戶的余額等信息。每個(gè)客戶通過(guò)其社會(huì)保險(xiǎn)號(hào)碼來(lái)標(biāo)識(shí)。下面是對(duì)該表的描述：
表 1. 對(duì) 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 |
* 表示主鍵
請(qǐng)使用 DB2 命令行處理器為上面的表創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)。將數(shù)據(jù)庫(kù)命名為 bnkdb。
db2 => create database bnkdb
接下來(lái)，連接到該數(shù)據(jù)庫(kù)。我假設(shè)您已經(jīng)在您的機(jī)器上有了一個(gè)用戶名為 db2admin ， 密碼為 db2admin 的帳號(hào)。
db2 => connect to bnkdb user db2admin using db2admin
現(xiàn)在，創(chuàng)建 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)
|
現(xiàn)在向所創(chuàng)建的表中加入兩條記錄：
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)
|
觸發(fā)器可以在對(duì)表的一次 INSERT、 DELETE 或者 UPDATE 操作 之前或 之后啟動(dòng)。在我們的例子中，您將創(chuàng)建一個(gè)在對(duì)ACCTTABLE 表執(zhí)行 UPDATE 操作之前啟動(dòng)的觸發(fā)器。在觸發(fā)器術(shù)語(yǔ)中，INSERT、 DELETE 或者 UPDATE 這些使得觸發(fā)器啟動(dòng)的事件被稱(chēng)作 觸發(fā)事件。觸發(fā)器的啟動(dòng)是在觸發(fā)事件之前還是之后則稱(chēng)為觸發(fā)器的 激活時(shí)間。
使用 Control Center 創(chuàng)建觸發(fā)器
打開(kāi) DB2 Control Center 開(kāi)始創(chuàng)建觸發(fā)器，展開(kāi)您創(chuàng)建的數(shù)據(jù)庫(kù)（即 bnkdb），鼠標(biāo)右鍵點(diǎn)擊 Triggers 選項(xiàng)并且選擇 Create.....
圖 1. 創(chuàng)建觸發(fā)器
在 Create Trigger 屏幕中，可以指定觸發(fā)器所在的模式。請(qǐng)選擇 DB2ADMIN 模式。記住，觸發(fā)器是與表相關(guān)的，所以我們需要選擇相關(guān)表的模式。然后請(qǐng)?jiān)俅芜x擇 DB2ADMIN 模式：
圖 2. 選擇模式
在同一個(gè)屏幕中, 需要指定一個(gè)觸發(fā)器的名字。將觸發(fā)器命名為 OVERDRAFT。而且，需要指定與該觸發(fā)器相關(guān)的表的名字。這里選擇您創(chuàng)建的 ACCTTABLE。
圖 3. 選擇您創(chuàng)建的表
在 Time to trigger action區(qū)域中，選擇 Before。
圖 4. 選擇 Before
在 Operation that causes the trigger to be executed區(qū)域中選擇 Update of columns 操作并且指定被操作列為 CHECKINGBALANCE：
圖 5.
點(diǎn)擊 Triggered action標(biāo)簽頁(yè)來(lái)創(chuàng)建該觸發(fā)器：
圖 6. 構(gòu)建觸發(fā)器
指定臨時(shí)變量
DB2 UDB 能夠跟蹤在啟動(dòng)觸發(fā)器的那條語(yǔ)句之前和之后的一行的狀態(tài)。請(qǐng)?jiān)?Correlation name for the old rows 一欄中填入 OLDROW， 在 Correlation name for the new rows 一欄中填入 NEWROW ：
圖 7. 指定 NEWROW
注意，您也許無(wú)法指定其中的一個(gè) correlation name，因?yàn)樗蕾?lài)于引起觸發(fā)器啟動(dòng)的特定操作和激活時(shí)間的組合。例如，假設(shè)您的觸發(fā)器選擇的 Time to trigger action 是 Before，觸發(fā)事件是 DELETE 語(yǔ)句。在這種情況下，我們就無(wú)法指定一個(gè) "Correlation name for the new rows"。為什么呢？因?yàn)樵趫?zhí)行了一個(gè)刪除操作以后，新行是不存在的。
因?yàn)槟鷦?chuàng)建的觸發(fā)器是在 UPDATE 之前被激活，所以不能編輯 Temporary table for the old rows 和 Temporary table for the new rows選項(xiàng)。
您將注意到，在這種情況下（一個(gè)在 UPDATE 之前被激活的觸發(fā)器），您只能指定觸發(fā)器針對(duì) 每 行而不是針對(duì)每個(gè) 語(yǔ)句觸發(fā)。
圖 8. 觸發(fā)器觸發(fā)于每行
引起觸發(fā)事件的語(yǔ)句可能會(huì)同時(shí)影響數(shù)據(jù)庫(kù)中的多行。"For each Row" 選項(xiàng)意味著觸發(fā)器將在每一行被修改時(shí)激活。另一方面，"For each statement" 選項(xiàng)（"before" 型觸發(fā)器是不允許的）則意味著觸發(fā)器定義的操作只在調(diào)用一次 SQL 語(yǔ)句后執(zhí)行一次。
可以點(diǎn)擊 Show SQL按鈕來(lái)看看底層的 SQL 語(yǔ)句到目前為止是什么樣子：
圖 9. Show SQL 框
構(gòu)建觸發(fā)動(dòng)作
現(xiàn)在該創(chuàng)建觸發(fā)動(dòng)作了。我們的業(yè)務(wù)規(guī)則是支票帳戶的余額必須低于 0 才能激活該觸發(fā)器。也就是說(shuō)，我們需要指定 search-condition 為 NEWROW.CHECKINGBALANCE<0 。我們之所以指定 NEWROW.CHECKINGBALANCE 是因?yàn)樾枰治鲈?update 操作之后支票帳戶的余額將會(huì)是多少。
創(chuàng)建觸發(fā)器主體
現(xiàn)在我們將要在 Triggered Action 文本區(qū)域中替換 triggered-SQL-statement （參見(jiàn)下面）。
圖 10. 創(chuàng)建觸發(fā)器語(yǔ)句
用來(lái)替換的代碼如下:
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;
|
讓我們一句一句地仔細(xì)研究一下這段代碼。在觸發(fā)器主體中，可以聲明將要在主體中使用的變量。我們使用下面這行代碼來(lái)聲明變量： decimal(7,2)declare overage decimal (7,2) ; 這樣就定義了一個(gè)類(lèi)型為 decimal(7,2) 、 名為
下一步我們將 overage 變量的值設(shè)置為 (NEWROW.CHECKINGBALANCE*-1) ;
我們將使用該算式計(jì)算出我們想要從支票帳戶取出的超額(overage)的數(shù)目。指定 NEWROW.CHECKINGBALANCE 是因?yàn)槲覀冃枰治鲋睅舻挠囝~在 update 操作發(fā)生后是多少。
set overage = (NEWROW.CHECKINGBALANCE*-1);
發(fā)出錯(cuò)誤條件信號(hào)
如果違反了您在觸發(fā)器中定義的業(yè)務(wù)規(guī)則，就可以使用 SIGNAL 語(yǔ)句來(lái)拋出一個(gè)錯(cuò)誤條件信號(hào)。在我們的例子中，不允許有人擁有的支票帳戶余額為負(fù)數(shù)。如果有人想要將支票帳戶的余額列更新為一個(gè)負(fù)數(shù)，我們就可以試著看看在儲(chǔ)蓄帳戶中是否有足夠多的錢(qián)來(lái)補(bǔ)償這個(gè)負(fù)數(shù)。如果沒(méi)有，那么就可以發(fā)出一條 SQL 狀態(tài)為 '70001' 的信息 "'Overdraft Protection Unsuccessful"。
認(rèn)識(shí)到包含 SIGNAL 語(yǔ)句的效果是很重要的。SIGNAL 語(yǔ)句回滾由觸發(fā)語(yǔ)句（也就是我們的 update 語(yǔ)句）嘗試的更改。SIGNAL 語(yǔ)句也將回滾在觸發(fā)器內(nèi)發(fā)生的更改。此外，假設(shè)我們是使用 Java™ 應(yīng)用程序來(lái)與數(shù)據(jù)庫(kù)進(jìn)行交互的，并且試圖執(zhí)行一次會(huì)觸發(fā)我們的觸發(fā)器并違反業(yè)務(wù)規(guī)則的 update 操作。Java 應(yīng)用程序?qū)⒔邮芪覀兯付ǖ?SQLSTATE 以及值為 -438 的 SQLCODE。在這行中我們使用 SIGNAL SQLSTATE 屬性：
if overage>OLDROW.SAVINGBALANCE then SIGNAL SQLSTATE '70001'
('Overdraft Protection Unsuccessful');
|
該行說(shuō)明，如果我們的 overage 比儲(chǔ)蓄帳戶的余額數(shù)目還要大，那么就需要拋出一個(gè)危險(xiǎn)信號(hào)。
轉(zhuǎn)帳
如果儲(chǔ)蓄帳戶的余額數(shù)目足夠補(bǔ)償超出的數(shù)目，這時(shí)就會(huì)發(fā)生轉(zhuǎn)帳。如果滿足這種條件，我們將對(duì)新行作兩處修改：
- 修改 "new row" 的 savingbalance 列，將其減去 overage 以促成透支轉(zhuǎn)帳。
- 將支票帳戶的余額設(shè)置為 0。我們使用下面的代碼來(lái)完成：
else set newrow.savingbalance = oldrow.savingbalance-overage,
newrow.checkingbalance = 0; end if;
|
最終結(jié)果
可以再次通過(guò) Show SQL按鈕來(lái)看看最后的結(jié)果：
圖 11.顯示 SQL
在點(diǎn)擊 Close之后，您將看到 OVERDRAFT 觸發(fā)器已經(jīng)創(chuàng)建好了：
圖 12. 創(chuàng)建 OVERDRAFT 觸發(fā)器
測(cè)試
可以通過(guò)一個(gè) update 語(yǔ)句來(lái)進(jìn)行測(cè)試。打開(kāi)命令行編寫(xiě)下面的語(yǔ)句：
db2=> update accttable set checkingbalance = -500 where ssn='111-11-1111'
|
根據(jù)我們創(chuàng)建的業(yè)務(wù)邏輯，這個(gè) update 操作將啟動(dòng)該觸發(fā)器，由于支票帳戶透支，該觸發(fā)器將從 savingbalance 列取出 500.00 到支票帳戶。因此，SSN 為 111-11-1111 的帳戶的 checkingbalance 會(huì)變成 0.00 而 savingbalance 將變成 1000.00（原來(lái)的余額 1500 － 透支的 500）。下面的查詢驗(yàn)證了該結(jié)果：
圖 13. 查詢
尾聲
您已經(jīng)在一個(gè)假想的業(yè)務(wù)場(chǎng)景中創(chuàng)建了一個(gè) DB2 觸發(fā)器。觸發(fā)器是 DB2 數(shù)據(jù)庫(kù)的一個(gè)非常強(qiáng)大的特性，您可以使用它將業(yè)務(wù)邏輯分化到關(guān)系數(shù)據(jù)庫(kù)這邊。如果考慮到有多個(gè)應(yīng)用程序都將與同一個(gè)數(shù)據(jù)庫(kù)進(jìn)行交互，您就會(huì)發(fā)現(xiàn)這種分化是非常強(qiáng)大的。在一個(gè)大型企業(yè)中，您可能多次遇到過(guò)這樣的情況，即不知道要?jiǎng)?chuàng)建的是怎樣一個(gè)將與數(shù)據(jù)庫(kù)交互的應(yīng)用程序。與其只是希望這些應(yīng)用程序遵守被認(rèn)為是您的組織機(jī)構(gòu)的戒律的業(yè)務(wù)規(guī)則，還不如使用觸發(fā)器作為您工具箱中的一種工具，以確?，F(xiàn)在和將來(lái)與數(shù)據(jù)庫(kù)進(jìn)行交互的所有的應(yīng)用程序強(qiáng)制遵守這些業(yè)務(wù)規(guī)則。一個(gè)觸發(fā)器只能關(guān)聯(lián)一個(gè)表，而不能關(guān)聯(lián)一個(gè)視圖。您也許可以考慮使用 INSTEAD OF 觸發(fā)器來(lái)與視圖交互。