觸發(fā)器在數(shù)據(jù)庫設(shè)計中的應(yīng)用

摘要：觸發(fā)器是一種特殊的存儲過程。它的執(zhí)行不是通過顯性的調(diào)用，而是通過對相應(yīng)表格數(shù)據(jù)實現(xiàn)插入、更新或刪除時自動激活執(zhí)行。在程序開發(fā)過程中，可以通過觸發(fā)器對程序后臺數(shù)據(jù)實施復(fù)雜的完整性約束。本文以本校示范校建設(shè)專題網(wǎng)站管理后臺設(shè)計為例，闡述觸發(fā)器的概念及其在網(wǎng)站功能實現(xiàn)中的定義及應(yīng)用。

關(guān)鍵詞：觸發(fā)器；SQLSERVER；數(shù)據(jù)庫

中圖分類號：G712文獻(xiàn)標(biāo)識碼：A文章編號：1005-1422（2016）04-0110-03

作者簡介：陳舒心（1978-），廣東省輕工職業(yè)技術(shù)學(xué)校講師，碩士。研究方向：現(xiàn)代教育技術(shù)。（廣東 廣州/510300）

一、引言

隨著網(wǎng)絡(luò)技術(shù)的發(fā)展，網(wǎng)站瀏覽成為我們生活中不可或缺的一部分。一個制作精良的網(wǎng)站，除了要有精美的前臺頁面，必然也離不開后臺數(shù)據(jù)的處理。在復(fù)雜的數(shù)據(jù)處理過程中，保證網(wǎng)站數(shù)據(jù)的完整性尤為重要。數(shù)據(jù)完整性是指存儲在數(shù)據(jù)庫中的數(shù)據(jù)正確無誤并且相關(guān)數(shù)據(jù)具有一致性。通常在數(shù)據(jù)庫設(shè)計階段，可以通過創(chuàng)建表格的約束來實現(xiàn)數(shù)據(jù)完整性。譬如：通過主鍵約束或唯一鍵約束可以規(guī)范表格中某一字段的值不可重復(fù)；通過檢查約束可以規(guī)范表格中某一字段的取值范圍。但這些約束對不符合條件的數(shù)據(jù)只能做出單一拒絕執(zhí)行的動作。如果需要實現(xiàn)較為復(fù)雜反饋或操作時，這些約束還是存在一定的局限性的。如果要求處理數(shù)據(jù)過程中，系統(tǒng)能自動根據(jù)用戶對表格的操作做出不同的響應(yīng)執(zhí)行某些操作，那么，就可以使用觸發(fā)器去實現(xiàn)。

二、觸發(fā)器

觸發(fā)器是一種特殊的存儲過程，但它不像存儲過程能直接通過函數(shù)名被調(diào)用。當(dāng)受觸發(fā)器保護(hù)的數(shù)據(jù)受到某些操作影響時，觸發(fā)器會被數(shù)據(jù)庫系統(tǒng)激活執(zhí)行。因此，從激活操作的角度，觸發(fā)器可分以下三種類型：插入觸發(fā)器、更新觸發(fā)器和刪除觸發(fā)器。無論是哪種操作，由于觸發(fā)執(zhí)行的時機(jī)不同，SQLServer提供了兩種前綴類型不同的觸發(fā)器定義：After觸發(fā)器和Instead of觸發(fā)器。

After觸發(fā)器要求只執(zhí)行插入、更新、刪除中任一操作之后激活觸發(fā)器執(zhí)行。而Instead of觸發(fā)器的激活時機(jī)剛好相反，它是在執(zhí)行插入、更新、刪除中任一操作之前就被激活。因此，在執(zhí)行Instead of觸發(fā)器時，系統(tǒng)所做的相關(guān)操作都是未被執(zhí)行的。在SQLServer中，After觸發(fā)器是默認(rèn)觸發(fā)器。

