SQLServer觸發(fā)器應(yīng)用探析

李虎軍

摘要：該文介紹了觸發(fā)器類型、特點(diǎn)以及觸發(fā)器產(chǎn)生的兩個(gè)臨時(shí)表，并著重對(duì)觸發(fā)器提高數(shù)據(jù)完整性的應(yīng)用及其注意事項(xiàng)進(jìn)行了闡述，以期對(duì)數(shù)據(jù)庫(kù)設(shè)計(jì)者在設(shè)計(jì)數(shù)據(jù)庫(kù)系統(tǒng)時(shí)提高數(shù)據(jù)完整性控制提供有益幫助。

關(guān)鍵詞：完整性；觸發(fā)器；CHECK約束

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2016）16-0013-02

信息系統(tǒng)開(kāi)發(fā)與運(yùn)行離不開(kāi)數(shù)據(jù)庫(kù)系統(tǒng)的支持，而對(duì)于數(shù)據(jù)庫(kù)而言，其存儲(chǔ)的數(shù)據(jù)的正確性至關(guān)重要。為確保數(shù)據(jù)的正確性，各種DBMS都提供了各種方法或機(jī)制進(jìn)行檢測(cè)、檢查。靈活、合理地應(yīng)用觸發(fā)器對(duì)提高信息系統(tǒng)的數(shù)據(jù)正確性有很好的幫助。

1 SQL Server觸發(fā)器

SQL Server觸發(fā)器是一類特殊存儲(chǔ)過(guò)程，其由用戶定義在數(shù)據(jù)表上并被事件驅(qū)動(dòng)的由DBMS調(diào)用執(zhí)行的。觸發(fā)器不能被用戶直接調(diào)用執(zhí)行，也不能接收參數(shù)。在數(shù)據(jù)表上或在數(shù)據(jù)庫(kù)對(duì)象上定義了相應(yīng)的觸發(fā)器后，當(dāng)在數(shù)據(jù)表中DELETE、INSERT、UPDATE操作或在CREATE、DROP、ALTER數(shù)據(jù)庫(kù)對(duì)象時(shí)將激發(fā)并自動(dòng)執(zhí)行相應(yīng)的觸發(fā)器，使得相應(yīng)的操作必須滿足由觸發(fā)器所定義的約束規(guī)則才能得到成功執(zhí)行。引起觸發(fā)器被執(zhí)行的相應(yīng)操作和觸發(fā)器被統(tǒng)一當(dāng)作一次事務(wù)處理。當(dāng)事務(wù)未能整體成功執(zhí)行，則DBMS會(huì)自動(dòng)回到該事務(wù)執(zhí)行前的狀態(tài)。觸發(fā)器可用于各種數(shù)據(jù)規(guī)則、約束和默認(rèn)值的完整性檢查，也可完成更復(fù)雜的數(shù)據(jù)檢測(cè)和業(yè)務(wù)邏輯規(guī)則。

1.1 觸發(fā)器分類

按照觸發(fā)事件的不同，SQL Server觸發(fā)器可以分為DDL（Data Definition Language）觸發(fā)器和DML（Data Manipulation Language）觸發(fā)器兩種。

1）DDL觸發(fā)器用于處理相應(yīng)的DDL事件。DDL事件有數(shù)據(jù)庫(kù)系統(tǒng)用戶登錄與退出和CREATE、DROP、ALTER語(yǔ)句等操作。DDL觸發(fā)器可用于審核與規(guī)范數(shù)據(jù)庫(kù)中對(duì)表結(jié)構(gòu)、視圖結(jié)構(gòu)上的操作，還可被用于應(yīng)用系統(tǒng)管理任務(wù)。當(dāng)數(shù)據(jù)庫(kù)結(jié)構(gòu)或表結(jié)構(gòu)發(fā)生變化時(shí)激發(fā)觸發(fā)器，觸發(fā)器能記錄相應(yīng)的修改過(guò)程，并能禁止用戶刪除指定表、限制用戶對(duì)數(shù)據(jù)庫(kù)修改等操作。

2）DML觸發(fā)器用于響應(yīng)用戶DML操作事件。DML操作事件是指針對(duì)表或視圖的INSERT、DELETE和UPDATE操作語(yǔ)句。該類觸發(fā)器在DML編輯數(shù)據(jù)時(shí)觸發(fā)執(zhí)行，在處理表或視圖中數(shù)據(jù)時(shí)DML觸發(fā)器檢查操作是否滿足相應(yīng)業(yè)務(wù)規(guī)則，使得數(shù)據(jù)完整性進(jìn)一步擴(kuò)展。

