CREATE TRIGGER (Transact-SQL)

發行項
03/07/2014

建立 DML、DDL 或登入觸發程序。觸發程序是一種在資料庫伺服器發生事件時，會自動執行的特殊預存程序。當使用者試圖透過資料操作語言 (DML) 事件來修改資料時，便會執行 DML 觸發程序。 DML 事件包括資料表或檢視的 INSERT、UPDATE 或 DELETE 陳述式。無論資料表的資料列有無受到影響，這些觸發程序皆會在引發有效的事件時引發。如需詳細資訊，請參閱＜DML 觸發程序＞。

DDL 觸發程序會根據各種資料定義語言 (DDL) 事件的不同而執行。這些事件主要是對應到 Transact-SQL CREATE、ALTER 和 DROP 陳述式，以及執行類似 DDL 作業的特定系統預存程序。登入觸發程序會引發來回應使用者工作階段建立時所引發的 LOGON 事件。您可以直接從 Transact-SQL 陳述式建立觸發程序，也可以利用 Microsoft .NET Framework Common Language Runtime (CLR) 所建立之組件的方法來建立觸發程序，再將其上傳到 SQL Server 執行個體。 SQL Server 允許針對任何特定陳述式建立多個觸發程序。

安全性注意事項
觸發程序內的惡意程式碼可能在擴大的權限下執行。如需有關如何減輕這項威脅的詳細資訊，請參閱＜管理觸發程序安全性＞。

適用於：SQL Server (SQL Server 2008 透過目前版本)、Windows Azure SQL 資料庫 (初始版本，透過目前版本)。

主題連結圖示 Transact-SQL 語法慣例

語法

-- SQL Server Syntax 
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ]
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::= 
    assembly_name.class_name.method_name

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Trigger on a LOGON event (Logon Trigger)

CREATE TRIGGER trigger_name 
ON ALL SERVER 
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

語法

-- Windows Azure SQL Database Syntax  
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
 [ WITH <dml_trigger_option> [ ,...n ] ] 
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
  AS { sql_statement  [ ; ] [ ,...n ] [ ; ] > }

<dml_trigger_option> ::= 
        [ EXECUTE AS Clause ] 

-- Windows Azure SQL Database Syntax
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger) 

CREATE TRIGGER trigger_name 
ON { DATABASE } 
 [ WITH <ddl_trigger_option> [ ,...n ] ] 
{ FOR | AFTER } { event_type | event_group } [ ,...n ] 
AS { sql_statement  [ ; ] [ ,...n ]  [ ; ] }

<ddl_trigger_option> ::= 
    [ EXECUTE AS Clause ]

引數

schema_name
這是 DML 觸發程序所屬的結構描述名稱。 DML 觸發程序只會針對其據以建立的資料表或檢視表的結構描述。 DDL 或登入觸發程序不可指定 schema_name。
trigger_name
這是觸發程序的名稱。 trigger_name 必須符合識別碼的規則，不過，trigger_name 開頭不能是 # 或 ##。
table |view
這是執行 DML 觸發程序的資料表或檢視，有時稱為觸發程序資料表或觸發程序檢視。您可以選擇性地指定資料表或檢視的完整名稱。只有 INSTEAD OF 觸發程序可以參考檢視。在本機或全域暫存資料表上，不能定義 DML 觸發程序。
DATABASE
將 DDL 觸發程序的範圍套用在目前資料庫上。如果指定的話，每當目前資料庫中出現 event_type 或 event_group 時，都會引發這個觸發程序。
ALL SERVER

適用於：SQL Server 2008 至 SQL Server 2014。

將 DDL 或登入觸發程序的範圍套用在目前伺服器上。如果指定的話，每當目前伺服器中出現 event_type 或 event_group 時，都會引發這個觸發程序。
WITH ENCRYPTION

適用於：SQL Server 2008 至 SQL Server 2014。

模糊化 CREATE TRIGGER 陳述式的文字。使用 WITH ENCRYPTION 可防止在 SQL Server 複寫中發行這個觸發程序。 CLR 觸發程序不能指定 WITH ENCRYPTION。
EXECUTE AS
指定用來執行這個觸發程序的安全性內容。可讓您控制 SQL Server 執行個體要利用哪個使用者帳戶來驗證觸發程序所參考之任何資料庫物件的權限。

