SQL Server觸發(fā)器在教務(wù)管理系統(tǒng)中的應(yīng)用研究

摘 要：觸發(fā)器是保證數(shù)據(jù)完整性的重要機制之一，本文首先介紹了觸發(fā)器的分類、功能實現(xiàn)原理，以及在SQL Server中的常見使用方法，并結(jié)合教務(wù)管理系統(tǒng)中的實例給出了DML觸發(fā)器的創(chuàng)建方法。結(jié)果表明，觸發(fā)器的合理應(yīng)用可以較好地提高系統(tǒng)性能，維護數(shù)據(jù)庫的完整性。

關(guān)鍵詞：觸發(fā)器；SQL Server；數(shù)據(jù)完整性；教務(wù)管理系統(tǒng)

中圖分類號：TP311 文獻標(biāo)識碼：A

1 引言（Introduction）

在SQL Server數(shù)據(jù)庫中，有主鍵、檢查、唯一、默認、外鍵等約束能保證數(shù)據(jù)的完整性，但它們也有一定的局限性，大多只能在某張數(shù)據(jù)表中起作用，不能跨越其他數(shù)據(jù)表發(fā)揮作用，而觸發(fā)器正好就可以解決這樣的問題。觸發(fā)器是SQL Server數(shù)據(jù)庫中一類重要的數(shù)據(jù)庫對象，它與存儲過程不同，不需要進行顯式的調(diào)用，它因事件觸發(fā)而自動執(zhí)行無須人工干預(yù)，它可以完成比較復(fù)雜的完整性操作，如數(shù)據(jù)表的級聯(lián)更新、刪除等，從而確保數(shù)據(jù)的完整性。

2 觸發(fā)器的簡介（Introduction to the trigger）

2.1 觸發(fā)器的分類

SQL Server的觸發(fā)器通常分為兩類，分別是DML觸發(fā)器和DDL觸發(fā)器。DML觸發(fā)器主要當(dāng)數(shù)據(jù)庫中的數(shù)據(jù)發(fā)生增加、更新和刪除時被觸發(fā)，也即當(dāng)數(shù)據(jù)庫執(zhí)行INSERT、UPDATE和DELETE語句時被觸發(fā)。DDL觸發(fā)器主要用于審核與規(guī)范數(shù)據(jù)庫中的數(shù)據(jù)表、視圖的數(shù)據(jù)結(jié)構(gòu)，如當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生新增數(shù)據(jù)表或修改某列數(shù)據(jù)類型時被觸發(fā)。而根據(jù)觸發(fā)器的觸發(fā)時機不同，觸發(fā)器還可以分為AFTER觸發(fā)器和INSTEAD OF觸發(fā)器。AFTER觸發(fā)器又稱為后觸發(fā)器，它是在引起觸發(fā)器執(zhí)行的語句成功完成后執(zhí)行的。而INSTEAD OF觸發(fā)器又稱為替代觸發(fā)器，它代替引起觸發(fā)器執(zhí)行的語句而執(zhí)行。一個表或視圖的每個修改動作（INSERT、UPDATE和DELETE）都可以有一個INSTEAD OF觸發(fā)器，但可以有多個AFTER觸發(fā)器[1]。

2.2 觸發(fā)器的功能

觸發(fā)器的功能主要有：（1）跟蹤數(shù)據(jù)的變化，當(dāng)數(shù)據(jù)庫中的數(shù)據(jù)發(fā)生變化時，禁止一些非法操作，從而保證數(shù)據(jù)的安全。（2）增強數(shù)據(jù)的參照完整性，可以監(jiān)測到數(shù)據(jù)表中數(shù)據(jù)的變化，并自動地級聯(lián)更新整個數(shù)據(jù)庫中的各項內(nèi)容，從而保證數(shù)據(jù)的一致性[2]。（3）當(dāng)數(shù)據(jù)庫出現(xiàn)安全問題時用于審計，如可以記錄發(fā)生插入、修改和刪除操作時的操作類型、操作時間及相關(guān)的用戶等信息。（4）實現(xiàn)數(shù)據(jù)庫定義本身所不能實現(xiàn)的較為復(fù)雜的商業(yè)規(guī)則，如對更新數(shù)據(jù)操作時間的限制、更新數(shù)據(jù)幅度的限制等。

