觸發程序

觸發程序（trigger） 是一種特殊的預存程序，執行特定的陳述式（UPDATE、INSERT 或 DELETE）就可以啓動觸發程序。觸發程序與其他預存程序相同，可以是由簡單，亦或是複雜的 T-SQL 陳述式組成；至於與其他預存程序不同的地方，則在於當指定的資料被修改，觸發程序即自動執行，無法依名稱以手動執行。觸發程序執行時，稱為觸動（fire）。觸發程序雖建立在現有的資料庫資料表中，但它可以存取其他資料庫的資料表和物件。觸發程序不能建立在臨時的資料表或臨時的系統資料表上，只能建立在使用者自訂資料表或自訂的檢視表中。執行觸發程序所在的資料表或檢視表，稱為觸發程序資料表（trigger table）。

觸發程序有五種類型：UPDATE、INSERT、DELETE、INSTEAD OF 和 AFTER。有了觸發程序，只要您對該表格更新、插入或刪除時，就會觸動對應的 UPDATE、INSERT 或 DELETE 觸發程序。INSTEAD OF 和 AFTER 是 SQL 2000 新增的兩項觸發程序，Instead of的原義是“取代”，INSTEAD OF觸發程序會取代插入、更新和刪除操作而執行。AFTER 觸發程序會在觸發動作之後再觸動，可視為控制觸發程序啓動時間的機制。

對資料的更新、插入及刪除被視為資料修改事件。您可以設計當一項或多項修改事件產生時，即觸動觸發程序。例如，當執行 UPDATE 或 INSERT 陳述式時即觸動觸發程序。這種類型的觸發程序稱為 UPDATE/INSERT 觸發程序。您也可以建立任何一項修改事件產生時，執行相對的 UPDATE/INSERT/DELETE 觸發程序。

下面是關於觸發程序的一些其他規定：

觸發程序只在觸發它的陳述式完成後執行。舉例來説，如果 UPDATE 陳述式成功，UPDATE 觸發程序才會被觸動。

如果陳述式在資料表中執行違反條件約束或引起錯誤，觸發程序不會觸動。

觸發程序視為單一交易中的一部份，因此可以由原觸發程序復原交易，如果在交易過程中偵測到嚴重的錯誤（如使用者中斷連線），則會自動復原整個交易。

當觸發程序觸動，若產生任何結果，就會如預存程序一樣，將結果傳回其呼叫的應用程式。一般來説，INSERT、UPDATE 或 DELETE 的陳述式（觸動觸發程序的陳述式）不會將結果傳回；結果通常由 SELECT 查詢傳回。因此，為了避免觸發程序傳回結果給應用程式，請勿在觸發程序定義中引入 SELECT 陳述式或指派變數。如果希望從觸發程序中傳回結果，在允許修改觸發資料表的每個應用程式中都必須撰寫特殊的程式，才能使應用程式收到傳回的資料並進行正確的處理。

如果您必須在觸發程序中指派變數，則可在觸發程序的起始位置使用 SET NOCOUNT ON 陳述式以防止傳回任何結果資料列。SET NOCOUNT 陳述式指定是否傳回查詢或受陳述式影響的資料列數目的資訊（例如，影響 23 個資料列）。SET NOCOUNT 的預設值是設在 OFF，也就是説會傳回受影響列的訊息。該設定並不影響 SELECT 陳述式實際結果的傳回，只傳回計數。

使用觸發程序程序的好處是SQL Server會使用觸發程序以自動執行該觸發程序所定義的命令，這使得數據庫功能性更為強大。因此只要您事先定義觸發程序，您不需要擔心數據庫客户端（這包含了使用者或應用程序）是否瞭解所有的資料依存性和商業規則。

舉例來説，一位業務員已經規劃了一個訂單總計等於或大於$10,000時，就必須要進行信用檢查的規則。假如CreditApproved是指Customer資料表內的一個資料行，而現在有一筆訂單的相關資料要新增至Order資料表中，此時您無法使用CHECK條件約束來執行此規則。但是更令人煩惱的是您要在數據庫客户端強制這些規則更為困難，於是觸發程序就可以提供在數據庫本身進行建置此規則的機制。