如需詳細資訊，請參閱＜EXECUTE AS 子句 (Transact-SQL)＞。
FOR | AFTER
AFTER 指定只在觸發的 SQL 陳述式指定的所有作業都執行成功時，才引發 DML 觸發程序。所有參考的重疊顯示動作和條件約束檢查也都必須成功之後，才會引發這個觸發程序。

當指定的關鍵字只有 FOR 時，預設值便是 AFTER。

檢視不能定義 AFTER 觸發程序。
INSTEAD OF
指定執行 DML 觸發程序來取代觸發的 SQL 陳述式，因此，會覆寫觸發陳述式的動作。 DDL 或登入觸發程序不能指定 INSTEAD OF。

您最多只能在資料表或檢視上，定義每個 INSERT、UPDATE 或 DELETE 陳述式各一個 INSTEAD OF 觸發程序。不過，您可以定義檢視的檢視，讓每份檢視都有它自己的 INSTEAD OF 觸發程序。

使用 WITH CHECK OPTION 的可更新檢視上不可使用 INSTEAD OF 觸發程序。當 INSTEAD OF 觸發程序加入所指定之可更新檢視 WITH CHECK OPTION 時，SQL Server 會產生錯誤。使用者必須在定義 INSTEAD OF 觸發程序之前，利用 ALTER VIEW 來移除這個選項。
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
指定當試圖針對這份資料表或檢視來執行時，會啟動 DML 觸發程序的資料修改陳述式。至少必須指定一個選項。您可以在觸發程序定義中，依照任何順序來指定這些選項的任何組合。

對於 INSTEAD OF 觸發程序而言，含有指定了重疊顯示動作 ON DELETE 的參考關聯性之資料表不能使用 DELETE 選項。同樣地，含有指定了串聯動作 ON UPDATE 的參考關聯性之資料表也不能使用 UPDATE 選項。
WITH APPEND

適用於：SQL Server 2008 至 SQL Server 2008 R2。

指定應該加入現有類型的其他觸發程序。當使用 INSTEAD OF 觸發程序時，或明確指示 AFTER 觸發程序時，便不能使用 WITH APPEND。只有在為了與舊版相容而指定 FOR，且不含 INSTEAD OF 或 AFTER 時，才能使用 WITH APPEND。如果指定了 EXTERNAL NAME (也就是說，觸發程序是一個 CLR 觸發程序)，便不能指定 WITH APPEND。
event_type
這是在執行之後會引發 DDL 觸發程序的 Transact-SQL 語言事件名稱。對 DDL 觸發程序而言有效的事件會列在＜DDL 事件＞中。
event_group
這是 Transact-SQL 語言事件之預設定義群組的名稱。在執行屬於 event_group 的任何 Transact-SQL 語言事件之後，便會引發 DDL 觸發程序。對 DDL 觸發程序而言有效的事件群組會列在＜DDL 事件群組＞中。

CREATE TRIGGER 執行完成之後，event_group 也會藉著將所涵蓋事件類型加入 sys.trigger_events 目錄檢視，當做巨集來執行。
NOT FOR REPLICATION

適用於：SQL Server 2008 至 SQL Server 2014。

指出當複寫代理程式修改觸發程序所涉及的資料表時，不應執行觸發程序。

sql_statement
這是觸發程序條件和動作。觸發程序準則指定一些其他條件來決定所嘗試的 DML、DDL 或登入事件是否引起執行觸發程序動作。

當嘗試作業時，Transact-SQL 陳述式所指定的觸發程序動作便會生效。

觸發程序可以包括任意數目和任何種類的 Transact-SQL 陳述式，但有例外。如需詳細資訊，請參閱＜備註＞一節。觸發程序的設計，是為了根據資料修改或定義陳述式來檢查或變更資料；它不應將資料傳回給使用者。觸發程序的 Transact-SQL 陳述式通常會包括流程控制語言。

DML 觸發程序會使用 deleted 和 inserted 邏輯 (概念) 資料表。它們的結構類似於定義觸發程序的資料表，也就是使用者動作試圖處理的資料表。 deleted 和 inserted 資料表用來保留使用者動作可能已變更之資料列的舊值或新值。例如，若要擷取 deleted 資料表中的所有值，請使用：

