觸發(fā)器在數據庫設計中的應用

陳舒心

?

觸發(fā)器在數據庫設計中的應用

陳舒心

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

觸發(fā)器；SQLSERVER；數據庫

一、引言

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

二、觸發(fā)器

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

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

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

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

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

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

表1 Admin表結構

表2 News表結構

表3 News_operation表結構

三張數據表間的關聯如下：

1.Admin表與News表

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

2.News表與News_operation表

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

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

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 /*是數據添加操作*/

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/*是數據更新操作*/

DECLARE @author2 NVARCHAR(50)

select @author2=f_editer from deleted

insert into admin_log values(@author2,'更新新聞',getdate())

end

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

四、結束語

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

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

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

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

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

[4]彭嬌.觸發(fā)器在Oracle數據庫中的應用研究[J].軟件導刊，2015(6):151-152.

責任編輯 何麗華

2016-03-10

陳舒心(1978-)，廣東省輕工職業(yè)技術學校講師，碩士。研究方向：現代教育技術。(廣東 廣州/510300)

G712

A

1005-1422(2016)04-0110-03