觸發程序也可以用於避免客户端變更數據庫規則。繼續前一個範例，假如將商業規則更改為金額大於或等於$15,000時才需要信用驗證時，這個新的規則可以在數據庫層級建置，而您不需要檢查和更新所有客户端的應用程序。

觸發程序最典型的使用方式是－商業規則無法使用資料表條件約束強制建置時，就使用觸發程序。觸發程序很早就已經需要串聯參考完整性（cascading referential integrity）的功能，但是直到SQL Server 2000這個版本才支持。然而，現在我們可以在資料表層級使用觸發程序建置串聯參考完整性，就像剛才的範例一樣。大多數的商業規則會涉及多個資料表－例如計算總和需要使用到其它的資料表，這通常藉由觸發程序會比較容易建置。

提示

縱使觸發程序的執行效能並不是問題的重點所在（當它和資料指針一起使用時），您應該儘可能的使用較低階的方式建置規則。假如您的商業規則可以利用CHECK條件約束建置時，您就不應該使用觸發程序；而如果您可以使用UNIQUE條件約束時，您就不應該使用CHECK條件約束。

SQL Server 2000支持了二種不同的觸發程序型態：AFTER觸發程序和INSTEAD OF觸發程序。觸發的情況分別如下：AFTER觸發程序將會在其所定義的命令執行後被觸發；INSTEAD OF觸發程序將會代替其所定義的命令被觸發。

您可以針對INSERT、UPDATE和DELETE命令建立AFTER觸發程序。AFTER觸發程序只能在資料表上建立，而不能在檢視表上建立。但是您可以針對這三個命令分別建立多個觸發程序，相對地，單一的觸發程序也可以套用這三個命令的任意組合。

提示

假如您針對單一個命令建立多個觸發程序時，您可以使用系統預存程序sp_settriggerorder來設定該命令執行時的第一個和最後一個觸發程序。

AFTER觸發程序會在所有的條件約束被處理之後再觸發，假如有違反條件約束的情況發生時，該AFTER觸發程序將不會被觸發。

舉例來説，假如在資料表中試圖新增資料列而導致違反PRIMARY KEY條件約束時，該INSERT陳述式會在呼叫觸發程序之前就發生執行失敗。

INSTEAD OF觸發程序會取代其所定義的命令被觸發。就像AFTER觸發程序一樣，您可以在INSERT、UPDATE或DELETE命令中定義INSTEAD OF觸發程序。單一的觸發程序可以套用多重命令的任意組合。

與AFTER觸發程序不同的是，您可以針對資料表和檢視表來建立INSTEAD OF觸發程序，但是在資料表或檢視表上的每一個動作，您只能建立單一個INSTEAD OF觸發程序。

INSTEAD OF觸發程序不兼容於串聯參考完整性。INSTEAD OF DELETE與INSTEAD OF UPDATE觸發程序不能定義於以DELETE或UPDATE動作定義外部索引鍵的資料表。

因為INSTEAD OF觸發程序可以宣告在檢視表中，所以它們非常地適用於建置檢視表的功能性。舉例來説，針對一個包含有GROUP BY子句的檢視表來説，SQL Server會防止使用INSERT陳述式，但是卻允許您針對該檢視表定義一個INSTEAD OF INSERT觸發程序。您可以使用該觸發程序以將資料列插入定義檢視表的底層資料表中。

就本質上而言，是沒有BEFORE觸發程序的。但是INSTEAD OF觸發程序可以代替其所定義的命令執行。如果沒有INSTEAD OF觸發程序存在，則該命令就會真的執行。