SELECT * FROM deleted;

如需詳細資訊，請參閱＜使用插入或刪除的資料表＞。

DDL 和登入觸發程序會利用 EVENTDATA (Transact-SQL) 函數來擷取觸發事件的相關資訊。如需詳細資訊，請參閱＜使用 EVENTDATA 函數＞。

SQL Server 允許利用資料表或檢視的 INSTEAD OF 觸發程序來更新 text、ntext 或 image 資料行。

重要事項
在未來的 Microsoft SQL Server 版本中，將移除 ntext、text 和 image 等資料類型。請避免在新的開發工作中使用這些資料類型，並規劃修改目前在使用這些資料類型的應用程式。請改用 nvarchar(max)、varchar(max) 和 varbinary(max)。AFTER 和 INSTEAD OF 觸發程序都支援 inserted 和 deleted 資料表中的 varchar(MAX)、nvarchar(MAX) 和 varbinary(MAX) 資料。

在未來的 Microsoft SQL Server 版本中，將移除 ntext、text 和 image 等資料類型。請避免在新的開發工作中使用這些資料類型，並規劃修改目前在使用這些資料類型的應用程式。請改用 nvarchar(max)、varchar(max) 和 varbinary(max)。AFTER 和 INSTEAD OF 觸發程序都支援 inserted 和 deleted 資料表中的 varchar(MAX)、nvarchar(MAX) 和 varbinary(MAX) 資料。

< method_specifier >

適用於：SQL Server 2008 至 SQL Server 2014。

對於 CLR 觸發程序，指定繫結觸發程序的組件方法。此方法不可使用任何引數，且必須傳回空值。 class_name 必須是有效的 SQL Server 識別碼，且必須以類別的形式存在於可以顯示的組件中。如果該類別的名稱符合命名空間規定，利用 '.' 來分隔命名空間的各個部分，您就必須用 [ ] 或 " " 分隔字元來分隔類別名稱。這個類別不能是巢狀類別。

注意

依預設，會關閉 SQL Server 執行 CLR 程式碼的能力。您可以建立、修改和卸除參考 Managed 程式碼模組之資料庫物件，但除非您利用 sp_configure 來啟用 clr enabled Option，否則，便不會在 SQL Server 執行個體中執行這些參考。

備註

DML 觸發程序

DML 觸發程序常會用於執行商務規則與資料完整性。 SQL Server 利用 ALTER TABLE 及 CREATE TABLE 陳述式提供宣告式參考完整性 (DRI)。不過，DRI 並不提供跨資料庫的參考完整性。參考完整性是指資料表主索引鍵和外部索引鍵之間的關聯性規則。若要強制執行參考完整性，請在 ALTER TABLE 和 CREATE TABLE 中，使用 PRIMARY KEY 和 FOREIGN KEY 條件約束。如果觸發程序資料表有條件約束，便會在執行 INSTEAD OF 觸發程序之後，執行 AFTER 觸發程序之前，檢查這些條件約束。如果違反條件約束，便會回復 INSTEAD OF 觸發程序動作，且不會引發 AFTER 觸發程序。

您可以利用 sp_settriggerorder 來指定資料表所要執行的第一個和最後一個 AFTER 觸發程序。一份資料表的每個 INSERT、UPDATE 和 DELETE 作業，都只能指定頭尾各一個 AFTER 觸發程序。如果同一份資料表有其他 AFTER 觸發程序，便會隨機執行它們。

如果 ALTER TRIGGER 陳述式變更第一個或最後一個觸發程序，便會卸除修改的觸發程序所設定的第一個或最後一個屬性，順序值必須使用 sp_settriggerorder 來重設。

只有在觸發的 SQL 陳述式已執行成功之後，才會執行 AFTER 觸發程序。所謂執行成功，包括與已更新或刪除之物件關聯的所有參考串聯動作和條件約束檢查。 AFTER 觸發程序不會以遞迴方式在相同資料表上引發 INSTEAD OF 觸發程序。

