CREATE TRIGGER （Transact-SQL）

適用於：SQL Server Azure SQL Database Azure SQL Managed Instance Microsoft Fabric 中的 SQL 資料庫

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

DDL 觸發程式會執行，以回應各種資料定義語言（DDL）事件。這些事件主要對應至 Transact-SQL CREATE、 ALTER、和 DROP 語句，以及執行類似 DDL 作業的特定系統預存程式。

登入觸發程式會引發，以回應 LOGON 建立使用者的會話時所引發的事件。您可以直接從 Transact-SQL 陳述式建立觸發程序，也可以使用 Microsoft .NET Framework 通用語言執行平台 (CLR) 所建立的組件方法來建立觸發程序，並將其上傳到 SQL Server 執行個體。 SQL Server 可讓您針對任何特定陳述式建立多個觸發程序。

重要

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

注意

本文會探討如何將 .NET Framework CLR 整合至 SQL Server。 CLR 整合不適用於 Microsoft Fabric 中的 Azure SQL 資料庫或 SQL 資料庫。

Transact-SQL 語法慣例

語法

SQL Server 語法

對資料表或檢視表的 INSERT、 UPDATE或 DELETE 語句觸發程式（DML 觸發程式）：

CREATE [ OR ALTER ] 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

對資料表的 INSERT、 UPDATE或 DELETE 語句觸發程式（記憶體優化數據表上的 DML 觸發程式）：

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

<dml_trigger_option> ::=
    [ NATIVE_COMPILATION ]
    [ SCHEMABINDING ]
    [ EXECUTE AS Clause ]

、CREATEALTER、DROP、 GRANTDENY或 REVOKE 語句上的UPDATE觸發程式（DDL 觸發程式）：

CREATE [ OR ALTER ] 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 ]

事件觸發 LOGON 程式（登入觸發程式）：

CREATE [ OR ALTER ] 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 ]

Azure SQL 資料庫或 Fabric 語法中的 SQL 資料庫

對資料表或檢視表的 INSERT、 UPDATE或 DELETE 語句觸發程式（DML 觸發程式）：

CREATE [ OR ALTER ] 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 ]

、CREATEALTER、DROP、 GRANTDENY或 REVOKE 語句上的UPDATE STATISTICS觸發程式（DDL 觸發程式）：

CREATE [ OR ALTER ] 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 ]

引數

或改變

適用於：SQL Server 2016（13.x）SP1 及更新版本、Azure SQL 資料庫，以及 Microsoft Fabric 中的 SQL 資料庫。

只有在觸發程序已存在時，才能有條件地更改它。

schema_name

DML 觸發程序所屬的結構描述名稱。 DML 觸發程序範圍僅限於其建立所在之資料表或檢視表的結構描述。您不能為 DDL 或登入觸發程序指定 schema_name。

trigger_name

觸發程序的名稱。 trigger_name必須遵循標識符的規則，不同之處在於trigger_name無法以 # 或 ##開頭。

桌子 | 視圖

執行 DML 觸發程序的資料表或檢視表。這個資料表或檢視表有時也稱為觸發程序資料表或觸發程序檢視表。您可以選擇性地指定資料表或檢視的完整名稱。您只能藉由 INSTEAD OF 觸發程式參考檢視。您不能在本機或全域暫存資料表上定義 DML 觸發程序。

資料庫

將 DDL 觸發程序的範圍套用在目前資料庫上。若有指定，每當目前資料庫中出現 event_type 或 event_group 時，都會引發這個觸發程序。

所有伺服器

將 DDL 或登入觸發程序的範圍套用在目前伺服器上。若有指定，每當目前伺服器中的任何位置出現 event_type 或 event_group 時，都會引發這個觸發程序。

使用加密

遮蔽語句的 CREATE TRIGGER 文字。使用 WITH ENCRYPTION 可防止觸發程式發佈為 SQL Server 複寫的一部分。 WITH ENCRYPTION 無法為 CLR 觸發程式指定。

以指定身分執行

指定用來執行這個觸發程序的安全性內容。可讓您控制 SQL Server 執行個體要利用哪個使用者帳戶來驗證觸發程序所參考任何資料庫物件的權限。

