摘 要： 針對初學(xué)者容易混淆AFTER觸發(fā)器與INSTEAD OF觸發(fā)器的問題，首先用通俗的語言對觸發(fā)器進行了概述，接著闡述了觸發(fā)器的工作原理，然后結(jié)合實例分析了AFTER觸發(fā)器與INSTEAD OF觸發(fā)器的主要區(qū)別與應(yīng)用，最后總結(jié)了觸發(fā)器應(yīng)用的注意事項。

　　關(guān)鍵詞： SQL Server；數(shù)據(jù)庫；觸發(fā)器；AFTER；INSTEAD OF

0 引言

　　觸發(fā)器是SQL Server數(shù)據(jù)庫教學(xué)中的重要內(nèi)容之一，觸發(fā)器的教學(xué)重點是DML觸發(fā)器的創(chuàng)建、管理與應(yīng)用，而DML觸發(fā)器的難點是AFTER觸發(fā)器與INSTEAD OF觸發(fā)器的區(qū)別與應(yīng)用。初學(xué)者往往對此比較容易混淆，不知道何時選用AFTER觸發(fā)器、何時選用INSTEAD OF觸發(fā)器。為此，本文對SQL Server數(shù)據(jù)庫中DML觸發(fā)器的教學(xué)內(nèi)容進行優(yōu)化探討，以幫助初學(xué)者準確快速地掌握DML觸發(fā)器的精髓。

1 觸發(fā)器概述

　　觸發(fā)器是一種特殊的存儲過程，它是針對表或視圖定義的數(shù)據(jù)庫對象，它不能被顯式地調(diào)用，而是當對定義了觸發(fā)器的表或視圖進行Create、Alter或Drop操作時，或者對定義了觸發(fā)器的表或視圖進行Insert、Update或Delete操作時，觸發(fā)器才被自動執(zhí)行。在觸發(fā)器中主要是定義通過主鍵、外鍵、默認值或CHECK約束等無法實現(xiàn)的復(fù)雜的參照完整性和數(shù)據(jù)完整性的業(yè)務(wù)邏輯。當表或視圖被刪除時，其上定義的觸發(fā)器也一同被刪除[1]。使用觸發(fā)器主要有以下幾點優(yōu)點：

　?。?）級聯(lián)修改數(shù)據(jù)庫中的所有相關(guān)表。

　　（2）撤銷或回滾違反引用完整性的操作，防止非法數(shù)據(jù)修改。

　?。?）強制執(zhí)行比外鍵參照完整性、CHECK約束更為復(fù)雜的業(yè)務(wù)邏輯。

　?。?）查找在數(shù)據(jù)修改前后表狀態(tài)之間的差別，并根據(jù)差別分別采取相應(yīng)的措施。

　?。?）觸發(fā)器是自動執(zhí)行的，不需要管理員手動維護數(shù)據(jù)庫的數(shù)據(jù)完整性[2]。

　　需要注意以下幾點：

　　（1）只有表的所有者才可以在表上創(chuàng)建或刪除觸發(fā)器，且這種權(quán)限不能轉(zhuǎn)授。

　　（2）可以在觸發(fā)器中引用臨時表，但不能在臨時表上創(chuàng)建觸發(fā)器。

　?。?）在執(zhí)行修改語句的過程中，觸發(fā)器的執(zhí)行是執(zhí)行修改語句的一部分，所以如果觸發(fā)器執(zhí)行不成功則整個事務(wù)回滾[3]。

　　在SQL Server2008中，觸發(fā)器主要有DML（Data Manipulation Language）觸發(fā)器、DDL（Data Definition Language）觸發(fā)器和登錄觸發(fā)器。其中DML觸發(fā)器又可以分為6種類型：AFTER-INSERT觸發(fā)器、AFTER-UPDATE觸發(fā)器、AFTER-DELETE觸發(fā)器與INSTEAD OF-INSERT觸發(fā)器、INSTEAD OF-UPDATE觸發(fā)器、INSTEAD OF-DELETE觸發(fā)器[1]。