舉例來説，假如您想要在INSERT之前檢查某些條件時，您可以宣告一個INSTEAD OF INSERT觸發程序。該INSTEAD OF觸發程序將執行此檢查，然後在資料表中執行INSERT。該INSERT陳述式將會正常地執行，而不需要遞歸地呼叫INSTEAD OF觸發程序。

SQL Server使用觸發程序來處理一些條件約束。您無法使用觸發程序CREATE、ALTER或DROP數據庫、您無法使用觸發程序將數據庫或交易記錄檔案進行還原、並且您無法使用觸發程序進行更改SQL Server設定的操作（您可以參考“SQL Server線上叢書”所提供的完整説明）。

假如您在觸發程序中更改數據庫選項時，您所更改的選項只會在觸發程序執行期間有效，但是在觸發程序執行完畢之後，您所更改的值將會恢復到原來的值。

理論上，您可以使用RETURN陳述式自觸發程序中傳回一個值，但是您不應該依賴客户端應用程序來探知該觸發程序是否存在或該觸發程序作了什麼。RAISERROR命令提供了一個比較好的技術，因為大多數的應用程序的設計會掌控這些錯誤。

使用CREATE TRIGGER命令

就像其它的數據庫對象一樣，您可以使用CREATE陳述式的形式建立一個觸發程序。CREATE陳述式的基本語法為：

CREATE TRIGGER trigger_name

ON table_or_view

trigger_type command_list

AS

SQL_statements

其中trigger_name必須要遵守唯一性的規則，假如trigger_type是INSTEAD OF時，table_or_view可以是檢視表名稱，因為您只可以在檢視表中定義INSTEAD OF觸發程序。觸發程序不能建立在暫存資料表或系統資料表中，但是它們可以引用暫存資料表。

trigger_type關鍵詞只能是AFTER、FOR或INSTEAD OF其中之一；command_list可以聯合使用任何INSERT、UPDATE或DELETE命令，假如您要使用一個以上的命令時，您可以使用逗號“，”將這些命令區隔開來。

提示

早期的SQL Server版本僅支持AFTER觸發程序和trigger_type FOR語法，此語法在SQL Server 2000中也有支持，但是它與AFTER所表示的意思是相同的。

您可將觸發程序儲存至數據庫中。在儲存全新或更新的觸發程序時，觸發程序會加入 [資料表] 資料夾中。

若要儲存觸發程序

在 [檔案] 菜單中，按一下 [儲存 <觸發程序名稱>]。

如果更新現存觸發程序，則會出現消息框，提示您確認是否要進行儲存動作。請選擇 [是]。

顯示於服務器總管內 [資料表] 資料夾中的已儲存觸發程序，位於其所屬的資料表下。

您可開啓觸發程序，來檢視或編輯數據庫中現存觸發程序的文字。觸發程序在 Transact-SQL for Microsoft ® SQL Server™ 數據庫或 PL/SQL for Oracle 數據庫中編寫指令碼。

在服務器總管中，觸發程序與數據庫資料表資料行是以觸發程序圖標來區分。

若要開啓觸發程序

在服務器總管內，展開 [資料表] 資料夾。

將準備開啓的觸發程序所在的資料表展開。

在準備開啓的觸發程序名稱上按一下鼠標右鍵，並於快速鍵菜單上選擇 [編輯觸發程序]。

- 或 -

連按兩下您要開啓的觸發程序名稱。

觸發程序在可用以編輯 SQL 陳述式的原始程序代碼編輯器中開啓。

您可刪除數據庫中不再需要，或者會執行不必要動作的觸發程序。例如，如果您使用數據庫圖表來設計數據庫，則會使用關聯性來實行參考完整性，而非使用觸發程序。如果觸發程序重複了數據庫圖表內的關聯性，則應該刪除觸發程序或者關聯性。如需刪除關聯性的詳細信息，請參閲刪除關聯性。

若要刪除觸發程序

在服務器總管內，展開 [資料表] 資料夾。

將準備刪除的觸發程序所在的資料表展開。

在準備刪除的觸發程序上，按一下鼠標右鍵，並於快速鍵菜單上選擇 [刪除]。

