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

徐曉麗

0 前言

在大型關(guān)系數(shù)據(jù)庫(kù)設(shè)計(jì)中，會(huì)經(jīng)常用到觸發(fā)器。它的特點(diǎn)是：一旦被定義，就存在于后臺(tái)數(shù)據(jù)庫(kù)系統(tǒng)（server，服務(wù)器方）中，并會(huì)在往表中插入記錄、更改記錄或者刪除記錄時(shí)，被自動(dòng)地隱式執(zhí)行，從而使得它的設(shè)計(jì)既與前臺(tái)（client，客戶機(jī)方）的平臺(tái)無關(guān)，又免除了前臺(tái)相關(guān)的數(shù)據(jù)操作設(shè)計(jì)。因此，觸發(fā)器可以用來對(duì)表實(shí)施復(fù)雜的完整性約束，當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)發(fā)生改變時(shí)，觸發(fā)器會(huì)自動(dòng)被激活，從而防止對(duì)數(shù)據(jù)的不正確修改。

1 觸發(fā)器的種類

根據(jù)觸發(fā)器被激活的時(shí)機(jī)不同，SQL Server 2000中提供了兩種類型的觸發(fā)器：INSTEAD OF觸發(fā)器和AFTER觸發(fā)器[1]。

AFTER觸發(fā)器在一個(gè)INSERT、UPDATE或DELETE語(yǔ)句完成之后執(zhí)行，進(jìn)行約束檢查等動(dòng)作都將在AFTER觸發(fā)器被激活之前發(fā)生。AFTER觸發(fā)器只能用于表。

INSTEAD OF觸發(fā)器用于替代引起觸發(fā)器執(zhí)行的T-SQL語(yǔ)句。除表之外，INSTEAD OF觸發(fā)器也可以用于視圖，用來擴(kuò)展視圖可以支持的更新操作。

一個(gè)表或視圖的每個(gè)修改動(dòng)作（INSERT、UPDATE和DELETE）都可以有一個(gè)INSTEAD OF觸發(fā)器，但可以有多個(gè)AFTER觸發(fā)器。

2 觸發(fā)器的作用

觸發(fā)器主要用于如下幾個(gè)方面：自動(dòng)生成派生列值；禁止非法事務(wù)；增強(qiáng)復(fù)雜的安全識(shí)別； 在分布式數(shù)據(jù)庫(kù)中增強(qiáng)參照完整性；增強(qiáng)復(fù)雜的商業(yè)規(guī)則；提供透明的事件日志；提供高級(jí)審計(jì)；維護(hù)同步表復(fù)制；在表存取上進(jìn)行聚合統(tǒng)計(jì)。開發(fā)人員一般是對(duì)上述幾種情況作組合使用，可歸為以下四種典型應(yīng)用。

（1）對(duì)庫(kù)中相關(guān)表進(jìn)行連環(huán)更新，如：鍵值的同步更新，數(shù)據(jù)冗余實(shí)現(xiàn)，計(jì)算表的同步更新等；

（2）實(shí)現(xiàn)那些破壞完整性操作的拒絕，如：不匹配外鍵值的插入拒絕；

（3）實(shí)現(xiàn)庫(kù)定義本身所不能實(shí)現(xiàn)的更為復(fù)雜的商業(yè)規(guī)則，如：更新操作的時(shí)間限制，更新數(shù)據(jù)的幅度限制等；

（4）實(shí)現(xiàn)簡(jiǎn)單的“如果……怎么辦”的分析。

3 觸發(fā)器的實(shí)現(xiàn)

觸發(fā)器的實(shí)現(xiàn)離不了以下兩個(gè)專用表：Inserted表和Deleted表。這是兩個(gè)邏輯表，由系統(tǒng)來維護(hù)，用戶不能對(duì)它們進(jìn)行修改。它們存放在內(nèi)存而不是數(shù)據(jù)庫(kù)中。這兩個(gè)表的結(jié)構(gòu)總是與激活該觸發(fā)器的表的結(jié)構(gòu)相同。觸發(fā)器執(zhí)行完成后，與該觸發(fā)器相關(guān)的這兩個(gè)表也會(huì)被刪除。

Deleted表存放由于執(zhí)行DELETE或UPDATE語(yǔ)句而要從表中刪除的所有行。在執(zhí)行DELETE或UPDATE操作時(shí)，被刪除的行從表中被移動(dòng)到Deleted表，這兩個(gè)表不會(huì)有共同的行。

Inserted表存放由于執(zhí)行INSERT或UPDATE語(yǔ)句而要向表中插入的所有行。在 INSERT或UPDATE事務(wù)中，新的行同時(shí)添加到激活觸發(fā)器的表和Inserted表中，Inserted表的內(nèi)容是激活觸發(fā)器的表中新行的拷貝。