2 觸發(fā)器工作原理

　　觸發(fā)器被觸發(fā)時，系統(tǒng)將在內(nèi)存中自動創(chuàng)建兩個特殊的臨時表，分別是INSERTED表和DELETED表。INSERTED表用于存儲INSERT和UPDATE語句所影響的記錄行的副本。DELETED表用于存儲DELETE和UPDATE語句所影響的記錄行的副本。INSERTED表和DELETED表只是存儲于內(nèi)存的邏輯表，而不是存儲在數(shù)據(jù)庫中的物理表，但其結(jié)構(gòu)與觸發(fā)器所關(guān)聯(lián)的表結(jié)構(gòu)一致。

　　這兩個表由系統(tǒng)進行創(chuàng)建和管理，用戶不允許直接讀取和修改其內(nèi)容，但可以在觸發(fā)器中訪問它們的數(shù)據(jù)。當觸發(fā)器執(zhí)行完畢后，這兩個表由系統(tǒng)自動刪除[4]。

　　當通過插入（INSERT）語句引發(fā)觸發(fā)器時，新的記錄的副本會添加到臨時表INSERTED表中。當通過刪除（DELETE）語句引發(fā)觸發(fā)器時，被刪除的記錄會添加到臨時表DELETED表中。當通過更新（UPDATE）語句引發(fā)觸發(fā)器時，首先刪除原有的舊記錄，并將該被刪除的記錄添加到臨時表DELETED表中，然后添加更新后的新記錄，并將更新后的新記錄添加到臨時表INSERTED表中。在觸發(fā)器內(nèi)部可以引用INSERTED表和DELETED表中相關(guān)數(shù)據(jù)實現(xiàn)數(shù)據(jù)的操縱。

　　創(chuàng)建觸發(fā)器的基本語法結(jié)構(gòu)如下：

　　CREATE TRIGGER [<所有者名稱>.]<觸發(fā)器名> /*指明

　　觸發(fā)器的名稱*/

　　ON { <表名> | <視圖名> } /*指定觸發(fā)器依賴的基表或視

　　圖*/

　　[ WITH ENCRYPTION ] /*指定對觸發(fā)器的源碼進行加密*/

　　{

　　{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [,][DELETE]}

　　AS

　　[BEGIN]

　　<T-SQL語句>[ ...n ] /*指定觸發(fā)器執(zhí)行的SQL語句，

　　是觸發(fā)器的核心*/

　　[END]

　　}

　　}

　　說明：上面基本語法結(jié)構(gòu)中，INSERT、UPDATE、DELETE選項用來指定觸發(fā)器的事件類型，三個選項至少要指定一個，允許任意次序組合這三個選項。FOR與AFTER關(guān)鍵字含義完全相同，與INSTEAD OF一起組成指定觸發(fā)的方式，AFTER為后觸發(fā)，INSTEAD OF為替代觸發(fā)。

3 AFTER觸發(fā)器與INSTEAD OF觸發(fā)器的區(qū)別

　　AFTER觸發(fā)器也稱后觸發(fā)，是只有執(zhí)行了某一個操作（如INSERT、UPDATE、DELETE等）之后，觸發(fā)器才被觸發(fā)。也即只有引發(fā)觸發(fā)器的操作語句已經(jīng)完成，并通過各類約束驗證后才會去執(zhí)行觸發(fā)器的語句；如果引發(fā)觸發(fā)器的操作語句有錯誤或違反了約束而導(dǎo)致執(zhí)行失敗，觸發(fā)器是不會執(zhí)行的。

　　INSTEAD OF觸發(fā)器也稱替代觸發(fā)，該類型觸發(fā)器并不會執(zhí)行引發(fā)觸發(fā)器的操作語句（如INSERT、UPDATE、DELETE等T-SQL語句），而只是去執(zhí)行觸發(fā)器里面的T-SQL語句。即由觸發(fā)器里面的T-SQL語句替代引發(fā)觸發(fā)器的T-SQL語句的執(zhí)行。