如果觸發程序是在經記憶體最佳化的資料表上，則這個選項為必要。

如需詳細資訊，請參閱 EXECUTE AS 子句。

NATIVE_COMPILATION

表示觸發程序是原生編譯的。

如果觸發程序是在經記憶體最佳化的資料表上，則這個選項為必要。

SCHEMABINDING

確保使用者無法卸除或改變觸發程序所參考的資料表。

如果觸發程序是在經記憶體最佳化的資料表上，則這個選項為必要，且其不支援傳統資料表上的觸發程序。

FOR |後

FOR 或 AFTER 指定只有在觸發 SQL 語句中指定的所有作業都成功啟動時，才會引發 DML 觸發程式。所有參考的串聯動作和條件約束檢查也都必須成功之後，才會引發這個觸發程序。

您無法在檢視上定義 AFTER 觸發程式。

而不是

指定要啟動 DML 觸發程序，而「不是」觸發 SQL 陳述式，因此會覆寫觸發陳述式的動作。您無法 INSTEAD OF 指定 DDL 或登入觸發程式。

您最多可以在資料表或檢視表上為每個、或 INSTEAD OF 語句定義一個INSERT觸發UPDATE程式。DELETE 您也可以定義檢視的檢視，其中每個檢視都有自己的 INSTEAD OF 觸發程式。

您無法在使用 INSTEAD OF的可更新檢視上定義WITH CHECK OPTION觸發程式。這麼做會導致觸發程式新增至指定的可更新檢視INSTEAD OF時WITH CHECK OPTION發生錯誤。您可以在定義ALTER VIEW觸發程式之前使用 INSTEAD OF 來移除該選項。

{ [ DELETE ] [ ， ] [ INSERT ] [ ， ] [ UPDATE ] }

指定當試圖針對這份資料表或檢視表執行此動作時，會啟動 DML 觸發程序的資料修改陳述式。請至少指定一個選項。您可以在觸發程序定義中，依照任何順序來使用這些選項的任何組合。

針對 INSTEAD OF 觸發程式，您無法 DELETE 在具有引用關聯性的數據表上使用選項，並指定串聯動作 ON DELETE。同樣地， UPDATE 在具有引用關聯性的數據表上不允許此選項，指定串聯動作 ON UPDATE。

加上

適用於：SQL Server 2008 (10.0.x) 到 SQL Server 2008 R2 (10.50.x)。

指定應該加入現有類型的其他觸發程序。 WITH APPEND 無法與觸發程式搭配 INSTEAD OF 使用，或者 AFTER 如果明確表示觸發程序的話。為了提供回溯相容性，請只在指定時WITH APPEND使用，而不FOR使用 INSTEAD OF 或 AFTER。您無法指定 WITH APPEND 是否使用 EXTERNAL NAME （也就是說，如果觸發程式是 CLR 觸發程式）。

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目錄檢視，做為巨集。

不適用於複寫

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

sql_statement

觸發程序的條件和動作。觸發程序的條件可指定一些其他準則，以判斷所嘗試的 DML、DDL 或登入事件是否導致觸發程序動作的執行。

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

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

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

SELECT * FROM deleted;

如需詳細資訊，請參閱使用插入和刪除的數據表。

DDL 和登入觸發程式會使用 EVENTDATA 函式擷取觸發事件的相關信息。如需詳細資訊，請參閱使用 EVENTDATA 函式。

SQL Server 允許透過資料表或檢視上的觸發程式更新 text、ntext 或 INSTEAD OF 資料行。

重要

ntext、text 及 image 資料類型將從未來的 Microsoft SQL Server 版本移除。請避免在新的開發工作中使用這些資料類型，並規劃修改目前在使用這些資料類型的應用程式。請改用 nvarchar（max）、varchar（max）和 varbinary（max）。 AFTER和 INSTEAD OF 觸發程式都支援插入和刪除數據表中的 varchar（max）、nvarchar（max）和 varbinary（max） 數據。

對於記憶體優化數據表上的觸發程式，最上層允許的唯一 sql_statement 是區塊 ATOMIC 。區塊內 ATOMIC 允許的 T-SQL 受限於原生程式內允許的 T-SQL。

