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

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

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

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A

1 引言（Introduction）

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

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

2.1 觸發(fā)器的分類

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

2.2 觸發(fā)器的功能

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

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

trigger）

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

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

在執(zhí)行數(shù)據(jù)增加的操作時(shí)，相關(guān)聯(lián)的INSERT觸發(fā)器會(huì)被激活，系統(tǒng)會(huì)自動(dòng)建立一張INSERTED表，要插入的數(shù)據(jù)會(huì)被臨時(shí)存放在該表中；當(dāng)執(zhí)行數(shù)據(jù)刪除操作時(shí)，相關(guān)聯(lián)的DELETE觸發(fā)器會(huì)被激活，系統(tǒng)則會(huì)自動(dòng)生成一張DELETED表，刪除的數(shù)據(jù)會(huì)臨時(shí)存放在該表中；而當(dāng)執(zhí)行數(shù)據(jù)更新操作時(shí)，相當(dāng)于先在數(shù)據(jù)表中刪除了需要更新的數(shù)據(jù)，后又插入了需要替代的新的數(shù)據(jù)，因此系統(tǒng)會(huì)自動(dò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)識(shí)符規(guī)則，不能以#或##開頭（以下出現(xiàn)不再贅述）。由于觸發(fā)器可以應(yīng)用在數(shù)據(jù)表或視圖上，table/view此處表示對(duì)其執(zhí)行的數(shù)據(jù)庫對(duì)象的名稱。需要注意的是，視圖只能被INSTEAD OF觸發(fā)器引用。WITH ENCRYPTION是可選項(xiàng)，用來對(duì)觸發(fā)器的代碼進(jìn)行加密。FOR/AFTER指定 DML觸發(fā)器僅在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行時(shí)才被觸發(fā)，AFTER可以省略。INSTEAD OF設(shè)置觸發(fā)器激活的時(shí)機(jī)為前觸發(fā)，需要注意的是不能為DDL觸發(fā)器指定INSTEAD OF。[INSERT][，UPDATE][，DELETE]表示激活觸發(fā)器的DML語句的類型，允許使用上述選項(xiàng)的任意順序組。

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

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

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

ALTER TABLE table_name DISABLE/ ENABLE TRIGGER trigger_name/ALL

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

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

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

4.3 觸發(fā)器的刪除

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

DROP TRIGGER trigger_name

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

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

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

班級(jí)（班級(jí)編號(hào)，班級(jí)名稱，人數(shù)，專業(yè)編號(hào)）

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

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

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

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

CREATE TRIGGER tri_stuInsert

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

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

AS

BEGIN

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

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

WHERE Classno=（SELECT Classno FROM INSERTED）

END

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

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

WHERE Sno IN （SELECT Sno

FROM DELETED）

END

5.3 使用級(jí)聯(lián)更新修改課程編號(hào)

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

CREATE TRIGGER tri_courseUpdate

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

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

AS

BEGIN

--分別定義局部變量存放原課程編號(hào)和新課程編號(hào)

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

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

SELECT@oldcno=deleted.cno，@newcno

=inserted.cno

FROM DELETED，INSERTED

UPDATE Result --對(duì)選課表的課程編號(hào)進(jìn)行更新

SET Cno=@newcno

WHERE Cno=@oldcno

END

6 結(jié)論（Conclusion）

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

參考文獻(xiàn)（References）

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

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

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

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

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

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

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

算機(jī)信息，2012，28（8）：173-175.

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

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

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

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

作者簡(jiǎn)介：

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

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