簡介
當特定事件在 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 變量的值設置為 (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 比儲蓄帳戶的余額數目還要大，那么就需要拋出一個危險信號。
轉帳
如果儲蓄帳戶的余額數目足夠補償超出的數目，這時就會發生轉帳。如果滿足這種條件，我們將對新行作兩處修改：
- 修改 "new row" 的 savingbalance 列，將其減去 overage 以促成透支轉帳。
- 將支票帳戶的余額設置為 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 觸發器來與視圖交互。