<method_specifier>

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

注意

根據預設，會關閉 SQL Server 執行 CLR 程式碼的能力。您可以建立、修改及卸除參考 Managed 程式代碼模組的資料庫物件，但這些參考不會在 SQL Server 的實例中執行，除非已啟用 clr 選項且已啟用 sp_configure。

DML 觸發程式的備註

DML 觸發程序常會用於執行商務規則與資料完整性。 SQL Server 透過和 ALTER TABLE 語句提供宣告式引用完整性（DRI）。CREATE TABLE 不過，DRI 並不提供跨資料庫的參考完整性。參考完整性是指資料表主索引鍵和外部索引鍵之間的關聯性規則。若要強制執行引用完整性，請在和中使用 PRIMARY KEYFOREIGN KEY 和 ALTER TABLECREATE TABLE條件約束。如果觸發程式數據表上有條件約束，則會在觸發程式執行之後 INSTEAD OF 和觸發程式執行之前 AFTER 檢查它們。如果違反條件約束， INSTEAD OF 則會回復觸發程式動作，而且 AFTER 不會引發觸發程式。

您可以使用，指定要在資料表AFTER上執行的第一個和最後sp_settriggerorder一個觸發程式。您只能針對資料表上的每個AFTER、 INSERT和作業指定一個第一個和UPDATE最後DELETE一個觸發程式。如果相同數據表上有其他 AFTER 觸發程式，則會隨機執行。

ALTER TRIGGER如果語句變更第一個或最後一個觸發程式，則會卸除修改之觸發程式上設定的第一個或最後一個屬性，而且您必須使用 sp_settriggerorder重設順序值。

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

INSTEAD OF如果數據表上定義的觸發程式會對通常會INSTEAD OF再次引發觸發程式的數據表執行語句，則不會以遞歸方式呼叫觸發程式。相反地，語句會像數據表沒有 INSTEAD OF 觸發程式一樣處理，並啟動條件約束作業和 AFTER 觸發程式的鏈結。例如，如果觸發程式定義為 INSTEAD OF INSERT 數據表的觸發程式。而且，如果觸發程式在相同的數據表上執行 INSERT 語句， INSERT 則觸發程式啟動的 INSTEAD OF 語句不會再次呼叫觸發程式。 INSERT觸發程式所啟動的會啟動執行條件約束動作的程式，並引發針對數據表定義的任何AFTER INSERT觸發程式。

INSTEAD OF在檢視上定義的觸發程式會對通常會再次引發INSTEAD OF觸發程式的檢視執行語句時，不會以遞歸方式呼叫它。相反地，陳述式會解析成針對檢視下的基底資料表來進行的修改。在這個情況下，檢視定義必須符合可更新之檢視的所有限制。如需可更新檢視的定義，請參閱透過檢視修改資料。

例如，如果觸發程式定義為 INSTEAD OF UPDATE 檢視的觸發程式。而且，觸發程式會執行 UPDATE 參考相同檢視的語句， UPDATE 觸發程式所啟動的 INSTEAD OF 語句不會再次呼叫觸發程式。 UPDATE觸發程式所啟動的會針對檢視處理，就像檢視沒有觸發程式一INSTEAD OF樣。所 UPDATE 變更的數據行必須解析為單一基表。基礎基表的每個修改都會啟動套用條件約束鏈結，並引發 AFTER 針對數據表定義的觸發程式。

測試特定數據行的 UPDATE 或 INSERT 動作

您可以設計 Transact-SQL 觸發程式，根據 UPDATE 或 INSERT 修改特定數據行來執行特定動作。針對此目的，請使用觸發程序主體中的 UPDATE 或 COLUMNS_UPDATED 。 UPDATE() 測試 UPDATE 或 INSERT 嘗試一個數據行。 COLUMNS_UPDATED UPDATE測試或INSERT執行於多個數據行上的動作。此函式會傳回一個位元模式，指出插入或更新了哪些資料行。

觸發程式限制

CREATE TRIGGER 必須是批次中的第一個語句，而且只能套用至一個數據表。

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

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

相同的觸發程式動作可以針對相同INSERT語句中的多個用戶動作定義（例如 UPDATE 和 CREATE TRIGGER）。

