SQL Server觸發器

一 觸發器基礎

二 對特定列的更改進行測試

三 INSTEAD OF 觸發器

[@more@]

一 觸發器基礎

什麼是觸發器？在SQL Server中，當對某一個表進行操作時，觸發某種條件，從而執行的一段程式。觸發器是一個特殊的儲存過程。

常見的觸發器有三種：分別應用於Insert , Update , Delete 事件。(SQL Server 2000定義了新的觸發器，這裡不提）

我為什麼要使用觸發器？

比如，這麼兩個表：

Create Table Student( --建立學生表

StudentID int primary key, --學號，定義主鍵約束

....

)

Create Table BorrowRecord( --建立借書記錄表

BorrowRecord int identity(1,1), --自動增長流水號

StudentID int , --學號

BorrowDate datetime, --借出時間

ReturnDAte Datetime, --歸還時間

...

)

用到的功能有:

1.如果我更改了學生的學號,我希望他的借書記錄仍然與這個學生相關(也就是同時更改借書記錄表的學號);

2.如果該學生已經畢業，我希望刪除他的學號的同時，也刪除它的借書記錄。

等等。

這時候可以用到觸發器。對於1，建立一個Update觸發器：

Create Trigger truStudent

On Student

for Update --觸發器型別--Update

-------------------------------------------------------

--Name:truStudent

--func:更新BorrowRecord 的StudentID，與Student同步。

--Use :None

--User:System

--Author: wp_love

--Date : 2003-4-16

--Memo : 臨時寫寫的，給大家作個Sample。沒有除錯阿。

-------------------------------------------------------

As

if Update(StudentID)

begin

Update BorrowRecord

Set br.StudentID=i.StudentID

From BorrowRecord br , Deleted d ,Inserted i

Where br.StudentID=d.StudentID

end

理解觸發器裡面的兩個臨時的表：Deleted , Inserted 。注意Deleted 與Inserted分別表示觸發事件的表“舊的一條記錄”和“新的一條記錄”。

一個Update 的過程可以看作為：生成新的記錄到Inserted表，複製舊的記錄到Deleted表，然後刪除Student記錄並寫入新紀錄。

對於2，建立一個Delete觸發器

Create trigger trdStudent

On Student

for Delete

-------------------------------------------------------

--Name:trdStudent

--func:同時刪除 BorrowRecord 的資料

--Use :None

--User:System

--Author:

--Date : 2003-4-16

--Memo : 臨時寫寫的，給大家作個Sample。沒有除錯阿。

-------------------------------------------------------

As

Delete BorrowRecord

From BorrowRecord br , Delted d

Where br.StudentID=d.StudentID

從這兩個例子我們可以看到了觸發器的關鍵：A.2個臨時的表；B.觸發機制。

二 對特定列的更改進行測試

觸發器定義中的 IF UPDATE (列名)子句可用來確定 INSERT 或 UPDATE 語句是否影響到表中的一個特定列。無論何時，當列被賦值時，該子句即為 TRUE。

說明 由於列中的特定值無法使用 DELETE 語句刪除，IF UPDATE 子句也就不能應用於 DELETE 語句。

或者可以用 IF COLUMNS_UPDATED()子句檢查表中已用 INSERT 或 UPDATE 語句更新的列。該子句使用整型位掩碼指定需測試的列。

示例

A. 使用 IF UPDATE 子句測試資料修改

下例在表 my_table 中建立名為 my_trig 的 INSERT 觸發器，並測試列 b 是否受到任何 INSERT 語句的影響。

CREATE TABLE my_table*

(a int NULL, b int NULL)

GO

CREATE TRIGGER my_trig

ON my_table

FOR INSERT

AS

IF UPDATE(b)

PRINT 'Column b Modified'

GO

B. 使用 COLUMNS UPDATED() 子句測試資料修改

下例使用 COLUMNS_UPDATED() 子句可以獲得相似的結果。

CREATE TRIGGER my_trig2

ON my_table

FOR INSERT

AS

IF ( COLUMNS_UPDATED() & 2 = 2 )

PRINT 'Column b Modified'

GO

三 INSTEAD OF 觸發器

原著：John Papa

翻譯：Anlee

原文出處：MSDN Magazine Jan 2004(Data Points)

下載原始碼：vckbase.com/code/downcode.asp?id=2242">DataPoints0401.exe (111KB)

　　這個月我會繼續上個月的專欄——“SQL SERVER觸發器的基礎知識及其特點” 。我開這個專欄的目的是為了回應我收到的許多關於觸發器的問題。我會從 INSTEAD OF 觸發器以及它與AFTER 觸發器差別開始講起。然後我會在討論一些使用 INSTEAD OF 觸發器的情形。其中包括允許幾個檢視對多個表進行更新。最後，我會討論觸發器怎樣去處理事務，編寫改變某個表中多行記錄的觸發器，以及確定在觸發器中哪些列被修改。

INSTEAD OF 觸發器

　　AFTER 觸發器（也叫“FOR”觸發器）會在觸發 insert、update 或是delect 動作之後執行。例如，一個 Employees 表上的 AFTER 觸發器會在在 Employee 表上執行一條 update 語句後啟用。因此，AFTER 觸發器只有在已插入一行或是多行和所有約束已被處理且透過後才觸發。INSTEAD OF 觸發器和 AFTER 觸發器有本質上的不同，因為 INSTEAD OF 觸發器代替觸發動作進行激發。就拿同樣的例子來說，如果在 Emplyees 表上有一個 INSTEAD OF UPDATE 觸發器和在這個表上執行一條 UPDATE 語句，結果是這條 UPDATE 語句並不會改變 Employee 表中的任何一行。相反,這條 UPDATE 語句只有是為了踢離 INSTEAD OF UPDATE 觸發器，這個觸發器可能會，也可能不會改變 Employees 表中的資料。

　　因此，怎麼決定在合適的時間和位置放置 INSTEAD OF 觸發器呢？有幾個關鍵的因素在做決定是值得考慮的。AFTER 觸發器多用在動作必須在表中資料發生改變之後才執行後情情況。比如，AFTER 觸發器可以用於將對資料作任何變動的日誌記錄在一個相對獨立的審計表中。INTEAD OF 觸發器也能做同樣的工作。但是 INSTEAD OF 觸發器在這個情況下的效率比較低，因為更新動作只能在將它發生的動作準確地記錄在審計表之後才允許執行。

　　一般來說，只要不影響資料的修改，AFTER 觸發器比 INSTEAD OF 觸發器更有效率。在對資料進行計算或是對資料的修改作為一個整體提交或是作為一個整體回退的情況下，AFTER 觸發器也是一個很好的選擇。例如，存在這樣一條規則：對在 Products 表的產品價格的變動超過30％的必須回退。AFTER 觸發器能很漂亮地完成這個工作，它利用已插入同已刪除的表中的產品價格作比較，然後在有必要的時回滾事務。這些都是 AFTER 觸發器的理想條件，但有時 INSTEAD OF會更好些。

　　INSTEAD OF 觸發器有一個很大的特點——就是它允許你在某個表或檢視上用多個複雜的查詢操作來代替單一的查詢。跟 AFTER 觸發器只能對錶起作用不同，INSTEAD OF 觸發器可以同時對錶和檢視起作用。我常常被問到怎麼樣去解決這種情況：有一個多表組成的檢視，如何對該檢視進行一次更新。如果檢視包含有關鍵欄位和包含有基本表的某些欄位，這只是簡單的更新基本表。但是，當有檢視中包含有多個基本表示，邏輯上的更新比單單一個 UPDATE 語句會更復雜。因此，你是怎麼利用什麼可以替代的工具來解決這個問題的呢？其中一個方法就是將一個INSTEAD OF 觸發器放在檢視上。INSTEAD OF 觸發器可以定義在一個或多個表上.INSTEAD OF 觸發器就能轉開在多個基本表中修改的範圍.

　　例如，如果一個檢視將 Customers、Products、orders 和 OrderDteils 等表合併成一個檢視，並利用檢視透過程式在螢幕上來顯示所有的資料。更新操作便允許用來代替這個檢視，假如存在一個這個樣的檢視：它包含 Northwind 資料庫中的四個表，並且被命名為vwCustomersOrdersOrderDetailsProducts，它看起來像這樣(Figure 1)：

Figure 1 連線 Customers 及其 Order Details 的檢視

CREATE VIEW vwCustomersOrdersOrderDetailsProducts

AS

SELECT

c.CustomerID,

c.CompanyName,

o.OrderID,

o.OrderDate,

od.UnitPrice,

od.Quantity,

od.Discount,

p.ProductID,

p.ProductName

FROM Customers c

INNER JOIN Orders o ON c.CustomerID = o.CustomerID

INNER JOIN [Order Details] od ON o.OrderID = od.OrderID

INNER JOIN Products p ON od.ProductID = p.ProductID

GO　　

vwCustomersOrdersOrderDetailsProducts 檢視連線著四個表，並且每個表都暴露一個取樣欄位。必須記住的一點是，當你設計一個含有 INSTEAD OF UPDATE 的觸發器時，將每個表的主關鍵欄位包含在SELECT語句中是很有益的做法。即使這些欄位在應用程式不會用到，它們也以在 INSTEAD OF 觸發器中用來定位將要被修改的行，然後對基表作相應的修改。假設你打算允許更新該檢視以便按非關鍵字過濾基表。更新程式碼應該寫在 INSTEAD OF UPDATE 觸發器中，讓觸發器去更新 Customers 表中的 CompnayName 列，Orders 表中的 OrderDate 列，Order Details 表的 UnitPrice 和 Quantity 列以及在 Products 表中的 ProductName 列。在這種情況下，使用 AFTER 觸發器就不適合了，而 INSTEAD OF 觸發器則是一個很好的選擇，參見 Figure 2：

Figure 2 用 INSTEAD OF 觸發器更新檢視

CREATE TRIGGER tr_vwCustomersOrdersOrderDetailsProducts_IO_U

ON vwCustomersOrdersOrderDetailsProducts

INSTEAD OF UPDATE

AS

-- 更新 Customers

UPDATE Customers SET CompanyName = i.CompanyName

FROM inserted i

INNER JOIN Customers c ON i.CustomerID = c.CustomerID

-- 更新 Orders

UPDATE Orders SET OrderDate = i.OrderDate

FROM inserted i

INNER JOIN Orders o ON i.OrderID = o.OrderID

-- 更新 Order Details

UPDATE [Order Details] SET UnitPrice = i.UnitPrice, Quantity = i.Quantity

FROM inserted i

INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND i.ProductID = od.ProductID

-- 更新 Products UPDATE Products SET ProductName = i.ProductName

FROM inserted i

INNER JOIN Products p ON i.ProductID = p.ProductID

GO

注意在 Figure 2 中的 INSTEAD OF UPDATE 觸發器包含了四個 UPDATE 語句。每個 UPDATE語句目的都是為了對其中一個基表中的非關鍵欄位進行修改。在 UPDATE 語句中包含了每個表中的關鍵欄位對應於檢視中的欄位。這樣就允許 UPDATE 語句在相應的表中定位對應的列並只對這些列作修改。下面的 UPDATE 語句將對 INSTEAD OF 觸發器進行測試：

UPDATE vwCustomersOrdersOrderDetailsProducts

SET

Quantity = 100,

UnitPrice = 20,

CompanyName = ‘’,

Fake Name = ‘’,

OrderDate = ‘11/23/2001’,

ProductName = ‘’,

Widget = ''''

WHERE OrderID = 10265 AND ProductID = 17　　

　　如果你（透過檢視或是表自身）檢查相應表中的值，很明顯，這些值已被更新了。當然，對INSTEAD　OF 觸發器作一些改變會使其有不同的結果。例如，不存在寫一個觸發器去改變四個基表的需求，因此，可以將觸發器中的一個或是多個 UPDATE 語句刪去。假設 INSTEAD　OF 觸發器僅僅是為了更新 Order Details 表的值，這就會僅僅更新在 Order Details 表中的欄位，而忽視任何在其他基表上的修改。在這種情況下，在 Customers,Products 或是 Orders 表中不會產生任何錯誤同時也不會發生任何改變。當然，如果這三個表中的某些欄位發生改變的話，會發生報錯。如我呆會在這篇文章會討論的一樣，UPDATE 和 COLUMNS＿UPDATED 函式是個檢測哪些欄位發生改變的理想的方法。

　　Figure 2 也演示了怎麼寫一個觸發器修改多行記錄。注意到 UPDATE 語句如何按關鍵字連線被插入的表和各個基表。這就保證更新是對所有的行，這些行在檢視中被原有的 UPDATE 語句修改。透過迴圈被插入表的記錄行也能完成該操作。不管怎麼樣，通常避免使用遊標是個好主意，尤其是在使用觸發器時更應如此。SQL SERVER 被設計成以資料集的方式來處理資料，而遊標是為一次處理一個資料行而設計的。在觸發器中使用遊標會降低程式的效能，因此，最好能使用象 Figure 2 中那樣更有效代替方法或使用一個子查詢。

　　另一個改變 INSERT OF UPDATE 觸發器的方法就是使其在檢視的 INSERT 和 DELETE 語句中激發。這也就意味著在適當的地方，觸發器會實現 INSERT 或是 DELETE 的功能。但是必須記隹的是 DELETE 可能會刪除多個記錄，這關鍵在於觸發器是怎樣寫的。因此，檢查觸發器的需求，在實現之前進行測試，這些做法十分重要。INSERT　OF UPDATE 觸發器可寫在檢視中，因此它可插入一個新的顧客、訂單、詳細的訂單和產品。這個觸發器也可以用來在插入一個新顧客之前檢查這個顧客是否是新的(對其它記錄的操作也是一樣)。當採用的是 INSTEAD OF 觸發器時存在有許多機會，但是，當然，觸發器是為解決相應的需求這才是它的本質。

　　通常，當引用一張表的 UPDATE 語句試圖去賦值一個計算型的，恆等型的或是時間戳型的列時，會產生一個錯誤，因為這些列的值必須是由SQL SERVER來決定的。這些列必須被包含在UPDATE 語句中以便能滿足列不能為空的要求。但是，如果 UPDATE 語句用 INSTEAD OF 觸發器引用一個檢視，定義在觸發器中的邏輯可以旁路掉這些列來避免錯誤的發生。為了達到這個目的，觸發器決不能嘗試去更新基表中相應列的值(讓它們遠離 UPDATE 語句的 SET 從句)。當某一條被處理的記錄來自被插入的表時，計算型的，恆等型的或是時間戳型的列可以用一個虛假值以滿足不為空值的要求，這時，INSTEAD OF 觸發器將忽略這些值，正確的值由 SQL SERVER 設定。

更新分開的列

　　INSTEAD OF 觸發器也很普遍地用於更新基表中計算型的列。例如，假設存在有如下這樣一個叫 vwOrdersOrderDetailsProducts 的檢視：

CREATE VIEW vwOrdersOrderDetailsProducts

AS

SELECT

o.OrderID,

o.OrderDate,

od.UnitPrice * od.Quantity AS ExtendedPrice,

p.ProductID,

p.ProductName

FROM Orders o

INNER JOIN [Order Details] od ON o.OrderID = od.OrderID

INNER JOIN Products p ON od.ProductID = p.ProductID

GO

這個檢視揭示了一個計算型的列叫ExtendedPrice，這個列不能被直接被更新，因為它不能將其自己變為表中獨立的一列。雖然你可實現這樣一個生意規則，在這個規則中ExtendedPrice透過這個檢視來修改，Quantity列不應修改，但是UnitPrice可被修改(我知道這條規則有點奇怪，但我可以忍受這點)。可以寫一個INSTEAD OF UPDATE觸發器來增強這條生意規則，其程式碼如下所示：

CREATE TRIGGER tr_vwOrdersOrderDetailsProducts_IO_U

ON vwOrdersOrderDetailsProducts

INSTEAD OF UPDATE

AS

UPDATE [Order Details]

SET UnitPrice = i.ExtendedPrice / Quantity

FROM inserted i

INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND i.ProductID = od.ProductID

GO

這些程式碼揭示了怎樣用一個在INSTEAD　OF　觸發器中的邏輯來代替對一個計算型列的更新。假設一個產品在一張特定的定單表中Quantity為100而ExtendedPrice要更新為200，這時新的UnitPrice值就變為2。在這種情況下，在執行一個對ExtendedPrice列進行修改的UPDATE語句時，最終的結果是UnitPrice被賦為ExtendedPrice除以Quantity的商。下面的程式碼可以用來測試這種情況：

UPDATE vwOrdersOrderDetailsProducts

SET ExtendedPrice = 200

WHERE OrderID = 10265AND ProductID = 17

檢查改變

　　在INSTEAD　OF和AFTER觸發器中都有UPDATE和COLUMNS＿UPDATE功能，這二種功能允許由觸發器決定哪些欄位由觸發器的語句來改變。例如，下面的觸發器阻止任何對Employees表中的lastname欄位進行修改。在這裡，UPDATE功能用來決定對哪些對欄位的修改可以執行。如果超出發生了改變（而又是不允許修改的）就會產生一個錯誤。PAISERR OR功能和事務就會回退，回退會撤消所做的任何修改。UPDATE功能都可以在AGTER觸發器和INSTEAD OF觸發器中工作，而不是在外部工作。

CREATE TRIGGER tr_Employees_U on Employees

AFTER UPDATE

AS

IF UPDATE(lastname)

BEGIN

RAISERROR ('cannot change lastname', 16, 1)

ROLLBACK TRAN

RETURN

END

GO

　　UPDATE功能是為了判斷單一列是否被INSERT或是UPDATE語句修改過。UPDATE(列)是一個用來檢測更新的標準的方法。但是當需要用來他檢測多列是否受到INSERT或UPDATE語句的影響時就變得更低效率。而這恰恰是COLUM_UPDATE功能的一個亮點。COLUMN_UPDATE功能返回一個位掩碼來判斷特定的列是否被修改過。位掩碼是包含在被表中被修改的列中的一個位元，目的是為了在表模式中定義這些列。如果一行修改，這位元位的值就為1，否則為0。不像從右到左地讀位元組的常規方法，位掩碼是從左往右讀。例如，下面的程式碼提示了一個在Order Details表中的觸發器，這個觸發器是為了檢測Quantity和UnitPrice二個欄位是否被修改過。

CREATE TRIGGER tr_OrderDetails ON [Order Details]

AFTER UPDATE

AS

IF (COLUMNS_UPDATED() = 12)

BEGIN

RAISERROR ('Cannot change both UnitPrice and Quantity at the same time', 16, 1)

ROLLBACK TRAN

END

GO

　　如果這個欄位都被修改了，就會產生一個錯誤，同時事務也將回滾。就拿Order Details表來說，COLUMN_UPDATED功能返回代表Order Details表中欄位的五個位元組。只要第三和第四個欄位被修改，上面這種情況就會發生，它檢測這些位是不是已賦值為1.當第三和第四位都開啟的慶，它就如：00110。L因這個位掩碼代表2次冪，第一位表示1，第二位表示2，第三位表示4，第四位表示8，第五位表示16（是的，這是和正常二進位制數相反的順序）；因此只表示UnitPrice和Quantity欄位被修改位掩碼的值為00110，這個值為12（4+8）。請注意，這個觸發器只有在UnitPrice和Quantity欄位被修改才會將事務回滾。如果其他欄位修改的話，位掩碼就會不一樣，因此就不等於整數12了。如果觸發器被修改為禁止對這二個欄位修改即使對其他欄位也禁止，它就可重新編寫為如下:

ALTER TRIGGER tr_OrderDetails ON [Order Details]

AFTER UPDATE

AS

IF (COLUMNS_UPDATED() >= 12)

BEGIN

RAISERROR ('Cannot change both UnitPrice and Quantity at the same time', 16, 1)

ROLLBACK TRAN

END

GO

　　請注意　COLUMN_UPDATED　功能現在是怎麼去檢測位掩碼的但是否小於等於12.如果你修改聯絡UnitPrice,Quantity和Discount列的話，位掩碼就變為00111，代表整數28（4+8+16）。當在一個表中不止有8個列時，這個函式就會先返回包含了前八列的五個位元組，而第從第九到第十六就會在第二個位元組中，以此類推。這個功能在決定允許哪些列可以被更新比只對第列進行更新的UPDATE功能更有用。

　　如前期所描述的一樣，在潢足特定條件規則條件下，觸發器可以回滾事務，當一個含有回滾的觸發器在SQL指令碼中執行時，整個處理將被取消。因此，被觸發動作修改的的所有資料將由ROLLBACK TRANSACION語句回滾。雖然一個回滾並不阻止觸發執行SQL語句所有在ROLLBACK TRANSACION語句後面的語句都會被執行 。特別是當一個觸發器繼續執行回滾語句後面的語句時，在回滾以後所作的任何修改都不會回滾。發生這種情況是因為當在觸發器中執行了一個ROLLBACK TRANSACTION時，所以有的事務都被取消。因此當一個新的查詢語句被執行時，一個新的不同與以前事務的事務就重新開始。因此，一般情況下，建議不要在ROLLBACK TRANSACTION語句後放置任何語句。

　　像回滾不會自動退出觸發器一樣，它也不會自動產生錯誤。如果必須回滾且必會產生錯誤，PAISERR OR語句應該放在退出觸發器程式碼前，緊跟在回滾後.

結束語

　　在對同一個表的資料所作的修改會激發同樣的INSTEAD OF觸發器，這種觸發器不會遞迴呼叫。因此,如果在Emplyee表中有一個INSTEAD OF觸發器,P這個觸發器是用來更新Employee表的，這並不會發生呼叫同一個INSTEAD OF觸發器。如果允許這種遞旭的話，更新應該被禁止。INSTEAD OF觸發器和AFTER觸發器的另一個不同在於Text,Ntext和Image列可以出現在被更新和刪除的表的觸發器中。這些二進位制列會以如VARCAHAR數值出現在更新和刪除表的觸發器中，這種是可行的，但這並不是他們原始的資料型別。

　　這有一個有用的儲存過程-sp_helptrigger 系統儲存過程-來檢測觸發器。他返回定義在表上的觸發器型別，這個表是傳遞給儲存過程的。用這種方法，你可以看到哪些觸發器和某個表有關聯，什麼操作動觸發這些觸發器和判斷觸發器是AFTER觸發器還是INSTEAD OF觸發器。

　　在最後二欄中，我已經討論了AFTER觸發器和INSTEAD OF觸發器的多個方面。當然，還有許多情形下他們很有用，還有許多使用時機也沒有提出來。當一個觸發器必須查詢其他表的情況下，觸發器就會沒有什麼效率了。在這些情況下，觸發器的效能和觸發動作會受到很大損害。當使用得好時，觸發器是一個很棒的工具，但是必須保證在使用他們之前必須對你的程式作一個全面的測試。

傳送你的問題和評論到 John 的郵箱 mmdata@microsoft.com

　

作者簡介：John Papa 是個棒球愛好者，他將夏季晚上的大部分時候花費在他的二個小女兒，妻子還由他忠誠的狗 Kadi 身上。他已經編寫了幾本關於ADO，XML 和 SQL Server 的書。可常常在象 VSLive 這樣的行業會議上看到他。你可以透過 data@lancelotweb.com 和他聯絡。

　

本文出自 aspx">MSDN Magazine 的 aspx">January 2004 期刊，可透過當地 報攤獲得，或者最好是 aspx">訂閱

http://www.chinaitpower.com/A/image/mttdoc.gif' width=48 height=22 align=absmiddle>本文由 VCKBASE MTT 翻譯