如果資料表所定義的 INSTEAD OF 觸發程序是針對通常會再引發 INSTEAD OF 觸發程序的資料表來執行陳述式，這個觸發程序並不稱為遞迴運作。相反地，此時會依照資料表沒有 INSTEAD OF 觸發程序的方式來處理陳述式，且會啟動條件約束作業的鏈結，執行 AFTER 觸發程序。例如，如果觸發程序定義為資料表的 INSTEAD OF INSERT 觸發程序，且這個觸發程序會在相同的資料表上執行 INSERT 陳述式，INSTEAD OF 觸發程序所執行的 INSERT 陳述式便不會重新呼叫這個觸發程序。觸發程序所執行的 INSERT 會啟動執行條件約束動作及引發定義給資料表之任何 AFTER INSERT 觸發程序的程序。

如果檢視所定義的 INSTEAD OF 觸發程序是針對通常會再引發 INSTEAD OF 觸發程序的檢視來執行陳述式，它並不稱為遞迴運作。相反地，陳述式會解析成針對檢視下的基底資料表來進行的修改。在這個情況下，檢視定義必須符合可更新之檢視的所有限制。如需可更新之檢視的定義，請參閱＜透過檢視修改資料＞。

例如，如果觸發程序定義為檢視的 INSTEAD OF UPDATE 觸發程序，且這個觸發程序會執行參考相同檢視的 UPDATE 陳述式，INSTEAD OF 觸發程序所執行的 UPDATE 陳述式便不會重新呼叫這個觸發程序。觸發程序所執行的 UPDATE 是依照檢視沒有 INSTEAD OF 觸發程序的方式，針對檢視來處理的。 UPDATE 所變更的資料行必須解析成單一基底資料表。基礎基底資料表的每項修改都會啟動套用條件約束及引發定義給資料表之 AFTER 觸發程序的鏈結。

測試特定資料行的 UPDATE 或 INSERT 動作

您可以設計一個 Transact-SQL 觸發程序來執行以特定資料行之 UPDATE 或 INSERT 修改為基礎的特定動作。請在觸發程序的主體中，利用 UPDATE() 或 COLUMNS_UPDATED 來完成這個目的。 UPDATE() 會測試單一資料行所嘗試的 UPDATE 或 INSERT。 COLUMNS_UPDATED 會測試多個資料行所執行的 UPDATE 或 INSERT 動作，且會傳回一個位元模式來指出插入或更新了哪些資料行。

觸發程序限制

CREATE TRIGGER 必須是批次中的第一個陳述式，且只能套用至一份資料表。

觸發程序只會建立在目前資料庫中；不過，觸發程序可以參考在目前資料庫之外的物件。

如果指定觸發程序結構描述名稱來限定觸發程序，請依照相同方式來限定資料表名稱。

您可以在相同 CREATE TRIGGER 陳述式中，定義多個使用者動作 (如 INSERT 和 UPDATE) 的相同觸發程序動作。

您不能在外部索引鍵定義了 DELETE/UPDATE 動作串聯的資料表上，定義 INSTEAD OF DELETE/UPDATE 觸發程序。

您可以在觸發程序內指定任何 SET 陳述式。在觸發程序的執行期間，所選取的 SET 選項會持續有效，之後，會還原為先前的設定。

當引發觸發程序時，如同預存程序一樣，結果會傳回發出呼叫的應用程式。若要防止結果因為引發觸發程序而傳回應用程式，請勿併入會傳回結果的 SELECT 陳述式或是會在觸發程序中執行變數指派的陳述式。包括將結果傳回給使用者的 SELECT 陳述式或執行變數指派的陳述式之觸發程序，需要特殊處理；這些傳回的結果必須寫入允許修改觸發程序資料表的每個應用程式中。如果必須在觸發程序內發生變數指派，請在觸發程序開頭使用 SET NOCOUNT 陳述式，以防止傳回任何結果集。

雖然 TRUNCATE TABLE 陳述式實際上是 DELETE 陳述式，但是它並不會啟動觸發程序，因為此作業不會記錄個別的資料列刪除。但是，只有具有執行 TRUNCATE TABLE 陳述式之權限的使用者才需要擔心這種方式會不小心阻止 DELETE 觸發程序。

WRITETEXT 陳述式，不論有沒有記錄，都不會啟動觸發程序。

DML 觸發程序中不可使用下列 Transact-SQL 陳述式：

ALTER DATABASE	CREATE DATABASE	DROP DATABASE
RESTORE DATABASE	RESTORE LOG	RECONFIGURE