3 觸發(fā)器的工作原理（The working principle of the

trigger）

觸發(fā)器是一種依賴于數(shù)據(jù)表而存在的數(shù)據(jù)庫對象，當(dāng)它被激活的時候，系統(tǒng)會自動的創(chuàng)建兩張臨時表：分別是INSERTED表和DELETED表。這兩張表是邏輯表，并且這兩張表是由系統(tǒng)管理的，存儲在內(nèi)存中，不存儲在數(shù)據(jù)庫中，因此不允許用戶直接對其修改[3]。這兩張表的結(jié)構(gòu)和觸發(fā)器所在的表結(jié)構(gòu)完全相同，在觸發(fā)器的代碼被執(zhí)行后由系統(tǒng)自動撤銷。

系統(tǒng)將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個事務(wù)對待，如果檢測到嚴重錯誤，則整個事務(wù)即自動回滾，恢復(fù)到原來的狀態(tài)[4]。

在執(zhí)行數(shù)據(jù)增加的操作時，相關(guān)聯(lián)的INSERT觸發(fā)器會被激活，系統(tǒng)會自動建立一張INSERTED表，要插入的數(shù)據(jù)會被臨時存放在該表中；當(dāng)執(zhí)行數(shù)據(jù)刪除操作時，相關(guān)聯(lián)的DELETE觸發(fā)器會被激活，系統(tǒng)則會自動生成一張DELETED表，刪除的數(shù)據(jù)會臨時存放在該表中；而當(dāng)執(zhí)行數(shù)據(jù)更新操作時，相當(dāng)于先在數(shù)據(jù)表中刪除了需要更新的數(shù)據(jù)，后又插入了需要替代的新的數(shù)據(jù)，因此系統(tǒng)會自動生成DELETED表和INSERTED表，需更新的原始數(shù)據(jù)被存入到DELETED表中，而替代的新的數(shù)據(jù)則被存入到了INSERTED表中。具體如表1所示。

4 觸發(fā)器的常見操作（Common operation of the

trigger）

4.1 觸發(fā)器的創(chuàng)建

創(chuàng)建觸發(fā)器不僅需要指定觸發(fā)器的名稱，還需說明與之相關(guān)的數(shù)據(jù)表、觸發(fā)器的觸發(fā)類型、觸發(fā)事件等信息，具體語法格式如下：

CREATE TRIGGER trigger_name

ON table/view

[WITH ENCRYPTION]

FOR /AFTER/INSTEAD OF[INSERT][，UPDATE][，DELETE]

AS

T-SQL語句

其中trigger_name表示觸發(fā)器的名稱，它必須遵循標(biāo)識符規(guī)則，不能以#或##開頭（以下出現(xiàn)不再贅述）。由于觸發(fā)器可以應(yīng)用在數(shù)據(jù)表或視圖上，table/view此處表示對其執(zhí)行的數(shù)據(jù)庫對象的名稱。需要注意的是，視圖只能被INSTEAD OF觸發(fā)器引用。WITH ENCRYPTION是可選項，用來對觸發(fā)器的代碼進行加密。FOR/AFTER指定 DML觸發(fā)器僅在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行時才被觸發(fā)，AFTER可以省略。INSTEAD OF設(shè)置觸發(fā)器激活的時機為前觸發(fā)，需要注意的是不能為DDL觸發(fā)器指定INSTEAD OF。[INSERT][，UPDATE][，DELETE]表示激活觸發(fā)器的DML語句的類型，允許使用上述選項的任意順序組。