會出現訊息提示您確認是否刪除。請選擇 [是]。

觸發程序自數據庫與服務器總管中刪除。

觸發程序適用於下列方式：

觸發程序是自動的。觸發程序在資料表內的資料經過修改 (例如手動輸入或應用程序動作) 後，會立即激活。

觸發程序可透過數據庫的關聯資料表串聯 (Cascade) 變更。例如，您可以在 titles 資料表的 title_id 資料行中，寫入某個刪除觸發程序，以便刪除其它資料表中相符的資料列。觸發程序使用 title_id 資料行做為唯一鍵，以便從 titleauthor、sales 和 roysched 資料表中找出符合的資料列。

注意在 SQL Server 2000 環境下，也可以透過關聯資料表串聯變更；方法是在外部索引鍵條件約束中設定 CASCADE UPDATE 或 DELETE (或同時設定兩種) 條件約束。

觸發程序可實行檢查條件約束中所定義更為複雜的限制。觸發程序與檢查條件約束不同，前者可以參考其它資料表內的資料行。例如，觸發程序可以將嘗試套用折扣的更新項目 (儲存於 discounts 資料表) 復原為書籍 (儲存於 titles 資料表)，其價格會低於 $10。

如需使用條件約束的詳細信息，請參閲下列主題：

至 請參閲

建立新觸發程序 建立觸發程序

開啓現存觸發程序 開啓觸發程序

將觸發程序儲存至您的項目中 儲存觸發程序

使用 INSTEAD OF 觸發程序 在檢視中使用 INSTEAD OF 觸發程序

刪除預存觸發程序 刪除觸發程序

如需詳細信息以及觸發程序的範例，請參閲您的數據庫服務器文件。如果您使用 Microsoft SQL Server，請參閲《SQL Server 線上叢書》的部份。

觸發程序和條件約束相同，可用來維持資料的完整性和商業規則，但是觸發程序不能取代條件約束。例如，您不需要建立觸發程序來檢查資料表中主索引鍵中的某個值是否存在，才能決定這個值是否能被插入到另一個資料表中的相對應資料行（外部索引鍵條件約束在這種情況下才是一個較好的選擇）。您應當建立一個觸發程序來啓動資料庫中所有相關資料表的串聯變更，例如，您可能在 pubs 資料庫中titles這個資料表中的title_id資料行上建立 DELETE 觸發程序，當您在titles欄位中刪除一筆資料時，在sales、roysched和titleauthor資料表中對應資料的資料行也會被刪除（在接下來的章節我們將看到如何建立該 DELETE 觸發程序）。

您還可以利用觸發程序執行比 CHECK 條件約束更復雜的資料檢測（CHECK 條件約束在 第十六章 有詳細討論）。由於觸發程序可以引用其他資料表中的資料行，因此才可能執行復雜的資料檢測；反之，CHECK 條件約束只限於在其所定義的資料表上執行。

您還可以建立多重觸發程序，當資料修改時即觸動所有觸發程序。（請記住，如果在資料表或檢視表中為一個事件定義多重觸發程序，每個觸發程序都必須有一個自己的名稱）。

或者您可以建立單一觸發程序，在資料修改時即被觸動。也就是每一次當被定義的事件發生，觸發程序就被觸動一次。因此，若是在資料表上定義 INSERT、UPDATE 和 DELETE 的觸發程序，每次定義的事件產生時，觸發程序就會觸動。

在建立觸發程序時，SQL Server 會為觸發程序建立兩個暫時資料表，您可以參考這兩個資料表，用 T-SQL 撰寫觸發程序定義。這些資料表固定儲存在與觸發程序一起的記憶體中，每個觸發程序只能存取自己的暫時資料表，暫時資料表即為觸發程序所在資料表的一個副本。您可以使用這些資料表比較資料修改前後狀態。下一節將列舉這些特殊資料表（稱為deleted和inserted資料表）。