除此之外，當 DML 觸發程序用於觸發動作的目標資料表或檢視表時，不可在 DML 觸發程序的主體內使用下列 Transact-SQL 陳述式。

CREATE INDEX (包括 CREATE SPATIAL INDEX 和 CREATE XML INDEX)

ALTER INDEX

DROP INDEX

DBCC DBREINDEX

ALTER PARTITION FUNCTION

DROP TABLE

用來執行下列動作的 ALTER TABLE：

新增、修改或卸除資料行。
切換資料分割。
加入或卸除 PRIMARY KEY 或 UNIQUE 條件約束。

注意

由於 SQL Server 不支援系統資料表的使用者自訂觸發程序，因此，我們建議您不要在系統資料表上建立使用者自訂觸發程序。

DDL 觸發程序

如同標準觸發程序，DDL 觸發程序也會執行預存程序來回應事件。但它們不像標準觸發程序，並不會為了回應資料表或檢視的 UPDATE、INSERT 或 DELETE 陳述式而執行。相反地，執行它們主要是為了回應資料定義語言 (DDL) 陳述式。其中包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 陳述式。執行類似 DDL 作業的某些系統預存程序也可能引發 DDL 觸發程序。

重要事項
請測試 DDL 觸發程序，以判斷它們對執行之系統預存程序的回應。例如，CREATE TYPE 陳述式與 sp_addtype 和 sp_rename 預存程序都會引發在 CREATE_TYPE 事件上建立的 DDL 觸發程序。

如需有關 DDL 觸發程序的詳細資訊，請參閱＜DDL 觸發程序＞。

對於影響區域或全域暫存資料表與預存程序的事件，DDL 觸發程序不會為了回應它而引發。

DDL 觸發程序不像 DML 觸發程序，並不以結構描述為範圍。因此，OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 等函數無法用來查詢有關 DDL 觸發程序的中繼資料。請改用目錄檢視。如需詳細資訊，請參閱＜取得 DDL 觸發程序的詳細資訊＞。

注意

以伺服器為範圍的 DDL 觸發程序出現在 SQL Server Management Studio 物件總管的 [觸發程序] 資料夾中。這個資料夾在 [伺服器物件] 資料夾之下。以資料庫為範圍的 DDL 觸發程序出現在 [資料庫觸發程序] 資料夾中。這個資料夾在對應資料庫的 [可程式性] 資料夾之下。

登入觸發程序

登入觸發程序會執行預存程序來回應 LOGON 事件。當 SQL Server 執行個體建立使用者工作階段時，就會引發這個事件。登入觸發程序會在登入驗證階段結束之後，但在使用者工作階段實際建立之前引發。因此，從觸發程序內產生且一般會顯示給使用者的所有訊息，例如錯誤訊息和來自 PRINT 陳述式的訊息，都會轉至 SQL Server 錯誤記錄檔。如需詳細資訊，請參閱＜登入觸發程序＞。

如果驗證失敗，登入觸發程序就不會引發。

登入觸發程序不支援分散式交易。若引發含有分散式交易的登入觸發程序，會傳回錯誤 3969。

停用登入觸發程序

登入觸發程序可以有效地防止所有使用者成功連接到 Database Engine，包括 sysadmin 固定伺服器角色的成員。當登入觸發程序防止連接時，sysadmin 固定伺服器角色的成員可以使用專用管理員連接或以最低組態模式 (-f) 啟動 Database Engine，藉以進行連接。如需詳細資訊，請參閱＜Database Engine 服務啟動選項＞。

一般觸發程序考量

傳回結果

SQL Server 的未來版本將移除從觸發程序傳回結果的功能。傳回結果集的觸發程序可能會導致非專用的應用程式發生非預期的行為。請避免從新開發工作中的觸發程序傳回結果集，並計畫修改目前採用這種作法的應用程式。若要避免觸發程序傳回結果集，請將 disallow results from triggers 選項設為 1。

登入觸發程序不允許傳回結果集，而且您無法設定這項行為。如果登入觸發程序產生結果集，此觸發程序將無法執行，而引發觸發程序的登入嘗試將被拒絕。

多重觸發程序