在創(chuàng)建觸發(fā)器時(shí)需要制定以下內(nèi)容：觸發(fā)器的名稱、觸發(fā)器所基于的表或視圖、觸發(fā)器種類（AFTER 或 INSTEAD OF）、激活觸發(fā)器的修改語(yǔ)句（INSERT、UPDATE和DELETE）、觸發(fā)器執(zhí)行的語(yǔ)句，用T-SQL語(yǔ)句創(chuàng)建觸發(fā)器的具本語(yǔ)法如下：[2]

CREATE Trigger trigger_name

ON {table_name | view_name}

{ AFTER | INSTEAD OF}

[INSERT，UPDATE，DELETE]

AS

Sq_Statement

4 實(shí)際應(yīng)用

在一個(gè)Market數(shù)據(jù)庫(kù)中有Goods表、Customers表及Orders表，三個(gè)表的結(jié)構(gòu)如下所示：

Goods:

Customers:

Orders:

現(xiàn)要求當(dāng)向Orders表中插入一條記錄時(shí)，ordersum（訂貨金額）字段的值由quantity*price而得到，并且如課該客戶是重點(diǎn)客戶，則對(duì)其打8.5折，若是一般客戶，則不打折。

分析：由于quantity字段和price字段分別來自不同的表中，因此不能在創(chuàng)建表時(shí)用公式來完成。為了實(shí)現(xiàn)此功能，我們可考慮在Orders中針對(duì)Insert操作建立一個(gè)INSTEAD OF觸發(fā)器，在觸發(fā)器中查詢下定單的客戶的類別及所定貨品的價(jià)格，計(jì)算出貨品的ordersum的值；然后再將記錄插入到Orders表中。具體代碼如下所示：

CREATE TRIGGER orderinsert ON OrderS

INSTEAD OF INSERT

AS

declare @price money，@quantity int，@type varchar（10）

declare @sum money

select @price=price，@quantity=quantity，@type=type from goods，inserted，customers where goods.name=inserted.goodsnameandcustomers.customerid=inserted.customerid

if @type='一般客戶'

set @sum=@price*@quantity

else

set @sum=@price*@quantity*0.85

insert into orders（goodsname，customerid，quantity，ordersum，orderdate）select goodsname，customerid，quantity，@sum，orderdate from inserted.

觸發(fā)器建成以后，只有向order表中插入記錄，就會(huì)自動(dòng)計(jì)算ordersum字段的值，從而實(shí)現(xiàn)了比默認(rèn)值及公式都更為復(fù)雜的字段默認(rèn)值的設(shè)置。

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

本文通過使用觸發(fā)器，實(shí)現(xiàn)了通過幾個(gè)表的數(shù)據(jù)對(duì)某一字段進(jìn)行自動(dòng)計(jì)算的功能，完成了比CHECK約束更復(fù)雜的限制。當(dāng)然，也可以通過觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)庫(kù)的級(jí)聯(lián)更新、級(jí)聯(lián)刪除及某些字段的自動(dòng)更新功能（如增加某一訂單時(shí)，減少Goods表中相應(yīng)貨品的庫(kù)存量）。綜上所述，觸發(fā)器的主要特點(diǎn)在于，不管何種原因造成數(shù)據(jù)變化，它均能自動(dòng)響應(yīng)。觸發(fā)器對(duì)數(shù)據(jù)庫(kù)開發(fā)過程中遇到的問題，往往會(huì)有獨(dú)到的解決方法。因此，在當(dāng)今數(shù)據(jù)庫(kù)系統(tǒng)的建立中，系統(tǒng)開發(fā)人員除了使用視圖、存儲(chǔ)過程等技術(shù)外，大多也使用觸發(fā)器技術(shù)，以改進(jìn)數(shù)據(jù)庫(kù)系統(tǒng)建立的質(zhì)量，使數(shù)據(jù)庫(kù)的設(shè)計(jì)變得簡(jiǎn)潔和高效。

[1]羅運(yùn)模，王珊等《SQL Server數(shù)據(jù)庫(kù)系統(tǒng)基礎(chǔ)》[M].高等教育出版社.

[2][美]James R.Groff Paul N.Weinberg 著.章小莉，寧欣，汪永好.等譯《SQL 完全手冊(cè)》[M].電子工業(yè)出版社.

[3]郭勐，張擁軍，彭宇行.數(shù)據(jù)庫(kù)觸發(fā)器機(jī)制的設(shè)計(jì)與實(shí)現(xiàn)[J].電子技術(shù)應(yīng)用，2005，（02）.

[4]莫裕清，SQL Server 2000中基于觸發(fā)器的自動(dòng)更新[J].中國(guó)科技信息.2006 Vol.8 P.221-222.

[5]黃曉濤，數(shù)據(jù)庫(kù)觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)庫(kù)系統(tǒng)的主動(dòng)功能[J].計(jì)算機(jī)應(yīng)用研究.2005 Vol.21 No.2 P.238-240.

[6]周愛華，南理勇.SQL Server 2000數(shù)據(jù)庫(kù)觸發(fā)器的應(yīng)用[J].商場(chǎng)現(xiàn)代化，2008，（07）.