根據(jù)觸發(fā)器是在觸發(fā)操作語(yǔ)句前還是后執(zhí)行的不同，觸發(fā)器又分為INSTEAD OF觸發(fā)器和AFTER（FOR）觸發(fā)器兩種。

1）INSTEAD OF觸發(fā)器。顧名思義，該種觸發(fā)器在數(shù)據(jù)操作之前執(zhí)行，代替操作數(shù)據(jù)的相關(guān)語(yǔ)句，實(shí)際上是執(zhí)行觸發(fā)器中的代碼。該類觸發(fā)器定義在視圖或表上。在視圖或表上，每個(gè)DELETE、UPDATE和INSERT語(yǔ)句只可定義一個(gè)INSTEAD OF觸發(fā)器。

2）AFTER（FOR）觸發(fā)器。在DML語(yǔ)句操作數(shù)據(jù)完成以后被觸發(fā)執(zhí)行。此種觸發(fā)器可對(duì)被操作的數(shù)據(jù)進(jìn)行業(yè)務(wù)規(guī)則檢查，如果違反相應(yīng)的業(yè)務(wù)規(guī)則，則返回該數(shù)據(jù)操作前的狀態(tài)。只能在表上而不能在視圖上定義AFTER（FOR）觸發(fā)器，在同一張數(shù)據(jù)表上可定義多個(gè)AFTER（FOR）觸發(fā)器。

1.2 DBMS生成的兩個(gè)臨時(shí)表

在執(zhí)行DML觸發(fā)器時(shí)，SQL SERVER生成兩個(gè)只讀型臨時(shí)表：deleted表和inserted表。在觸發(fā)器執(zhí)行時(shí)這兩個(gè)表才存在于內(nèi)存，其表結(jié)構(gòu)與激發(fā)DML觸發(fā)器的視圖或基表類似。

1）deleted表

deleted表僅存放進(jìn)行本次UPDATE和DELETE語(yǔ)句操作的基表的舊數(shù)據(jù)行。執(zhí)行UPDATE或DELETE語(yǔ)句時(shí)，該數(shù)據(jù)行從基表中移動(dòng)到deleted表并從基表中刪除。

2）inserted表

inserted表僅存放進(jìn)行本次UPDATE和INSERT語(yǔ)句操作的新數(shù)據(jù)行。執(zhí)行UPDATE語(yǔ)句或INSERT語(yǔ)句時(shí)，新數(shù)據(jù)行被插入基表的同時(shí)也被復(fù)制到inserted表中。

實(shí)際上，UPDATE操作可看做由DELETE操作和INSERT操作組合而成的。UPDATE操作時(shí)，DBMS將要修改的數(shù)據(jù)行從基表中刪除并移到deleted表中，然后將修改后的新數(shù)據(jù)行同時(shí)插入到inserted表和基表中。

2 SQL Server觸發(fā)器應(yīng)用

觸發(fā)器機(jī)制類似Windows系統(tǒng)的消息響應(yīng)機(jī)制，我們可以靈活利用這一點(diǎn)，不僅可以在數(shù)據(jù)表上實(shí)施比CHECK約束、外鍵約束等更為復(fù)雜的檢查和操作，可以引用數(shù)據(jù)庫(kù)中其他表中的字段，具有更精細(xì)和更強(qiáng)大的數(shù)據(jù)控制能力，還可以利用DDL觸發(fā)器對(duì)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)實(shí)施各種應(yīng)用控制，提高數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的安全性。

2.1實(shí)現(xiàn)CHECK約束檢查功能