在每一個觸發(fā)器被激活時，數(shù)據(jù)庫系統(tǒng)都會自動創(chuàng)建兩張數(shù)據(jù)表：Inserted表和Delete表。它們只保存在內(nèi)存中，并不真實存在于數(shù)據(jù)庫表對象中。兩張表的結(jié)構(gòu)與觸發(fā)器作用的表格的結(jié)構(gòu)相同。Instead表中存放的是系統(tǒng)執(zhí)行INSERT 和UPDATE 語句時被影響的數(shù)據(jù)行的副本。在一個插入或更新事務(wù)處理中， 新添加的行會同時被添加到Inserted 表和觸發(fā)器作用的數(shù)據(jù)表中。Deleted 表用于存儲系統(tǒng)執(zhí)行DELETE 和UPDATE 語句時被影響的數(shù)據(jù)行的副本。在執(zhí)行DELETE或UPDATE 語句時，相關(guān)數(shù)據(jù)行從數(shù)據(jù)表中刪除，同時被傳送到Deleted 表中。Deleted 表和觸發(fā)器作用的數(shù)據(jù)表一般不會出現(xiàn)內(nèi)容完全相同的數(shù)據(jù)行。當(dāng)觸發(fā)器執(zhí)行成功，數(shù)據(jù)操作語句執(zhí)行完成后，整個事務(wù)過程才算完成。否則，這個事務(wù)要回滾。因此，觸發(fā)器定義的條件不能與表格中已經(jīng)定義的完整性約束相矛盾。當(dāng)觸發(fā)器完成后，Inserted表和Delete表會自動刪除。

三、應(yīng)用實例——基于T-SQL語言的觸發(fā)器的設(shè)計

本校在示范校建設(shè)過程中，需要搭建示范校建設(shè)專題網(wǎng)站用以發(fā)布示范校項目建設(shè)過程的動態(tài)信息、通知公告及工作簡報。同時展示項目建設(shè)的方案、資源、成果等。網(wǎng)站后臺的主體功能是進(jìn)行新聞信息與用戶信息的管理。數(shù)據(jù)后臺分別設(shè)計了數(shù)據(jù)表Admin（用戶信息表）、News_operation（新聞操作日志）、News（新聞發(fā)布信息表）、News_Class（新聞類別表）、News_Picture（新聞圖片信息表）等表格，用以存儲網(wǎng)站上相關(guān)數(shù)據(jù)。

由于文章篇幅問題，不能將整個網(wǎng)站數(shù)據(jù)后臺所做設(shè)計一一闡述，因此，在下文中以其中的三張有關(guān)聯(lián)的數(shù)據(jù)表Admin、News、News_operation為例，闡述選擇觸發(fā)器實現(xiàn)完整性約束的原因及設(shè)計過程。具體表格結(jié)構(gòu)如表1、表2、表3所示。

三張數(shù)據(jù)表間的關(guān)聯(lián)如下：

1.Admin表與News表

在網(wǎng)站上的新聞公告，必須是通過注冊賬號登錄成功后才能發(fā)布，發(fā)布者的名稱要與登錄的賬號一致。因此，對于Admin表的UserName字段的值和News表的Author字段的值必須一致，這種一致性可以通過定義兩張表格UserName字段和Author字段主外鍵約束實現(xiàn)。其中，UserName字段為主鍵，Admin表為主鍵表；Author字段為外鍵，News表為外鍵表。主外鍵約束條件下，通常可以通過定義相關(guān)數(shù)據(jù)級聯(lián)更新和級聯(lián)刪除來達(dá)到數(shù)據(jù)完整性。所謂級聯(lián)更新和刪除，就是主鍵表中的數(shù)據(jù)被執(zhí)行更新或者刪除操作時，與其相關(guān)的外鍵表中的對應(yīng)數(shù)據(jù)也自動進(jìn)行更新或者刪除。顯然，在本站中設(shè)置Admin表的UserName字段和News表的Author字段級聯(lián)更新時合適的。但是如果設(shè)置級聯(lián)刪除，則會導(dǎo)致某些新聞公告在發(fā)布用戶注銷后也被刪除，造成信息的丟失。因此，在主鍵表Admin表執(zhí)行數(shù)據(jù)刪除時為保證外鍵表News表中對應(yīng)數(shù)據(jù)不丟失的前提下，又可以達(dá)到數(shù)據(jù)的一致性，筆者選擇通過為Admin表設(shè)計一個刪除觸發(fā)器，在數(shù)據(jù)執(zhí)行刪除操作時系統(tǒng)激活，將News表中的Author字段設(shè)置為空值。

2.News表與News_operation表