4.2 觸發(fā)器的禁用和啟用

觸發(fā)器可以根據(jù)情況進行禁用和啟用。在數(shù)據(jù)維護或初始化過程中，特別是在大批量數(shù)據(jù)庫導(dǎo)入時，需要暫停觸發(fā)器語句體的執(zhí)行，等數(shù)據(jù)維護或初始化過程完成后，繼續(xù)使觸發(fā)器生效[5]。

禁用和啟用觸發(fā)器的語句格式如下：

ALTER TABLE table_name DISABLE/ ENABLE TRIGGER trigger_name/ALL

其中table_name表示禁用或啟用的某個觸發(fā)器所在的數(shù)據(jù)表的名稱，ALL表示禁用數(shù)據(jù)表上的所有觸發(fā)器。此外，還可以調(diào)用系統(tǒng)存儲過程sp_msforeachtable來禁止或啟用所有表上的所有觸發(fā)器，具體格式如下所示：

exec sp_msforeachtable'ALTER TABLE ？ DISABLE/ENABLE TRIGGER ALL'

其中sp_msforeachtable是系統(tǒng)存儲過程，存放在SQL Server的系統(tǒng)數(shù)據(jù)庫master中。"？"的作用相當(dāng)于DOS命令中，以及我們在Windows下搜索文件時的通配符的作用。

4.3 觸發(fā)器的刪除

當(dāng)觸發(fā)器不再需要時，可以使用DROP命令進行刪除，其語法格式如下：

DROP TRIGGER trigger_name

5 觸發(fā)器的應(yīng)用（Application of the trigger）

在一個教務(wù)管理系統(tǒng)中的教學(xué)管理模塊中有學(xué)生表、班級表、選課表和課程表等數(shù)據(jù)表，分別記錄了學(xué)生、班級、選課和課程的具體信息，關(guān)系模式如下所示：

學(xué)生（學(xué)號，姓名，性別，出生日期，電子郵件，地址，班級編號）

班級（班級編號，班級名稱，人數(shù)，專業(yè)編號）

選課（學(xué)號，課程編號，成績，學(xué)年，學(xué)期）

課程（課程編號，課程名稱，學(xué)分，課程性質(zhì)）

5.1 處理新轉(zhuǎn)入的學(xué)生

當(dāng)學(xué)生信息增加到學(xué)生表中后，與之相關(guān)聯(lián)的班級信息也會發(fā)生相應(yīng)的變化，即班級人數(shù)會增加。如果每次都使用手工的方式去更改班級表中的信息，既麻煩也容易出錯，不利于數(shù)據(jù)的一致性。因此可以設(shè)計一個INSERT觸發(fā)器來解決這類問題，當(dāng)系統(tǒng)發(fā)現(xiàn)學(xué)生表中增加記錄時，自動的實現(xiàn)班級表中班級人數(shù)字段的更新。具體代碼如下：

CREATE TRIGGER tri_stuInsert

ON student --創(chuàng)建在學(xué)生表上

FOR INSERT --觸發(fā)事件是增加數(shù)據(jù)

AS

BEGIN

UPDATE class --根據(jù)INSERTED表中的班級編號更新班級表中的人數(shù)

SET num=num+1--每增加一條記錄，班級人數(shù)增加1

WHERE Classno=（SELECT Classno FROM INSERTED）

END

5.2 使用級聯(lián)刪除處理學(xué)生退學(xué)

當(dāng)學(xué)生辦理退學(xué)時，需要在刪除學(xué)生個人信息的同時，將其選修課程的信息刪除。否則留在選課表中的學(xué)生成績就會因找不到學(xué)生的相關(guān)信息，而造成整個數(shù)據(jù)庫中數(shù)據(jù)的異常。這時可以設(shè)計一個DELETE觸發(fā)器來解決這類問題，當(dāng)系統(tǒng)發(fā)現(xiàn)學(xué)生表中刪除的學(xué)生在選課表里有相關(guān)記錄時，自動實現(xiàn)選課表中數(shù)據(jù)的刪除。具體代碼如下：