SQL Server 允許每個 DML、DDL 或 LOGON 事件各建立多個觸發程序。例如，如果針對已有 UPDATE 觸發程序的資料表來執行 CREATE TRIGGER FOR UPDATE，便會建立其他更新觸發程序。在舊版的 SQL Server 中，每份資料表的每個 INSERT、UPDATE 或 DELETE 資料修改事件都只能有一個觸發程序。

遞迴觸發程序

當利用 ALTER DATABASE 來啟用 RECURSIVE_TRIGGERS 設定時，SQL Server 也允許觸發程序的遞迴叫用。

遞迴觸發程序有可能產生下列遞迴類型：

間接遞迴

當進行間接遞迴時，應用程式會更新 T1 資料表。這會引發 TR1 觸發程序，進而更新 T2 資料表。在這個狀況下，T2 觸發程序後來又會引發和更新 T1 資料表。
直接遞迴

當進行直接遞迴時，應用程式會更新 T1 資料表。這會引發 TR1 觸發程序，進而更新 T1 資料表。由於更新了 T1 資料表，因此，又會引發 TR1 觸發程序，依此類推。

下列範例會使用間接和直接觸發程序遞迴。它假設 T1 資料表定義了 TR1 和 TR2 這兩個更新觸發程序。 TR1 觸發程序會遞迴更新 T1 資料表。 UPDATE 陳述式會執行每個 TR1 和 TR2 各一次。另外，執行 TR1 會觸發執行 TR1 (遞迴) 和 TR2。特定觸發程序的 inserted 和 deleted 資料表包含只對應於呼叫觸發程序之 UPDATE 陳述式的資料列。

注意

只有在利用 ALTER DATABASE 來啟用 RECURSIVE_TRIGGERS 設定時，才會發生上述行為。定義給特定事件的多個觸發程序並沒有任何定義的執行順序。每個觸發程序都應該是獨立自足的。

停用 RECURSIVE_TRIGGERS 設定只會防止直接遞迴。若要停用間接遞迴，請使用 sp_configure 將 nested triggers 伺服器選項設為 0。

如果有任何觸發程序執行 ROLLBACK TRANSACTION，不論巢狀層級為何，都不會執行任何觸發程序。

巢狀觸發程序

觸發程序的巢狀結構，最多可達 32 層。如果觸發程序變更了一份資料表，且這份資料表有另一個觸發程序，就會啟動第二個觸發程序，之後這個觸發程序可能會呼叫第三個觸發程序，依此類推。如果鏈結中的任何觸發程序造成無限迴圈，就會超出巢狀層級，並取消觸發程序。當 Transact-SQL 觸發程序參考 CLR 常式、類型或彙總來執行 Managed 程式碼時，在 32 層的巢狀限制中，這項參考算是一層。從 Managed 程式碼內叫用的方法，不列入這項限制。

若要停用巢狀觸發程序，請將 sp_configure 的 nested triggers 選項設為 0 (關閉)。預設組態接受巢狀觸發程序。如果關閉了 nested triggers，recursive triggers 也會停用，不論使用 ALTER DATABASE 設定的 RECURSIVE_TRIGGERS 設定為何，都是如此。

即使 nested triggers 伺服器組態選項設為 0，INSTEAD OF 觸發程序內部的第一個巢狀 AFTER 觸發程序仍會引發。不過，在此設定下，後續的 AFTER 觸發程序不會引發。建議您檢閱應用程式中是否有巢狀觸發程序，以判斷當 nested triggers 伺服器組態選項設定為 0 時，這些應用程式的新行為是否符合您的商務規則，然後進行適當的修改。

延遲名稱解析

SQL Server 允許 Transact-SQL 預存程序、觸發程序和批次參考在編譯階段並不存在的資料表。這項功能稱為延遲名稱解析。

權限

若要建立 DML 觸發程序，需要建立觸發程序的資料表或檢視的 ALTER 權限。

若要建立伺服器範圍 (ON ALL SERVER) 的 DDL 觸發程序或登入觸發程序，需要伺服器的 CONTROL SERVER 權限。若要建立資料庫範圍 (ON DATABASE) 的 DDL 觸發程序，需要目前資料庫的 ALTER ANY DATABASE DDL TRIGGER 權限。

範例

A.使用包含提示訊息的 DML 觸發程序