INSTEAD OF DELETE / INSTEAD OF UPDATE 在數據表上無法定義觸發程式，該數據表的外鍵具有定義動作的 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
DROP TABLE
DBCC DBREINDEX
ALTER PARTITION FUNCTION
ALTER TABLE 用來執行下列動作時：
- 新增、修改或卸除資料行。
- 切換資料分割。
- 新增或卸除 PRIMARY KEY 或 UNIQUE 條件約束。

注意

由於 SQL Server 不支援系統資料表上的使用者定義觸發程式，因此建議您不要在系統數據表上建立使用者定義的觸發程式。

優化 DML 觸發程式

觸發程序會在交易中運作 (隱含或其他方式)，並在開啟時鎖定資源。鎖定會維持原位，直到交易得到確認（含COMMIT）或拒絕（含）。ROLLBACK 觸發程序執行時間越長，封鎖其他處理序的機率越高。因此，撰寫觸發程序時，請盡可能降低其持續時間。要確保較短持續時間的方法之一，是在 DML 陳述式變更零個資料列時釋放觸發程序。

若要針對不會變更任何資料列的命令釋放觸發程序，請使用系統變數 ROWCOUNT_BIG。

下列 T-SQL 程式碼片段示範如何針對不會變更任何資料列的命令釋放觸發程序。此程式碼應該出現在每個 DML 觸發程序的開頭：

IF (ROWCOUNT_BIG() = 0)
RETURN;

DDL 觸發程式的備註

如同標準觸發程序，DDL 觸發程序也會啟動預存程序來回應事件。但是，不同於標準觸發程式，它們不會在數據表或檢視表上回應 UPDATE、 INSERT或 DELETE 語句。相反地，其主要執行目的是為了回應資料定義語言 (DDL) 陳述式。語句類型包括 CREATE、、、ALTERDROP、GRANT、DENY、和 REVOKEUPDATE 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。

停用登入觸發程式

登入觸發程序可以有效地防止所有使用者成功連線到資料庫引擎，包括 sysadmin 固定伺服器角色的成員。當登入觸發程式防止連線時， 系統管理員 固定伺服器角色的成員可以使用專用的系統管理員連線，或以最少的組態模式-f啟動 Database Engine 來連線。如需詳細資訊，請參閱 Database Engine Service （資料庫引擎服務）啟動選項。

一般觸發程序考慮

傳回結果

SQL Server 的未來版本將移除從觸發程序傳回結果的功能。傳回結果集的觸發程式可能會在未設計為使用結果集的應用程式中造成非預期的行為。在新的開發工作中，請避免從觸發程序傳回結果集，並計畫修改目前如此運作的應用程式。若要避免觸發程序傳回結果集，請將不允許來自觸發程序的結果選項設為 1。

登入觸發程序一律不允許傳回結果集，且您無法設定這項行為。如果登入觸發程序產生結果集，就無法啟動觸發程序，並引發會拒絕觸發程序的登入嘗試。

多個觸發程序

SQL Server 可讓您為每個 DML、DDL 或 LOGON 事件建立多個觸發程式。例如，如果 CREATE TRIGGER FOR UPDATE 針對已經有 UPDATE 觸發程式的數據表執行，則會建立額外的更新觸發程式。在舊版的 SQL Server 中，每個、 INSERT或 UPDATE 資料修改事件只允許每個DELETE數據表有一個觸發程式。

遞歸觸發程式

使用啟用RECURSIVE_TRIGGERS設定時ALTER DATABASE，SQL Server 也支援觸發程式的遞歸調用。

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

間接遞迴：使用間接遞迴，應用程式會更新資料表 T1。這會引發觸發程式 TR1，並更新資料表 T2。觸發 T2 程式接著會引發並更新資料表 T1。
直接遞迴：在直接遞迴中，應用程式會更新資料表 T1。這會引發觸發程式 TR1，並更新資料表 T1。因為數據表 T1 已更新，所以觸發 TR1 程式會再次引發，依序引發等等。

