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

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

0 引言

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

1 觸發(fā)器概述

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

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

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

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

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

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

　　需要注意以下幾點(diǎn)：

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

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

　?。?）在執(zhí)行修改語句的過程中，觸發(fā)器的執(zhí)行是執(zhí)行修改語句的一部分，所以如果觸發(fā)器執(zhí)行不成功則整個(gè)事務(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ā)時(shí)，系統(tǒng)將在內(nèi)存中自動(dòng)創(chuàng)建兩個(gè)特殊的臨時(shí)表，分別是INSERTED表和DELETED表。INSERTED表用于存儲(chǔ)INSERT和UPDATE語句所影響的記錄行的副本。DELETED表用于存儲(chǔ)DELETE和UPDATE語句所影響的記錄行的副本。INSERTED表和DELETED表只是存儲(chǔ)于內(nèi)存的邏輯表，而不是存儲(chǔ)在數(shù)據(jù)庫中的物理表，但其結(jié)構(gòu)與觸發(fā)器所關(guān)聯(lián)的表結(jié)構(gòu)一致。

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

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

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

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

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

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

　　圖*/

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

　　{

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

　　AS

　　[BEGIN]

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

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

　　[END]

　　}

　　}

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

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

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

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

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

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

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

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

　　USE StudentManager

　　GO

　　CREATE TRIGGER delete_DepInfo_instead ON tbDepartment

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

　　AS

　　-- 從表deleted中獲取刪除記錄的部門編號(hào)

　　DECLARE @depid varchar(20)

　　DECLARE @stuname varchar(20)

　　SELECT @depid = DepID FROM deleted

　　-- 判斷要?jiǎng)h除的部門編號(hào)是否存在學(xué)生信息

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

　　IF @stuname IS NOT NULL

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

　　ELSE

　　DELETE FROM tbDepartment WHERE DepId=@depid

　　GO

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

　　USE StudentManager

　　GO

　　DELETE FROM tbDepartment WHERE DepId=1

　　GO

　　結(jié)果分析：由于tbStudent表中存在系部編號(hào)為1的學(xué)生信息，所以結(jié)果為彈出“指定系部存在學(xué)生，請(qǐng)先刪除或修改學(xué)生信息！”。但是把引發(fā)觸發(fā)器的SQL語句修改為“DELETE FROM Department WHERE DepId=4”，由于在學(xué)生表tbStudent中不存在系部編號(hào)為4的學(xué)生信息，所以會(huì)執(zhí)行觸發(fā)器本身所含的“DELETE FROM tbDepartment WHERE DepId=@depid”SQL語句刪除系部編號(hào)為4的系部信息，而不是通過引發(fā)觸發(fā)器的SQL語句“DELETE FROM tbDepartment WHERE DepId=4”刪除系部編號(hào)為4的系部信息。換句話說假如上面觸發(fā)器delete_DepInfo_instead中T-SQL語句中沒有“ELSE DELETE FROM tbDepartment WHERE DepId=@depid”語句，即使引發(fā)該觸發(fā)器T-SQL語句要?jiǎng)h除系部編號(hào)為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中獲取刪除記錄的部門編號(hào)

　　DECLARE @depid varchar(20)

　　DECLARE @stuname varchar(20)

　　SELECT @depid = DepID FROM deleted

　　-- 判斷要?jiǎng)h除的部門編號(hào)是否存在學(xué)生信息

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

　　IF @stuname IS NOT NULL

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

　　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”語句，系部編號(hào)為4的記錄都將刪除，因?yàn)榇藭r(shí)會(huì)先執(zhí)行引發(fā)觸發(fā)器的T-SQL語句（DELETE FROM tbDepartment WHERE DepId=4），從而把系部編號(hào)為4的記錄刪除。

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

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

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

5 結(jié)束語

　　DML觸發(fā)器是用得最廣泛的觸發(fā)器，在SQL SERVER教學(xué)中占有重要的地位。教學(xué)中的難點(diǎn)往往是ALTER觸發(fā)器與INSTEAD OF觸發(fā)器的區(qū)別與應(yīng)用。本文通過通俗的語言對(duì)觸發(fā)器進(jìn)行了概述，闡述了觸發(fā)器被觸發(fā)時(shí)兩個(gè)臨時(shí)邏輯表INSERTED表和DEKETED表的作用，結(jié)合實(shí)例分析了AFTER觸發(fā)器與INSTEAD OF觸發(fā)器的主要區(qū)別與應(yīng)用。AFTER觸發(fā)器為后觸發(fā)器，也即AFTER觸發(fā)器會(huì)先執(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語句不會(huì)執(zhí)行。

　　參考文獻(xiàn)

　　[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]. 計(jì)算機(jī)應(yīng)用與軟件, 2009(3):188-189.