　　假如在一個StudentManager數(shù)據(jù)庫中有tbStudent表及tbDepartment表，兩個表的結(jié)構(gòu)如表1、表2所示（由于只是為了說明問題，在此對表結(jié)構(gòu)做了簡化處理）。

　　現(xiàn)假定tbStudent表中有如下3條記錄：1001，張三，女，1；1002，李四，男，2；1003，王五，男，3。tbDepartment表中有如下4條記錄：1，計算機系，小張；2，藝術(shù)系，小李；3，服裝工程系，小朱；4，物理系，老譚。

　　例：假如要從tbDepartment表中刪除某系部信息，如果該系部下存在學(xué)生信息，則不允許刪除，要求利用觸發(fā)器來實現(xiàn)。

　　現(xiàn)編寫替代觸發(fā)器delete_DepInfo_instead，代碼如下：

　　USE StudentManager

　　GO

　　CREATE TRIGGER delete_DepInfo_instead ON tbDepartment

　　INSTEAD OF DELETE -- 替代觸發(fā)

　　AS

　　-- 從表deleted中獲取刪除記錄的部門編號

　　DECLARE @depid varchar(20)

　　DECLARE @stuname varchar(20)

　　SELECT @depid = DepID FROM deleted

　　-- 判斷要刪除的部門編號是否存在學(xué)生信息

　　SELECT @stuname= StuName FROM tbStudent WHERE DepID=@depid

　　IF @stuname IS NOT NULL

　　PRINT '指定系部存在學(xué)生，請先刪除或修改學(xué)生信息！' -- 提示錯誤信息

　　ELSE

　　DELETE FROM tbDepartment WHERE DepId=@depid

　　GO

　　編寫引發(fā)該觸發(fā)器T-SQL語句代碼如下：

　　USE StudentManager

　　GO

　　DELETE FROM tbDepartment WHERE DepId=1

　　GO

　　結(jié)果分析：由于tbStudent表中存在系部編號為1的學(xué)生信息，所以結(jié)果為彈出“指定系部存在學(xué)生，請先刪除或修改學(xué)生信息！”。但是把引發(fā)觸發(fā)器的SQL語句修改為“DELETE FROM Department WHERE DepId=4”，由于在學(xué)生表tbStudent中不存在系部編號為4的學(xué)生信息，所以會執(zhí)行觸發(fā)器本身所含的“DELETE FROM tbDepartment WHERE DepId=@depid”SQL語句刪除系部編號為4的系部信息，而不是通過引發(fā)觸發(fā)器的SQL語句“DELETE FROM tbDepartment WHERE DepId=4”刪除系部編號為4的系部信息。換句話說假如上面觸發(fā)器delete_DepInfo_instead中T-SQL語句中沒有“ELSE DELETE FROM tbDepartment WHERE DepId=@depid”語句，即使引發(fā)該觸發(fā)器T-SQL語句要刪除系部編號為4的記錄（DELETE FROM tbDepartment WHERE DepId=4），也不能完成刪除操作。

　　但是如果把上面觸發(fā)器（delete_DepInfo_instead）修改為后觸發(fā)的觸發(fā)器（delete_DepInfo_after）：

　　USE StudentManager

　　GO

　　CREATE TRIGGER delete_DepInfo_after ON tbDepartment

　　after DELETE -- 后觸發(fā)

　　AS

　　-- 從表deleted中獲取刪除記錄的部門編號

　　DECLARE @depid varchar(20)

　　DECLARE @stuname varchar(20)

　　SELECT @depid = DepID FROM deleted

　　-- 判斷要刪除的部門編號是否存在學(xué)生信息

　　SELECT @stuname= StuName FROM tbStudent WHERE DepID=@depid

　　IF @stuname IS NOT NULL

　　PRINT '指定系部存在學(xué)生，請先刪除或修改學(xué)生信息！' -- 提示錯誤信息

　　ELSE

　　DELETE FROM tbDepartment WHERE DepId=@depid

　　GO

　　編寫引發(fā)該觸發(fā)器T-SQL語句代碼如下：