當任何人嘗試在 AdventureWorks2012 資料庫的 Customer 資料表中新增或變更資料時，以下 DML 觸發程序會向用戶端列印一則訊息。

IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
   DROP TRIGGER Sales.reminder1;
GO
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B.使用包含提示電子郵件訊息的 DML 觸發程序

當 Customer 資料表有了改變時，以下範例會向指定的人 (MaryM) 傳送一則電子郵件訊息。

IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2012 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

C.使用 DML AFTER 觸發程序在 PurchaseOrderHeader 與 Vendor 資料表之間執行商務規則

由於 CHECK 條件約束只能參考定義了資料行層級或資料表層級條件約束的資料行，任何跨資料表的條件約束 (這裡是商務規則) 都必須定義成觸發程序。

以下範例會在 AdventureWorks2012 資料庫中建立 DML 觸發程序。當試圖在 PurchaseOrderHeader 資料表中插入新的採購單時，這個觸發程序會檢查確認供應商的信用等級良好。為了取得供應商的信用等級，必須參考 Vendor 資料表。如果信用等級太低，將會顯示訊息，且不會執行插入動作。

IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader AS p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.BusinessEntityID = p.VendorID
           WHERE v.CreditRating = 5
          )
BEGIN
RAISERROR ('A vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;
GO

-- This statement attempts to insert a row into the PurchaseOrderHeader table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES (
2
,3
,261
,1652
,4
,GETDATE()
,GETDATE()
,44594.55
,3567.564
,1114.8638 );
GO

D.使用僅限於資料庫的 DDL 觸發程序

以下範例利用 DDL 觸發程序避免卸除資料庫中的同義字。

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'safety')
    DROP TRIGGER safety
         ON DATABASE;
GO
CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_SYNONYM
AS 
   RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
   ROLLBACK
GO
DROP TRIGGER safety
ON DATABASE;
GO

E.使用僅限於伺服器的 DDL 觸發程序

以下範例會在目前伺服器執行個體出現任何 CREATE DATABASE 事件時，利用 DDL 觸發程序來列印訊息，並利用 EVENTDATA 函數來擷取對應 Transact-SQL 陳述式的文字。如需更多在 DDL 觸發程序中使用 EVENTDATA 的範例，請參閱＜使用 EVENTDATA 函數＞。

適用於：SQL Server 2008 至 SQL Server 2014。

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

F.使用登入觸發程序

在以下登入觸發程序範例中，如果已有三個使用者工作階段以 login_test 登入執行，會拒絕以該登入的成員身分嘗試登入 SQL Server。

適用於：SQL Server 2008 至 SQL Server 2014。

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

G.檢視引發觸發程序的事件

以下範例會查詢 sys.triggers 及 sys.trigger_events 目錄檢視，判別引發 safety 觸發程序的 Transact-SQL 語言事件。 safety 是在前一個範例中建立。

SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T ON T.object_id = TE.object_id
WHERE T.parent_class = 0 AND T.name = 'safety';
GO

請參閱

參考

ALTER TABLE (Transact-SQL)

ALTER TRIGGER (Transact-SQL)

COLUMNS_UPDATED (Transact-SQL)

CREATE TABLE (SQL Server)

DROP TRIGGER (Transact-SQL)

ENABLE TRIGGER (Transact-SQL)

DISABLE TRIGGER (Transact-SQL)

TRIGGER_NESTLEVEL (Transact-SQL)

EVENTDATA (Transact-SQL)

sys.dm_sql_referenced_entities (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

sys.sql_expression_dependencies (Transact-SQL)

sp_help (Transact-SQL)

sp_helptrigger (Transact-SQL)

sp_helptext (Transact-SQL)

sp_rename (Transact-SQL)

sp_settriggerorder (Transact-SQL)

UPDATE() (Transact-SQL)

sys.triggers (Transact-SQL)

sys.trigger_events (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.assembly_modules (Transact-SQL)

sys.server_triggers (Transact-SQL)

sys.server_trigger_events (Transact-SQL)

sys.server_sql_modules (Transact-SQL)

sys.server_assembly_modules (Transact-SQL)

概念

取得關於 DML 觸發程序的詳細資訊

取得 DDL 觸發程序的詳細資訊

共用方式為