在DML觸發(fā)器中可以靈活、編寫(xiě)代碼處理相應(yīng)的業(yè)務(wù)邏輯，因此DML觸發(fā)器可以設(shè)置比CHECK約束更復(fù)雜、更強(qiáng)大的相應(yīng)數(shù)據(jù)檢查規(guī)則。例如醫(yī)院所用某種藥品已經(jīng)停止采購(gòu)，但此藥品還需在藥品數(shù)據(jù)表中存在，此時(shí)需要阻止門(mén)診醫(yī)生開(kāi)列此藥，數(shù)據(jù)庫(kù)管理人員就可以在藥品數(shù)據(jù)表上建立相應(yīng)的觸發(fā)器，來(lái)禁止醫(yī)師修改已停止采購(gòu)藥品的數(shù)據(jù)，此任務(wù)無(wú)法使用CHECK約束來(lái)完成。當(dāng)數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)者想控制錯(cuò)誤信息或者給用戶/客戶應(yīng)用程序傳遞錯(cuò)誤信息時(shí)，無(wú)法使用CHECK約束實(shí)現(xiàn)，而觸發(fā)器就能很好地完成任務(wù)。當(dāng)需要發(fā)出更詳細(xì)的錯(cuò)誤信息以提示用戶時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)者可以在觸發(fā)器中使用RAISERROR（）函數(shù)來(lái)達(dá)到目的。當(dāng)在業(yè)務(wù)邏輯需要使用另一張表的數(shù)據(jù)時(shí)，也可以使用觸發(fā)器來(lái)實(shí)現(xiàn)。觸發(fā)器與CHECK約束各有優(yōu)勢(shì)。CHECK約束執(zhí)行快且效率高，但只能引用同一表中的數(shù)據(jù)，而觸發(fā)器卻可以引用不同表中的數(shù)據(jù)。觸發(fā)器可以完全實(shí)現(xiàn)CHECK約束的所有功能，但在效率上并不總是比CHECK約束好。觸發(fā)器是維護(hù)引用完整性需要的一種最好選擇，雖然它們的速度有點(diǎn)慢，但在維護(hù)數(shù)據(jù)完整性上更靈活。何時(shí)選擇使用觸發(fā)器與CHECK約束，需要根據(jù)實(shí)際情況定，首選CHECK約束，當(dāng)CHECK約束無(wú)能為力時(shí)就用觸發(fā)器。

2.2 數(shù)據(jù)庫(kù)設(shè)計(jì)與批量數(shù)據(jù)導(dǎo)入

數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，要在表上設(shè)計(jì)各種約束以及要設(shè)計(jì)表與表之間的引用關(guān)系等等。在數(shù)據(jù)庫(kù)系統(tǒng)調(diào)試中難免會(huì)對(duì)表結(jié)構(gòu)進(jìn)行改變完善，此時(shí)在原表上所建立的各種完整性約束都需要?jiǎng)h除，在更改表結(jié)構(gòu)建立表后，再重新建立各種約束，上述工作較難保證沒(méi)有差錯(cuò)或遺漏，這就給數(shù)據(jù)庫(kù)設(shè)計(jì)者帶來(lái)較大不便。可以使用觸發(fā)器來(lái)建立相關(guān)約束規(guī)則，在表結(jié)構(gòu)發(fā)生更改時(shí)只需禁用觸發(fā)器即可，隨后也可方便地恢復(fù)觸發(fā)器的使用，方便數(shù)據(jù)庫(kù)設(shè)計(jì)和調(diào)試。在數(shù)據(jù)庫(kù)系統(tǒng)開(kāi)發(fā)完畢后再將某些觸發(fā)器改為DRI（聲明引用完整性）。當(dāng)需要大批量導(dǎo)入數(shù)據(jù)時(shí)，往往需要關(guān)閉各種約束來(lái)方便導(dǎo)入數(shù)據(jù)；當(dāng)使用觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)約束時(shí)，就能體現(xiàn)出觸發(fā)器的優(yōu)點(diǎn)。只需關(guān)閉觸發(fā)器就可減少導(dǎo)入時(shí)的大量開(kāi)銷(xiāo)以實(shí)現(xiàn)批量數(shù)據(jù)的快速導(dǎo)入。

2.3 UPDATE（）函數(shù)和UPDATE_COLUMNS（）函數(shù)

在UPDATE觸發(fā)器中，經(jīng)常通過(guò)查看感興趣的列是否通過(guò)發(fā)生改變來(lái)限制觸發(fā)器中實(shí)際執(zhí)行的代碼數(shù)量，要完成這個(gè)任務(wù)，可以利用UPDATE（）函數(shù)和UPDATE_COLUMNS（）函數(shù)。

1）UPDATE（）函數(shù)

該函數(shù)用于測(cè)試在指定列上進(jìn)行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。只適用于觸發(fā)器范圍，其核心目的是對(duì)指定列進(jìn)行是否被更新檢測(cè)。

UPDATE（FIELD_NAME）值為true，則意味著FIELD_NAME列被更新。

2.4 加強(qiáng)數(shù)據(jù)庫(kù)用戶管理