　　USE StudentManager

　　GO

　　DELETE FROM tbDepartment WHERE DepId=4

　　GO

　　結(jié)果是不論觸發(fā)器T-SQL語句中有沒有“ELSE DELETE FROM tbDepartment WHERE DepId=@depid”語句，系部編號為4的記錄都將刪除，因為此時會先執(zhí)行引發(fā)觸發(fā)器的T-SQL語句（DELETE FROM tbDepartment WHERE DepId=4），從而把系部編號為4的記錄刪除。

　　此外，AFTER觸發(fā)器只能定義在表上，INSTEAD OF觸發(fā)器可以定義在表上，也可以定義在視圖上。一個表上可以定義多個AFTER觸發(fā)器，但是只能在一個表或視圖上定義一個INSTEAD OF觸發(fā)器。

4 觸發(fā)器應(yīng)用注意事項

　　觸發(fā)器功能強大，可輕松地實現(xiàn)許多復(fù)雜的功能。觸發(fā)器主要用來實現(xiàn)比較復(fù)雜的數(shù)據(jù)完整性、一致性。例如監(jiān)督某一列數(shù)據(jù)的變化范圍，并在超出規(guī)定范圍以后，對兩個以上的表進行修改。但當使用約束、規(guī)則、默認值就可以實現(xiàn)數(shù)據(jù)完整性時，應(yīng)優(yōu)先使用前三種措施，因為濫用觸發(fā)器會造成數(shù)據(jù)庫及應(yīng)用程序維護困難。對表執(zhí)行修改操作時，約束優(yōu)先于觸發(fā)器。如果約束和觸發(fā)器發(fā)生沖突，觸發(fā)器將被屏蔽，不再執(zhí)行。一般來說，只要不影響數(shù)據(jù)的修改，AFTER觸發(fā)器比INSTEAD OF觸發(fā)器效率更高，因此，AFTER觸發(fā)器和INSTEAD OF觸發(fā)器都能實現(xiàn)某功能需求時，優(yōu)先選用AFTER觸發(fā)器。

5 結(jié)束語

　　DML觸發(fā)器是用得最廣泛的觸發(fā)器，在SQL SERVER教學(xué)中占有重要的地位。教學(xué)中的難點往往是ALTER觸發(fā)器與INSTEAD OF觸發(fā)器的區(qū)別與應(yīng)用。本文通過通俗的語言對觸發(fā)器進行了概述，闡述了觸發(fā)器被觸發(fā)時兩個臨時邏輯表INSERTED表和DEKETED表的作用，結(jié)合實例分析了AFTER觸發(fā)器與INSTEAD OF觸發(fā)器的主要區(qū)別與應(yīng)用。AFTER觸發(fā)器為后觸發(fā)器，也即AFTER觸發(fā)器會先執(zhí)行引發(fā)觸發(fā)器中的T-SQL語句，后再執(zhí)行觸發(fā)器本身的T-SQL語句，而INSTEAD OF觸發(fā)器為替代觸發(fā)器，即通過執(zhí)行觸發(fā)器中的T-SQL語句來替代執(zhí)行引發(fā)觸發(fā)器的T-SQL語句，也即INSTEAD OF觸發(fā)器中引發(fā)觸發(fā)器的T-SQL語句不會執(zhí)行。

　　參考文獻

　　[1] 高曉黎,韓曉霞. SQL Server2008案例教程[M]. 北京:清華大學(xué)出版社, 2010.

　　[2] 邱李華,李曉黎,任華,等. SQL Server 2008數(shù)據(jù)庫應(yīng)用教程(第2版)[M]. 北京:人民郵電出版社, 2012.

　　[3] 仝春靈,沈祥玖. 數(shù)據(jù)庫原理與應(yīng)用[M]. 北京:中國水利水電出版社, 2006.

　　[4] 程志梅,邱霞明,王曉燕. SQL Server2000數(shù)據(jù)庫中觸發(fā)器的妙用[J]. 計算機應(yīng)用與軟件, 2009(3):188-189.