考慮到每個注冊用戶登錄網(wǎng)站后，都可以對數(shù)據(jù)實行增刪改的操作。為保障數(shù)據(jù)安全，網(wǎng)站需要跟蹤記錄執(zhí)行的操作的用戶、操作類型及執(zhí)行操作的時間。因此，當(dāng)用戶對News表數(shù)據(jù)執(zhí)行操作時，相關(guān)執(zhí)行的信息將會記錄至News_operation表中。通過在表格中設(shè)置單純的完整性約束，很難實現(xiàn)此功能。當(dāng)然也可以考慮借助開發(fā)工具，編寫前臺代碼實現(xiàn)。但若通過前臺編碼實現(xiàn)此功能，不僅會增加程序員的工作量，而且每次在執(zhí)行到編碼時都必須經(jīng)過編譯，將指令從前臺通過網(wǎng)絡(luò)傳輸?shù)胶笈_，才能達(dá)到目的。在這個過程中必然會耗費一定是時間和網(wǎng)絡(luò)帶寬。若果通過觸發(fā)器去實現(xiàn)，由于觸發(fā)器是直接存儲在數(shù)據(jù)后臺，可以在后臺數(shù)據(jù)設(shè)計階段就設(shè)計定義好，因此在此設(shè)計觸發(fā)器去實現(xiàn)此需求更為合適。

以Admin、News、News_operation為基本表，需要滿足以下要求：①Admin表中用戶信息刪除時，該用戶發(fā)布的所有新聞公告的發(fā)布人Author字段置為空值（Null）；②用戶對News表數(shù)據(jù)進(jìn)行刪除或者修改或者添加時，將用戶對表格操作記錄到Admin_log表中。觸發(fā)器實現(xiàn)如下：

1.發(fā)布人Author字段置空

create trigger News_delete

on Adimin for delete

as

update News

set Author=Null

FROM News，Deleted

whereNews.ID=Delete.ID

2.記錄用戶對新聞表的操作

create trigger News_operation

on News for insert，update，delete

as

if not exists（select1 from inserted） and exists（select 1 from deleted）

begin /*inserted表無記錄，是刪除操作*/

DECLARE @author NVARCHAR（50）

select @author=f_editer from deleted

insert into admin_log values（@author，'刪除新聞'，getdate（））

end

else if not exists（select 1 from deleted） and exists（select1from inserted）begin /*是數(shù)據(jù)添加操作*/

DECLARE @author1 NVARCHAR（50）

select @author1=f_editer from inserted

insert into admin_log values（@author1，'添加新聞'，getdate（））

end

else if exists（select 1 from deleted） and exists（select 1 from inserted）

begin/*是數(shù)據(jù)更新操作*/

DECLARE @author2 NVARCHAR（50）

select @author2=f_editer from deleted

insert into admin_log values（@author2，'更新新聞'，getdate（））

end

以上觸發(fā)器的功能均已通過系統(tǒng)測試。

四、結(jié)束語

在系統(tǒng)開發(fā)過程中，觸發(fā)器設(shè)計可以保障后臺數(shù)據(jù)的完整性。當(dāng)然，上述的功能也可以借助開發(fā)工具的前臺代碼實現(xiàn)，但是觸發(fā)器所帶來的系統(tǒng)開銷會更加小。觸發(fā)器是存儲在本地服務(wù)器上的，在執(zhí)行時可以節(jié)省網(wǎng)絡(luò)傳輸過程中消耗的時間。而且，觸發(fā)器執(zhí)行過程中如果出現(xiàn)任何情況的錯誤導(dǎo)致操作失敗，之前所做的一切都會恢復(fù)，因此它能最大程度的保證數(shù)據(jù)的完整性。

由此可見，合理地設(shè)計觸發(fā)器，既可以使數(shù)據(jù)更安全，又可以減少程序開發(fā)人員的勞動量。若能在程序開發(fā)中熟練地將觸發(fā)器和其他數(shù)據(jù)對象有機(jī)集合，將能大大提高程序執(zhí)行的效率。

參考文獻(xiàn)：

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

[2]胡鶴年.SQL Server觸發(fā)器在數(shù)據(jù)庫設(shè)計中的應(yīng)用[J].數(shù)據(jù)庫與信息管理，2012（8）：37-38.

[3]劉春蕾.觸發(fā)器在數(shù)據(jù)庫編程中的應(yīng)用[J].電腦開發(fā)與應(yīng)用，2014（4）：56-58.

[4]彭嬌.觸發(fā)器在Oracle數(shù)據(jù)庫中的應(yīng)用研究[J].軟件導(dǎo)刊，2015（6）：151-152.

責(zé)任編輯 何麗華