可以針對(duì) CREATE、ALTER 和 DROP 開(kāi)頭的語(yǔ)句，建立DDL觸發(fā)器。為防止對(duì)數(shù)據(jù)庫(kù)架構(gòu)進(jìn)行某些更改時(shí)，當(dāng)希望數(shù)據(jù)庫(kù)中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫(kù)架構(gòu)中的更改時(shí)，當(dāng)需要記錄數(shù)據(jù)庫(kù)架構(gòu)中的更改或事件時(shí)，使用DDL觸發(fā)器進(jìn)行管理。可以對(duì)密碼表中指定列設(shè)定AFTER（FOR）DML觸發(fā)器，當(dāng)表中用戶名或密碼值發(fā)生變化時(shí)，將操作者、操作時(shí)間、操作對(duì)象、前后變化的數(shù)據(jù)等內(nèi)容進(jìn)行保存并向數(shù)據(jù)庫(kù)管理員發(fā)通知，為及時(shí)發(fā)現(xiàn)不合法操作、加強(qiáng)系統(tǒng)審核、保障數(shù)據(jù)庫(kù)的安全提供幫助。

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

3.1 觸發(fā)器執(zhí)行是被動(dòng)的而不是主動(dòng)的

這里主要指AFTER觸發(fā)器發(fā)生在事件發(fā)生之后，在觸發(fā)器被激活時(shí)，整個(gè)觸發(fā)語(yǔ)句已經(jīng)執(zhí)行而且事務(wù)已經(jīng)記錄在日志中了（但沒(méi)有提交）。這意味著，如果觸發(fā)器需要rollback，就不得不撤銷(xiāo)已經(jīng)完成的大量工作，因此促發(fā)其對(duì)系統(tǒng)性能的影響程度取決于觸發(fā)語(yǔ)句工作量的大小。而約束是主動(dòng)的，約束是在實(shí)際語(yǔ)句發(fā)生之前執(zhí)行的，這意味著它們?cè)谒兄饕ぷ魍瓿汕白柚故〉氖虑榘l(fā)生。也就意味著約束的運(yùn)行速度要快一些。特別是觸發(fā)語(yǔ)句越復(fù)雜、影響數(shù)據(jù)量越大，使用觸發(fā)器所帶來(lái)的效率影響也越大。

3.2 盡量保持觸發(fā)器簡(jiǎn)潔

觸發(fā)器是與所調(diào)用的語(yǔ)句所在事務(wù)的一部分。這意味著語(yǔ)句要到觸發(fā)器結(jié)束時(shí)才完成。如果在觸發(fā)器的語(yǔ)句體中書(shū)寫(xiě)了運(yùn)行很長(zhǎng)時(shí)間的代碼，就意味著引起觸發(fā)器觸發(fā)的每段代碼，運(yùn)行時(shí)間都很長(zhǎng)。因此這很可能使得應(yīng)用程序執(zhí)行效果較差。

3.3 盡量不要在觸發(fā)器中回滾

做到這一點(diǎn)較為困難，因?yàn)榛貪L是在觸發(fā)器中通常希望完成的主要任務(wù)。最典型的觸發(fā)器——AFTER觸發(fā)器——發(fā)生在大部分操作已經(jīng)完成以后，這也意味著回滾開(kāi)銷(xiāo)很大，這也是為什么建議盡可能使用DRI的原因。如果在觸發(fā)器中使用多條ROLLBACK TRAN語(yǔ)句，最好在執(zhí)行激活觸發(fā)器的語(yǔ)句之前預(yù)先找到錯(cuò)誤。在錯(cuò)誤發(fā)生前進(jìn)行測(cè)試，不要等到回滾的時(shí)候。

4 結(jié)束語(yǔ)

靈活使用觸發(fā)器可以幫助數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)者和維護(hù)者實(shí)現(xiàn)許多復(fù)雜的功能，能較好地提高信息系統(tǒng)的數(shù)據(jù)完整性。但要慎用觸發(fā)器，如果濫用會(huì)造成數(shù)據(jù)庫(kù)系統(tǒng)的維護(hù)困難。因此在數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)中，盡量使用DRI來(lái)保證數(shù)據(jù)完整性。合理的觸發(fā)器設(shè)計(jì)既可以保護(hù)表中的數(shù)據(jù)，使數(shù)據(jù)表更加安全，又可以減少數(shù)據(jù)庫(kù)設(shè)計(jì)者的勞動(dòng)強(qiáng)度，具有事半功倍的作用。

參考文獻(xiàn)：

[1] 張峰. 觸發(fā)器在數(shù)據(jù)處理過(guò)程中的應(yīng)用研究[J].計(jì)算機(jī)工程與科學(xué)，2008（5）.

[2] 李霞. SQL Server約束在維護(hù)數(shù)據(jù)完整性中的運(yùn)用[J].晉城職業(yè)技術(shù)學(xué)院學(xué)報(bào)，2012（5）.