CREATE TRIGGER tri_stuDelete

ON student --創(chuàng)建在學(xué)生表上

FOR DELETE --觸發(fā)事件是刪除數(shù)據(jù)

AS

BEGIN

DELETE result --根據(jù)DELETED表中的學(xué)號刪除選課表中的信息

WHERE Sno IN （SELECT Sno

FROM DELETED）

END

5.3 使用級聯(lián)更新修改課程編號

如果課程編號發(fā)生變化時，與之相關(guān)聯(lián)的選課表中的課程編號也需要同時更新，不然的話就會破壞數(shù)據(jù)的完整性。因此可以設(shè)計一個UPDATE觸發(fā)器來解決這類問題，當(dāng)系統(tǒng)發(fā)現(xiàn)課程表中的課程編號發(fā)生變化時，自動地對選課表中的課程編號進行更新。具體代碼如下：

CREATE TRIGGER tri_courseUpdate

ON course--創(chuàng)建在選課表上

FOR UPDATE--觸發(fā)事件是更新數(shù)據(jù)

AS

BEGIN

--分別定義局部變量存放原課程編號和新課程編號

DECLARE @oldcno CHAR（7），@newcno CHAR（7）

--從DELETED、INSERTED表中查詢出原課程編號和新課程編號并存放在局部變量中

SELECT@oldcno=deleted.cno，@newcno

=inserted.cno

FROM DELETED，INSERTED

UPDATE Result --對選課表的課程編號進行更新

SET Cno=@newcno

WHERE Cno=@oldcno

END

6 結(jié)論（Conclusion）

通過實踐證明，應(yīng)用觸發(fā)器不僅可以處理表與表之間復(fù)雜的邏輯關(guān)系，優(yōu)化數(shù)據(jù)庫的設(shè)計，更好地維護數(shù)據(jù)庫中數(shù)據(jù)的完整性[6]，同時也提高了教務(wù)管理系統(tǒng)實現(xiàn)的運行效率。但是同時我們也必須清醒地認識到，如果過多的使用觸發(fā)器不僅增加了數(shù)據(jù)庫維護的成本，有時還將降低數(shù)據(jù)庫的整體性能。因此，我們要在合適的時候恰當(dāng)?shù)厥褂糜|發(fā)器。

參考文獻（References）

[1] 吳西燕.SQL觸發(fā)器在旅行社管理信息系統(tǒng)中的應(yīng)用[J].電腦

編程技巧與維護，2012（22）：50-51.

[2] 耿濤，黃磊，劉儒香.SQL Server2005觸發(fā)器在圖書管理系統(tǒng)中

的應(yīng)用研究[J].西昌學(xué)院學(xué)報（自然科學(xué)版），2012，26（3）：69-71.

[3] 吳伶琳，楊正校.SQL Server數(shù)據(jù)庫技術(shù)及應(yīng)用[M].第二版.大

連：大連理工大學(xué)出版社，2014：170-172.

[4] 符策銳.觸發(fā)器保持評教系統(tǒng)數(shù)據(jù)完整性的應(yīng)用研究[J].微計

算機信息，2012，28（8）：173-175.

[5] 朱亞興.Oracle數(shù)據(jù)庫系統(tǒng)應(yīng)用開發(fā)實用教程[M].北京：高等

教育出版社，2012：248-249.

[6] 褚龍現(xiàn).DML觸發(fā)器保持數(shù)據(jù)庫完整性應(yīng)用研究[J].計算機

與現(xiàn)代化，2013（4）：57-59.

作者簡介：

吳伶琳（1977-），女，碩士，副教授，工程師.研究領(lǐng)域：數(shù)據(jù)

庫技術(shù)與應(yīng)用.