下列範例同時使用間接和直接觸發程式遞歸假設資料表TR1上定義了兩個更新觸發程式 TR2 和 T1。觸發程式 TR1 會以遞歸方式更新數據表 T1 。語句 UPDATE 會每次 TR1 執行 TR2 一次。此外，啟動 TR1 會 TR1 觸發執行（遞歸方式）和 TR2。特定觸發程式的插入和刪除數據表包含只對應至 UPDATE 叫用觸發程式的語句的數據列。

注意

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

停用 RECURSIVE_TRIGGERS 設定只會防止直接遞歸。若要同時停用間接遞歸，請使用 sp_configure將巢狀觸發程式伺服器選項設定為 0。

如果任一個觸發程式執行 ROLLBACK TRANSACTION，不論巢狀層級為何，就不會再執行任何觸發程式。

巢狀觸發程式

您最多可以將觸發程序巢狀化為 32 個層級。如果觸發程序變更了一份資料表，但這份資料表上有其他觸發程序，就會啟動第二個觸發程序，而第二個觸發程序可能會再呼叫第三個觸發程序，依此類推。如果鏈結中的任何觸發程序造成無限迴圈，就會超出巢狀層級，並取消觸發程序。當 Transact-SQL 觸發程序參考 CLR 常式、類型或彙總來啟動受控碼時，這項參考也會計入 32 層巢狀限制的一層。若是從受控碼內叫用的方法，則不計入這項限制。

若要停用巢狀觸發程式，請將巢狀觸發程式選項 sp_configure 設定為 0（關閉）。預設設定可支援巢狀觸發程序。如果巢狀觸發程式關閉，則遞歸觸發程式也會停用，儘管 RECURSIVE_TRIGGERS 使用 ALTER DATABASE所設定的設定。

即使AFTER程式伺服器組態選項為 0，觸發程式內的第一INSTEAD OF個觸發程式也會引發。但是，在此設定下，後續 AFTER 觸發程式不會引發。檢閱應用程式的巢狀觸發程序，以判斷當 [巢狀觸發程序] 伺服器設定選項設為 0 時，應用程式是否會遵循您的商務規則。若否，請進行適當的修改。

延遲的名稱解析

SQL Server 允許 Transact-SQL 預存程式、觸發程式、函式和批次來參考編譯時期不存在的數據表。這項功能稱為延遲名稱解析。

權限

若要建立 DML 觸發程式，它需要 ALTER 建立觸發程式之數據表或檢視表的許可權。

若要建立具有伺服器範圍（ON ALL SERVER）或登入觸發程式的 DDL 觸發程式，則需要 CONTROL SERVER 伺服器上的許可權。若要建立具有資料庫範圍（ON DATABASE）的 DDL 觸發程式，需要 ALTER ANY DATABASE DDL TRIGGER 目前資料庫中的許可權。

範例

A。搭配提醒訊息使用 DML 觸發程式

以下 DML 觸發器會在任何人嘗試新增或更改 Customer AdventureWorks2025 資料庫資料表資料時，向用戶端列印訊息。

CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B. 搭配提醒電子郵件訊息使用 DML 觸發程式

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

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
    EXECUTE msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2022 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 條件約束只會參考定義數據行層級或數據表層級條件約束的數據行，因此您必須將任何跨數據表條件約束（在此案例中為商務規則）定義為觸發程式。

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

USE AdventureWorks2022;
GO

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 (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    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 觸發程序避免卸除資料庫中的同義字。

CREATE TRIGGER safety
    ON DATABASE
    FOR DROP_SYNONYM
    AS IF (@@ROWCOUNT = 0)
           RETURN;
       RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1);
       ROLLBACK;
GO

DROP TRIGGER safety
    ON DATABASE;
GO

E. 使用伺服器範圍的 DDL 觸發程式

下列範例會使用 DDL 觸發程式，在目前伺服器實例上發生任何 CREATE DATABASE 事件時列印訊息，並使用函 EVENTDATA 式來擷取對應 Transact-SQL 語句的文字。如需在 DDL 觸發程式中使用 EVENTDATA 的詳細資訊，請參閱使用 EVENTDATA 函式。

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。變更 <password> 為強密碼。

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' 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程式會在範例 D 中建立。使用資料庫範圍的 DDL 觸發程式。

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

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-11-19