CREATE TRIGGER (Transact-SQL)CREATE TRIGGER (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

建立 DML、DDL 或登入觸發程序。Creates a DML, DDL, or logon trigger. 觸發程序是一種特殊的預存程序,其會在資料庫伺服器發生事件時自動執行。A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. 當使用者試圖透過資料操作語言 (DML) 事件來修改資料時,便會執行 DML 觸發程序。DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML 事件包括資料表或檢視的 INSERT、UPDATE 或 DELETE 陳述式。DML events are INSERT, UPDATE, or DELETE statements on a table or view. 無論資料表的資料列有無受到影響,這些觸發程序皆會在引發任何有效事件時引發。These triggers fire when any valid event fires, whether table rows are affected or not. 如需詳細資訊,請參閱 DML TriggersFor more information, see DML Triggers.

DDL 觸發程序的執行目的是回應各種資料定義語言 (DDL) 事件。DDL triggers run in response to a variety of data definition language (DDL) events. 這些事件主要是對應到 Transact-SQLTransact-SQL CREATE、ALTER 和 DROP 陳述式,以及執行類似 DDL 作業的特定系統預存程序。These events primarily correspond to Transact-SQLTransact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

登入觸發程序則會因回應在建立使用者工作階段時引發的 LOGON 事件而引發。Logon triggers fire in response to the LOGON event that's raised when a user's session is being established. 您可以直接從 Transact-SQLTransact-SQL 陳述式建立觸發程序,也可以使用 MicrosoftMicrosoft .NET Framework.NET Framework 通用語言執行平台 (CLR) 所建立的組件方法來建立觸發程序,並將其上傳到 SQL ServerSQL Server 執行個體。You can create triggers directly from Transact-SQLTransact-SQL statements or from methods of assemblies that are created in the MicrosoftMicrosoft .NET Framework.NET Framework common language runtime (CLR) and uploaded to an instance of SQL ServerSQL Server. SQL ServerSQL Server 可讓您針對任何特定陳述式建立多個觸發程序。lets you create multiple triggers for any specific statement.

重要

觸發程序內的惡意程式碼可能在擴大的權限下執行。Malicious code inside triggers can run under escalated privileges. 如需如何降低這項威脅的詳細資訊,請參閱管理觸發程序安全性For more information on how to mitigate this threat, see Manage Trigger Security.

注意

本文會探討如何將 .NET Framework CLR 整合至 SQL Server。The integration of .NET Framework CLR into SQL Server is discussed in this article. CLR 整合不適用於 Azure SQL Database。CLR integration does not apply to Azure SQL Database.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
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  
  
-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a 
-- table (DML Trigger on memory-optimized tables)  
  
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 ]  
  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE or UPDATE statement (DDL Trigger)  
  
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 ]  
  
-- Trigger on a LOGON event (Logon Trigger)  
  
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 ]  
  

語法Syntax

-- Azure SQL Database Syntax   
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
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 ]  
  
-- Azure SQL Database Syntax  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE, or UPDATE STATISTICS statement (DDL Trigger)   
  
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 ]  

引數Arguments

OR ALTEROR ALTER
適用於:Azure SQL DatabaseSQL Database SQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始)。Applies to: Azure SQL DatabaseSQL Database, SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).

只有在觸發程序已存在時,才能有條件地更改它。Conditionally alters the trigger only if it already exists.

schema_nameschema_name
DML 觸發程序所屬的結構描述名稱。The name of the schema to which a DML trigger belongs. DML 觸發程序範圍僅限於其建立所在之資料表或檢視表的結構描述。DML triggers are scoped to the schema of the table or view on which they're created. 您不能為 DDL 或登入觸發程序指定 schema_nameschema_name can't be specified for DDL or logon triggers.

trigger_nametrigger_name
觸發程序的名稱。The name of the trigger. trigger_name 必須遵循識別碼的規則,不過 trigger_name 的開頭不能是 # 或 ##。A trigger_name must follow the rules for identifiers, except that trigger_name can't start with # or ##.

table | viewtable | view
執行 DML 觸發程序的資料表或檢視表。The table or view on which the DML trigger runs. 這個資料表或檢視表有時也稱為觸發程序資料表或觸發程序檢視表。This table or view is sometimes referred to as the trigger table or trigger view. 您可以選擇性地指定資料表或檢視的完整名稱。Specifying the fully qualified name of the table or view is optional. 您僅可透過 INSTEAD OF 觸發程序來參考檢視表。You can only reference a view by an INSTEAD OF trigger. 您不能在本機或全域暫存資料表上定義 DML 觸發程序。You can't define DML triggers on local or global temporary tables.

DATABASEDATABASE
將 DDL 觸發程序的範圍套用在目前資料庫上。Applies the scope of a DDL trigger to the current database. 若有指定,每當目前資料庫中出現 event_typeevent_group 時,都會引發這個觸發程序。If specified, the trigger fires whenever event_type or event_group occurs in the current database.

ALL SERVERALL SERVER
適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

將 DDL 或登入觸發程序的範圍套用在目前伺服器上。Applies the scope of a DDL or logon trigger to the current server. 若有指定,每當目前伺服器中的任何位置出現 event_typeevent_group 時,都會引發這個觸發程序。If specified, the trigger fires whenever event_type or event_group occurs anywhere in the current server.

WITH ENCRYPTIONWITH ENCRYPTION
適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

遮蔽 CREATE TRIGGER 陳述式的文字。Obscures the text of the CREATE TRIGGER statement. 使用 WITH ENCRYPTION 可防止在 SQL ServerSQL Server 複寫中發行這個觸發程序。Using WITH ENCRYPTION prevents the trigger from being published as part of SQL ServerSQL Server replication. CLR 觸發程序不能指定 WITH ENCRYPTION。WITH ENCRYPTION can't be specified for CLR triggers.

EXECUTE ASEXECUTE AS
指定用來執行這個觸發程序的安全性內容。Specifies the security context under which the trigger is executed. 可讓您控制 SQL ServerSQL Server 執行個體要利用哪個使用者帳戶來驗證觸發程序所參考之任何資料庫物件的權限。Enables you to control which user account the instance of SQL ServerSQL Server uses to validate permissions on any database objects that are referenced by the trigger.

如果觸發程序是在經記憶體最佳化的資料表上,則這個選項為必要。This option is required for triggers on memory-optimized tables.

如需詳細資訊,請參閱 EXECUTE AS Clause (Transact-SQL)For more information, seeEXECUTE AS Clause (Transact-SQL).

NATIVE_COMPILATIONNATIVE_COMPILATION
表示觸發程序是原生編譯的。Indicates that the trigger is natively compiled.

如果觸發程序是在經記憶體最佳化的資料表上,則這個選項為必要。This option is required for triggers on memory-optimized tables.

SCHEMABINDINGSCHEMABINDING
確保使用者無法卸除或改變觸發程序所參考的資料表。Ensures that tables referenced by a trigger can't be dropped or altered.

如果觸發程序是在經記憶體最佳化的資料表上,則這個選項為必要,且其不支援傳統資料表上的觸發程序。This option is required for triggers on memory-optimized tables and isn't supported for triggers on traditional tables.

FOR | AFTERFOR | AFTER
AFTER 指定只在觸發 SQL 陳述式指定的所有作業都成功啟動時,才引發 DML 觸發程序。AFTER specifies that the DML trigger fires only when all operations specified in the triggering SQL statement have launched successfully. 所有參考的串聯動作和條件約束檢查也都必須成功之後,才會引發這個觸發程序。All referential cascade actions and constraint checks must also succeed before this trigger fires.

當指定的關鍵字只有 FOR 時,預設值便是 AFTER。AFTER is the default when FOR is the only keyword specified.

您不能在檢視表上定義 AFTER 觸發程序。You can't define AFTER triggers on views.

INSTEAD OFINSTEAD OF
指定要啟動 DML 觸發程序,而「不是」 觸發 SQL 陳述式,因此會覆寫觸發陳述式的動作。Specifies that the DML trigger launches instead of the triggering SQL statement, thus, overriding the actions of the triggering statements. 您不能針對 DDL 或登入觸發程序指定 INSTEAD OF。You can't specify INSTEAD OF for DDL or logon triggers.

您最多只能在資料表或檢視表上的每個 INSERT、UPDATE 或 DELETE 陳述式,各定義一個 INSTEAD OF 觸發程序。At most, you can define one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement on a table or view. 不過,您可以定義檢視表中的檢視表,讓每份檢視表都有它自己的 INSTEAD OF 觸發程序。You can also define views on views where each view has its own INSTEAD OF trigger.

您不能在使用 WITH CHECK OPTION 的可更新檢視表上定義 INSTEAD OF 觸發程序。You can't define INSTEAD OF triggers on updatable views that use WITH CHECK OPTION. SQL ServerSQL Server 將 INSTEAD OF 觸發程序新增至已指定 WITH CHECK OPTION 的可更新檢視表時,這樣做會產生錯誤。Doing so results in an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. 您可以在定義 INSTEAD OF 觸發程序之前,使用 ALTER VIEW 來移除這個選項。You remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
指定當試圖針對這份資料表或檢視表執行此動作時,會啟動 DML 觸發程序的資料修改陳述式。Specifies the data modification statements that activate the DML trigger when it's tried against this table or view. 請至少指定一個選項。Specify at least one option. 您可以在觸發程序定義中,依照任何順序來使用這些選項的任何組合。Use any combination of these options in any order in the trigger definition.

針對 INSTEAD OF 觸發程序,如果資料表上含有指定串聯動作 ON DELETE 的參考關聯性,您就不能在該資料表上使用 DELETE 選項。For INSTEAD OF triggers, you can't use the DELETE option on tables that have a referential relationship, specifying a cascade action ON DELETE. 同樣地,如果資料表上含有指定串聯動作 ON UPDATE 的參考關聯性,您就不能在該資料表上使用 UPDATE 選項。Similarly, the UPDATE option isn't allowed on tables that have a referential relationship, specifying a cascade action ON UPDATE.

WITH APPENDWITH APPEND
適用於SQL Server 2008SQL Server 2008SQL Server 2008 R2SQL Server 2008 R2Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2008 R2SQL Server 2008 R2.

指定應該加入現有類型的其他觸發程序。Specifies that an additional trigger of an existing type should be added. 當使用 INSTEAD OF 觸發程序或明確指示 AFTER 觸發程序時,便不能使用 WITH APPEND。WITH APPEND can't be used with INSTEAD OF triggers or if an AFTER trigger is explicitly stated. 為了與舊版相容,請僅在指定 FOR 且不含 INSTEAD OF 或 AFTER 時,才使用 WITH APPEND。For backward compatibility, only use WITH APPEND when FOR is specified, without INSTEAD OF or AFTER. 如果您使用 EXTERNAL NAME (亦即,如果觸發程序是 CLR 觸發程序),就不能指定 WITH APPEND。You can't specify WITH APPEND if using EXTERNAL NAME (that is, if the trigger is a CLR trigger).

event_typeevent_type
在啟動之後會引發 DDL 觸發程序的 Transact-SQLTransact-SQL 語言事件名稱。The name of a Transact-SQLTransact-SQL language event that, after launch, causes a DDL trigger to fire. 有效的 DDL 觸發程序事件會列在 DDL 事件中。Valid events for DDL triggers are listed in DDL Events.

event_groupevent_group
Transact-SQLTransact-SQL 語言事件的預先定義群組名稱。The name of a predefined grouping of Transact-SQLTransact-SQL language events. 在啟動屬於 event_group 的任何 Transact-SQLTransact-SQL 語言事件之後,便會引發 DDL 觸發程序。The DDL trigger fires after launch of any Transact-SQLTransact-SQL language event that belongs to event_group. 有效的 DDL 觸發程序事件群組會列在 DDL 事件群組中。Valid event groups for DDL triggers are listed in DDL Event Groups.

在完成執行 CREATE TRIGGER 之後,您也可以將其所涵蓋的事件類型新增至 sys.trigger_events 目錄檢視,以將 event_group 作為巨集。After the CREATE TRIGGER has finished running, event_group also acts as a macro by adding the event types it covers to the sys.trigger_events catalog view.

NOT FOR REPLICATIONNOT FOR REPLICATION
適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

指出當複寫代理程式修改觸發程序所含的資料表時,不應執行觸發程序。Indicates that the trigger shouldn't be run when a replication agent modifies the table that's involved in the trigger.

sql_statementsql_statement
觸發程序的條件和動作。The trigger conditions and actions. 觸發程序的條件可指定一些其他準則,以判斷所嘗試的 DML、DDL 或登入事件是否導致觸發程序動作的執行。Trigger conditions specify additional criteria that determine whether the tried DML, DDL, or logon events cause the trigger actions to be run.

當嘗試作業時,Transact-SQLTransact-SQL 陳述式所指定的觸發程序動作便會生效。The trigger actions specified in the Transact-SQLTransact-SQL statements go into effect when the operation is tried.

觸發程序可以包括任意數目和任何類型的 Transact-SQLTransact-SQL 陳述式,但有例外。Triggers can include any number and type of Transact-SQLTransact-SQL statements, with exceptions. 如需詳細資訊,請參閱<備註>。For more information, see Remarks. 觸發程序的設計用意是根據資料修改或定義陳述式,來檢查或變更資料,而不應將資料傳回給使用者。A trigger is designed to check or change data based on a data modification or definition statement; it should't return data to the user. 觸發程序的 Transact-SQLTransact-SQL 陳述式通常會包括流程控制語言The Transact-SQLTransact-SQL statements in a trigger frequently include control-of-flow language.

DML 觸發程序會使用 deleted 和 inserted 邏輯 (概念) 資料表。DML triggers use the deleted and inserted logical (conceptual) tables. 它們的結構類似於定義觸發程序的資料表,也就是使用者動作試圖處理的資料表。They're structurally similar to the table on which the trigger is defined, that is, the table on which the user action is tried. deleted 和 inserted 資料表用來保留使用者動作可能已變更之資料列的舊值或新值。The deleted and inserted tables hold the old values or new values of the rows that may be changed by the user action. 例如,若要擷取 deleted 資料表中的所有值,請使用:For example, to retrieve all values in the deleted table, use:

SELECT * FROM deleted;  

如需詳細資訊,請參閱使用插入和刪除的資料表For more information, see Use the inserted and deleted Tables.

DDL 和登入觸發程序會使用 EVENTDATA (Transact-SQL) 函式來擷取觸發事件的相關資訊。DDL and logon triggers capture information about the triggering event by using the EVENTDATA (Transact-SQL) function. 如需詳細資訊,請參閱使用 EVENTDATA 函式For more information, see Use the EVENTDATA Function.

SQL ServerSQL Server 允許使用資料表或檢視表的 INSTEAD OF 觸發程序來更新 textntextimage 資料行。allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

重要

MicrosoftMicrosoftSQL ServerSQL Server 的未來版本將會移除 ntexttextimage 資料類型。ntext, text, and image data types will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 請避免在新的開發工作中使用這些資料類型,並規劃修改目前在使用這些資料類型的應用程式。Avoid using these data types in new development work, and plan to modify applications that currently use them. 請改用 nvarchar(max)varchar(max)varbinary(max)Use nvarchar(max), varchar(max), and varbinary(max) instead. AFTER 和 INSTEAD OF 兩個觸發程序都支援插入和刪除資料表中的 varchar(MAX)nvarchar(MAX)varbinary(MAX) 資料。Both AFTER and INSTEAD OF triggers support varchar(MAX), nvarchar(MAX), and varbinary(MAX) data in the inserted and deleted tables.

針對經記憶體最佳化資料表上的觸發程序,唯一允許的最上層 sql_statement 是 ATOMIC 區塊。For triggers on memory-optimized tables, the only sql_statement allowed at the top level is an ATOMIC block. ATOMIC 區塊內允許使用哪些 T-SQL 則受限於原生程序內允許使用的 T-SQL 而定。The T-SQL allowed inside the ATOMIC block is limited by the T-SQL allowed inside native procs.

< method_specifier > 適用對象SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017< method_specifier > Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

對於 CLR 觸發程序,指定繫結觸發程序的組件方法。For a CLR trigger, specifies the method of an assembly to bind with the trigger. 此方法不可使用任何引數,且必須傳回空值。The method must take no arguments and return void. class_name 必須是有效的 SQL ServerSQL Server 識別碼,且必須以類別的形式存在於可以顯示的組件中。class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly with assembly visibility. 如果該類別的名稱符合命名空間規定,利用 '.' 來分隔命名空間的各個部分,您就必須用 [ ] 或 " " 分隔字元來分隔類別名稱。If the class has a namespace-qualified name that uses '.' to separate namespace parts, the class name must be delimited by using [ ] or " " delimiters. 這個類別不能是巢狀類別。The class can't be a nested class.

注意

依預設,會關閉 SQL ServerSQL Server 執行 CLR 程式碼的能力。By default, the ability of SQL ServerSQL Server to run CLR code is off. 您可以建立、修改及卸除參考受控碼模組的資料庫物件,但除非您使用 sp_configure 來啟用 clr enabled 選項;否則,這些參考就不會在 SQL ServerSQL Server 執行個體中執行。You can create, modify, and drop database objects that reference managed code modules, but these references don't run in an instance of SQL ServerSQL Server unless the clr enabled Option is enabled by using sp_configure.

DML 觸發程序的備註Remarks for DML Triggers

DML 觸發程序常會用於執行商務規則與資料完整性。DML triggers are frequently used for enforcing business rules and data integrity. SQL ServerSQL Server 利用 ALTER TABLE 及 CREATE TABLE 陳述式提供宣告式參考完整性 (DRI)。provides declarative referential integrity (DRI) through the ALTER TABLE and CREATE TABLE statements. 不過,DRI 並不提供跨資料庫的參考完整性。However, DRI doesn't provide cross-database referential integrity. 參考完整性是指資料表主索引鍵和外部索引鍵之間的關聯性規則。Referential integrity refers to the rules about the relationships between the primary and foreign keys of tables. 若要強制執行參考完整性,請在 ALTER TABLE 和 CREATE TABLE 中,使用 PRIMARY KEY 和 FOREIGN KEY 條件約束。To enforce referential integrity, use the PRIMARY KEY and FOREIGN KEY constraints in ALTER TABLE and CREATE TABLE. 如果觸發程序資料表有條件約束,便會在 INSTEAD OF 觸發程序執行之後、AFTER 觸發程序執行之前,檢查這些條件約束。If constraints exist on the trigger table, they're checked after the INSTEAD OF trigger runs and before the AFTER trigger runs. 如果違反條件約束,便會回復 INSTEAD OF 觸發程序動作,而不會引發 AFTER 觸發程序。If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger isn't fired.

您可以使用 sp_settriggerorder 來指定資料表要執行的第一個和最後一個 AFTER 觸發程序。You can specify the first and last AFTER triggers to be run on a table by using sp_settriggerorder. 您只能在資料表的每個 INSERT、UPDATE 和 DELETE 作業上,指定頭尾各一個 AFTER 觸發程序。You can specify only one first and one last AFTER trigger for each INSERT, UPDATE, and DELETE operation on a table. 如果同一份資料表有其他 AFTER 觸發程序,便會隨機執行。If there are other AFTER triggers on the same table, they're randomly run.

如果 ALTER TRIGGER 陳述式變更第一個或最後一個觸發程序,便會卸除已修改觸發程序上設定的第一個或最後一個屬性,因此您必須使用 sp_settriggerorder 來重設順序值。If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and you must reset the order value by using sp_settriggerorder.

只有在觸發的 SQL 陳述式已執行成功之後,才會執行 AFTER 觸發程序。An AFTER trigger is run only after the triggering SQL statement has run successfully. 所謂執行成功,包括與已更新或刪除之物件關聯的所有參考串聯動作和條件約束檢查。This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted. AFTER 不會以遞迴方式在相同資料表上引發 INSTEAD OF 觸發程序。An AFTER does not recursively fire an INSTEAD OF trigger on the same table.

如果資料表上定義的 INSTEAD OF 觸發程序是針對通常會再次引發 INSTEAD OF 觸發程序的資料表來執行陳述式,則不會遞迴呼叫這個觸發程序。If an INSTEAD OF trigger defined on a table runs a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger isn't called recursively. 相反地,陳述式會依照資料表不具 INSTEAD OF 觸發程序的方式來處理,並會啟動條件約束作業和 AFTER 觸發程序執行的鏈結。Instead, the statement processes as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. 例如,如果某個觸發程序定義為資料表的 INSTEAD OF INSERT 觸發程序,For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table. 且觸發程序會在相同資料表上執行 INSERT 陳述式,則 INSTEAD OF 觸發程序所啟動的 INSERT 陳述式不會再次呼叫觸發程序。And, the trigger runs an INSERT statement on the same table, the INSERT statement launched by the INSTEAD OF trigger doesn't call the trigger again. 觸發程序所啟動的 INSERT 會啟動程序,以執行條件約束動作,並引發資料表定義的任何 AFTER INSERT 觸發程序。The INSERT launched by the trigger starts the process of running constraint actions and firing any AFTER INSERT triggers defined for the table.

當檢視表上定義的 INSTEAD OF 觸發程序是針對通常會再次引發 INSTEAD OF 觸發程序的檢視表來執行陳述式,則不會遞迴呼叫這個觸發程序。When an INSTEAD OF trigger defined on a view runs a statement against the view that would ordinarily fire the INSTEAD OF trigger again, it's not called recursively. 相反地,陳述式會解析成針對檢視下的基底資料表來進行的修改。Instead, the statement is resolved as modifications against the base tables underlying the view. 在這個情況下,檢視定義必須符合可更新之檢視的所有限制。In this case, the view definition must meet all the restrictions for an updatable view. 如需可更新檢視的定義,請參閱透過檢視修改資料For a definition of updatable views, see Modify Data Through a View.

例如,如果某個觸發程序定義為檢視表的 INSTEAD OF UPDATE 觸發程序,For example, if a trigger is defined as an INSTEAD OF UPDATE trigger for a view. 且觸發程序會執行參考相同檢視表的 UPDATE 陳述式,則 INSTEAD OF 觸發程序所啟動的 UPDATE 陳述式不會再次呼叫觸發程序。And, the trigger runs an UPDATE statement referencing the same view, the UPDATE statement launched by the INSTEAD OF trigger doesn't call the trigger again. 系統會依照檢視表不具 INSTEAD OF 觸發程序的方式,來處理檢視表中由觸發程序所啟動的 UPDATE。The UPDATE launched by the trigger is processed against the view as if the view didn't have an INSTEAD OF trigger. UPDATE 所變更的資料行必須解析成單一基底資料表。The columns changed by the UPDATE must be resolved to a single base table. 基礎基底資料表的每項修改都會啟動套用條件約束及引發定義給資料表之 AFTER 觸發程序的鏈結。Each modification to an underlying base table starts the chain of applying constraints and firing AFTER triggers defined for the table.

測試特定資料行的 UPDATE 或 INSERT 動作Testing for UPDATE or INSERT Actions to Specific Columns

您可以設計一個 Transact-SQLTransact-SQL 觸發程序,以依據特定資料行的 UPDATE 或 INSERT 修改來進行特定動作。You can design a Transact-SQLTransact-SQL trigger to do certain actions based on UPDATE or INSERT modifications to specific columns. 請在觸發程序的主體中,使用 UPDATE()COLUMNS_UPDATED 來完成這個目的。Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() 會測試單一資料行所嘗試的 UPDATE 或 INSERT。UPDATE() tests for UPDATE or INSERT attempts on one column. COLUMNS_UPDATED 會測試多個資料行所執行的 UPDATE 或 INSERT 動作。COLUMNS_UPDATED tests for UPDATE or INSERT actions that run on multiple columns. 此函式會傳回一個位元模式,指出插入或更新了哪些資料行。This function returns a bit pattern that indicates which columns were inserted or updated.

觸發程序限制Trigger Limitations

CREATE TRIGGER 必須是批次中的第一個陳述式,且只能套用至一份資料表。CREATE TRIGGER must be the first statement in the batch and can apply to only one table.

觸發程序只會建立在目前資料庫中;不過,觸發程序可以參考在目前資料庫之外的物件。A trigger is created only in the current database; however, a trigger can reference objects outside the current database.

如果指定觸發程序結構描述名稱來限定觸發程序,請依照相同方式來限定資料表名稱。If the trigger schema name is specified to qualify the trigger, qualify the table name in the same way.

您可以在相同 CREATE TRIGGER 陳述式中,定義多個使用者動作 (如 INSERT 和 UPDATE) 的相同觸發程序動作。The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.

您不能在定義了 DELETE/UPDATE 動作串聯的資料表上,定義 INSTEAD OF DELETE/UPDATE 觸發程序。INSTEAD OF DELETE/UPDATE triggers can't be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined.

您可以在觸發程序內指定任何 SET 陳述式。Any SET statement can be specified inside a trigger. 在觸發程序的執行期間,所選取的 SET 選項會持續有效,之後,會還原為先前的設定。The SET option selected remains in effect during the execution of the trigger and then reverts to its former setting.

當引發觸發程序時,如同預存程序一樣,結果會傳回發出呼叫的應用程式。When a trigger fires, results are returned to the calling application, just like with stored procedures. 若要防止因引發觸發程序而將結果傳回給應用程式,請不要包含會傳回結果之 SELECT 陳述式或會在觸發程序中執行變數指派的陳述式。To prevent results being returned to an application because of a trigger firing, don't include either SELECT statements that return results or statements that carry out variable assignment in a trigger. 如果觸發程序包含會將結果傳回給使用者之 SELECT 陳述式或會執行變數指派的陳述式,則需要特殊處理。A trigger that includes either SELECT statements that return results to the user or statements that do variable assignment, requires special handling. 您必須將所傳回結果寫入允許修改觸發程序資料表的每個應用程式。You'd have to write the returned results into every application in which modifications to the trigger table are allowed. 如果必須在觸發程序內發生變數指派,請在觸發程序開頭使用 SET NOCOUNT 陳述式,以防止傳回任何結果集。If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the start of the trigger to prevent the return of any result sets.

雖然 TRUNCATE TABLE 陳述式實際上是 DELETE 陳述式,但是它並不會啟動觸發程序,因為此作業不會記錄個別的資料列刪除。Although a TRUNCATE TABLE statement is in effect a DELETE statement, it doesn't activate a trigger because the operation doesn't log individual row deletions. 但是,只有具有執行 TRUNCATE TABLE 陳述式權限的使用者才需要擔心這種方式會不小心阻止 DELETE 觸發程序。However, only those users with permissions to run a TRUNCATE TABLE statement need be concerned about inadvertently circumventing a DELETE trigger this way.

WRITETEXT 陳述式,不論有沒有記錄,都不會啟動觸發程序。The WRITETEXT statement, whether logged or unlogged, doesn't activate a trigger.

DML 觸發程序中不可使用下列 Transact-SQLTransact-SQL 陳述式:The following Transact-SQLTransact-SQL statements aren't allowed in a DML trigger:

ALTER DATABASEALTER DATABASE CREATE DATABASECREATE DATABASE DROP DATABASEDROP DATABASE
RESTORE DATABASERESTORE DATABASE RESTORE LOGRESTORE LOG RECONFIGURERECONFIGURE

此外,當 DML 觸發程序用於觸發動作的目標資料表或檢視表時,不可在 DML 觸發程序的主體內使用下列 Transact-SQLTransact-SQL 陳述式。Additionally, the following Transact-SQLTransact-SQL statements aren't allowed inside the body of a DML trigger when it's used against the table or view that's the target of the triggering action.

CREATE INDEX (包括 CREATE SPATIAL INDEX 和 CREATE XML INDEX)CREATE INDEX (including CREATE SPATIAL INDEX and CREATE XML INDEX) ALTER INDEXALTER INDEX DROP INDEXDROP INDEX
DBCC DBREINDEXDBCC DBREINDEX ALTER PARTITION FUNCTIONALTER PARTITION FUNCTION DROP TABLEDROP TABLE
用來執行下列動作的 ALTER TABLE:ALTER TABLE when used to do the following:

新增、修改或卸除資料行。Add, modify, or drop columns.

切換資料分割。Switch partitions.

加入或卸除 PRIMARY KEY 或 UNIQUE 條件約束。Add or drop PRIMARY KEY or UNIQUE constraints.

注意

由於 SQL ServerSQL Server 不支援系統資料表的使用者自訂觸發程序,因此,我們建議您不要在系統資料表上建立使用者自訂觸發程序。Because SQL ServerSQL Server does not support user-defined triggers on system tables, we recommend that you do not create user-defined triggers on system tables.

最佳化 DML 觸發程序Optimizing DML Triggers

觸發程序會在交易中運作 (隱含或其他方式),並在開啟時鎖定資源。Triggers work in transactions (implied or otherwise) and while they're open, they lock resources. 這項鎖定會持續,直到確認 (使用 COMMIT) 或拒絕 (使用 ROLLBACK) 交易為止。The lock remains in place until the transaction is confirmed (with COMMIT) or rejected (with a ROLLBACK). 觸發程序執行時間越長,封鎖其他處理序的機率越高。The longer a trigger runs, the higher the probability that another process is then blocked. 因此,撰寫觸發程序時,請盡可能降低其持續時間。So, write triggers to lessen their duration whenever possible. 要確保較短持續時間的方法之一,是在 DML 陳述式變更零個資料列時釋放觸發程序。One way to achieve shorter duration is to release a trigger when a DML statement changes zero rows.

若要針對不會變更任何資料列的命令釋放觸發程序,請使用系統變數 ROWCOUNT_BIGTo release the trigger for a command that doesn't change any rows, employ the system variable ROWCOUNT_BIG.

下列 T-SQL 程式碼片段示範如何針對不會變更任何資料列的命令釋放觸發程序。The following T-SQL code snippet shows how to release the trigger for a command that doesn't change any rows. 此程式碼應該出現在每個 DML 觸發程序的開頭:This code should be present at the beginning of each DML trigger:

IF (ROWCOUNT_BIG() = 0)
RETURN;

DDL 觸發程序的備註Remarks for DDL Triggers

如同標準觸發程序,DDL 觸發程序也會啟動預存程序來回應事件。DDL triggers, like standard triggers, launch stored procedures in response to an event. 但它們並不會為了回應資料表或檢視表的 UPDATE、INSERT 或 DELETE 陳述式而執行,這一點就和標準觸發程序不同。But, unlike standard triggers, they don't run in response to UPDATE, INSERT, or DELETE statements on a table or view. 相反地,其主要執行目的是為了回應資料定義語言 (DDL) 陳述式。Instead, they primarily run in response to data definition language (DDL) statements. 這些陳述式類型包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS。The statement types include CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS. 執行類似 DDL 作業的某些系統預存程序也可能引發 DDL 觸發程序。Certain system stored procedures that carry out DDL-like operations can also fire DDL triggers.

重要

請測試 DDL 觸發程序,以判斷它們對執行之系統預存程序的回應。Test your DDL triggers to determine their responses to system stored procedure execution. 例如,CREATE TYPE 陳述式與 sp_addtype 及 sp_rename 預存程序都會引發針對 CREATE_TYPE 事件建立的 DDL 觸發程序。For example, the CREATE TYPE statement and the sp_addtype and sp_rename stored procedures fire a DDL trigger that's created on a CREATE_TYPE event.

如需 DDL 觸發程序的詳細資訊,請參閱 DDL 觸發程序For more information about DDL triggers, see DDL Triggers.

若是影響區域或全域暫存資料表與預存程序的事件,DDL 觸發程序就不會為了回應這類事件而引發。DDL triggers don't fire in response to events that affect local or global temporary tables and stored procedures.

DDL 觸發程序不像 DML 觸發程序,並不以結構描述為範圍。Unlike DML triggers, DDL triggers aren't scoped to schemas. 因此,您不能使用 OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 等函式來查詢 DDL 觸發程序的相關中繼資料。So, you can't use functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX for querying metadata about DDL triggers. 請改用目錄檢視。Use the catalog views instead. 如需詳細資訊,請參閱取得 DDL 觸發程序的詳細資訊For more information, see Get Information About DDL Triggers.

注意

伺服器範圍的 DDL 觸發程序會出現在 SQL Server Management StudioSQL Server Management Studio 物件總管的 [觸發程序] 資料夾中。Server-scoped DDL triggers appear in the SQL Server Management StudioSQL Server Management Studio Object Explorer in the Triggers folder. 這個資料夾在 [伺服器物件] 資料夾之下。This folder is located under the Server Objects folder. 資料庫範圍的 DDL 觸發程序則會出現在 [資料庫觸發程序] 資料夾中。Database-scoped DDL Triggers appear in the Database Triggers folder. 這個資料夾在對應資料庫的 [可程式性] 資料夾之下。This folder is located under the Programmability folder of the corresponding database.

登入觸發程序Logon Triggers

登入觸發程序會執行預存程序來回應 LOGON 事件。Logon triggers carry out stored procedures in response to a LOGON event. 當使用 SQL ServerSQL Server 執行個體建立使用者工作階段時,就會引發這個事件。This event happens when a user session is established with an instance of SQL ServerSQL Server. 登入觸發程序會在登入驗證階段結束之後、使用者工作階段建立之前引發。Logon triggers fire after the authentication phase of logging in finishes, but before the user session is established. 因此,從觸發程序內產生且通常會傳遞給使用者的所有訊息 (例如錯誤訊息和來自 PRINT 陳述式的訊息),都會轉向至 SQL ServerSQL Server 錯誤記錄檔。So, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL ServerSQL Server error log. 如需詳細資訊,請參閱登入觸發程序For more information, see Logon Triggers.

如果驗證失敗,就不會引發登入觸發程序。Logon triggers don't fire if authentication fails.

登入觸發程序不支援分散式交易。Distributed transactions aren't supported in a logon trigger. 當引發含有分散式交易的登入觸發程序時,就會傳回錯誤 3969。Error 3969 returns when a logon trigger that contains a distributed transaction fire.

停用登入觸發程序Disabling a Logon Trigger

登入觸發程序可以有效地防止所有使用者成功連接到 Database EngineDatabase Engine ,包括 sysadmin 固定伺服器角色的成員。A logon trigger can effectively prevent successful connections to the Database EngineDatabase Engine for all users, including members of the sysadmin fixed server role. 當登入觸發程序防止連接時, sysadmin 固定伺服器角色的成員可以使用專用管理員連接或以最低組態模式 (-f) 啟動 Database EngineDatabase Engine ,藉以進行連接。When a logon trigger is preventing connections, members of the sysadmin fixed server role can connect by using the dedicated administrator connection, or by starting the Database EngineDatabase Engine in minimal configuration mode (-f). 如需詳細資訊,請參閱 Database Engine Service Startup OptionsFor more information, see Database Engine Service Startup Options.

一般觸發程序考量General Trigger Considerations

傳回結果Returning Results

SQL Server 的未來版本將移除從觸發程序傳回結果的功能。The ability to return results from triggers will be removed in a future version of SQL Server. 如果觸發程序會傳回結果集,其可能會導致非專用應用程式發生非預期的行為。Triggers that return result sets may cause unexpected behavior in applications that aren't designed to work with them. 在新的開發工作中,請避免從觸發程序傳回結果集,並計畫修改目前如此運作的應用程式。Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do. 若要避免觸發程序傳回結果集,請將不允許來自觸發程序的結果選項設為 1。To prevent triggers from returning result sets, set the disallow results from triggers option to 1.

登入觸發程序一律不允許傳回結果集,且您無法設定這項行為。Logon triggers always disallow the return of results sets and this behavior isn't configurable. 如果登入觸發程序產生結果集,就無法啟動觸發程序,並引發會拒絕觸發程序的登入嘗試。If a logon trigger generates a result set, the trigger fails to launch and the login attempt that fired the trigger is denied.

多重觸發程序Multiple Triggers

SQL ServerSQL Server 可讓您針對每個 DML、DDL 或 LOGON 事件建立多個觸發程序。lets you create multiple triggers for each DML, DDL, or LOGON event. 例如,如果針對已有 UPDATE 觸發程序的資料表執行 CREATE TRIGGER FOR UPDATE,便會建立其他更新觸發程序。For example, if CREATE TRIGGER FOR UPDATE is run for a table that already has an UPDATE trigger, an additional update trigger is created. 在舊版的 SQL ServerSQL Server 中,每份資料表的每個 INSERT、UPDATE 或 DELETE 資料修改事件都只能有一個觸發程序。In earlier versions of SQL ServerSQL Server, only one trigger for each INSERT, UPDATE, or DELETE data modification event is allowed for each table.

遞迴觸發程序Recursive Triggers

當使用 ALTER DATABASE 來啟用 RECURSIVE_TRIGGERS 設定時,SQL ServerSQL Server 也支援觸發程序的遞迴叫用。SQL ServerSQL Server also supports recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.

遞迴觸發程序有可能產生下列遞迴類型:Recursive triggers enable the following types of recursion to occur:

  • 間接遞迴Indirect recursion

    當進行間接遞迴時,應用程式會更新 T1 資料表。With indirect recursion, an application updates table T1. 這會引發 TR1 觸發程序,更新 T2 資料表。This fires trigger TR1, updating table T2. 接著引發 T2 觸發程序,並更新 T1 資料表。Trigger T2 then fires and updates table T1.

  • 直接遞迴Direct recursion

    在直接遞迴中,應用程式會更新 T1 資料表。In direct recursion, the application updates table T1. 這會引發 TR1 觸發程序,更新 T1 資料表。This fires trigger TR1, updating table T1. 由於更新了 T1 資料表,因此,又會引發 TR1 觸發程序,依此類推。Because table T1 was updated, trigger TR1 fires again, and so on.

下列範例會使用間接和直接觸發程序遞迴。它假設 T1 資料表定義了 TR1 和 TR2 這兩個更新觸發程序。The following example uses both indirect and direct trigger recursion Assume that two update triggers, TR1 and TR2, are defined on table T1. TR1 觸發程序會遞迴更新 T1 資料表。Trigger TR1 updates table T1 recursively. UPDATE 陳述式會執行 TR1 和 TR2 各一次。An UPDATE statement runs each TR1 and TR2 one time. 此外,啟動 TR1 時會觸發執行 TR1 (遞迴) 和 TR2。Additionally, the launch of TR1 triggers the execution of TR1 (recursively) and TR2. 特定觸發程序的 inserted 和 deleted 資料表包含只對應於呼叫觸發程序之 UPDATE 陳述式的資料表。The inserted and deleted tables for a specific trigger contain rows that correspond only to the UPDATE statement that invoked the trigger.

注意

只有在利用 ALTER DATABASE 來啟用 RECURSIVE_TRIGGERS 設定時,才會發生上述行為。The previous behavior occurs only if the RECURSIVE_TRIGGERS setting is enabled by using ALTER DATABASE. 針對特定事件定義的多個觸發程序,並沒有任何定義的執行順序。There's no defined order in which multiple triggers defined for a specific event are run. 每個觸發程序都應該是獨立自足的。Each trigger should be self-contained.

停用 RECURSIVE_TRIGGERS 設定只會防止直接遞迴。Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. 如果也要停用間接遞迴,請利用 sp_configure,將 nested triggers 伺服器選項設為 0。To disable indirect recursion also, set the nested triggers server option to 0 by using sp_configure.

如果有任何觸發程序執行 ROLLBACK TRANSACTION,則不論巢狀層級為何,都不會執行任何觸發程序。If any one of the triggers carries out a ROLLBACK TRANSACTION, regardless of the nesting level, no more triggers are run.

巢狀觸發程序Nested Triggers

您最多可以將觸發程序巢狀化為 32 個層級。You can nest triggers to a maximum of 32 levels. 如果觸發程序變更了一份資料表,但這份資料表上有其他觸發程序,就會啟動第二個觸發程序,而第二個觸發程序可能會再呼叫第三個觸發程序,依此類推。If a trigger changes a table on which there's another trigger, the second trigger activates and can then call a third trigger, and so on. 如果鏈結中的任何觸發程序造成無限迴圈,就會超出巢狀層級,並取消觸發程序。If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. Transact-SQLTransact-SQL 觸發程序參考 CLR 常式、類型或彙總來啟動受控碼時,這項參考也會計入 32 層巢狀限制的一層。When a Transact-SQLTransact-SQL trigger launches managed code by referencing a CLR routine, type, or aggregate, this reference counts as one level against the 32-level nesting limit. 若是從受控碼內叫用的方法,則不計入這項限制。Methods invoked from within managed code don't count against this limit.

若要停用巢狀觸發程序,請將 sp_configure 的 nested triggers 選項設成 0 (關閉)。To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). 預設設定可支援巢狀觸發程序。The default configuration supports nested triggers. 如果關閉巢狀觸發程序,遞迴觸發程序也會停用,不論使用 ALTER DATABASE 設定的 RECURSIVE_TRIGGERS 設定為何,都是如此。If nested triggers are off, recursive triggers are also disabled, despite the RECURSIVE_TRIGGERS setting that's set by using ALTER DATABASE.

即使 [巢狀觸發程序] 伺服器設定選項為 0,仍會引發 INSTEAD OF 觸發程序內部的第一個巢狀 AFTER 觸發程序。The first AFTER trigger nested inside an INSTEAD OF trigger fires even if the nested triggers server configuration option is 0. 不過,在此設定下,不會引發後續的 AFTER 觸發程序。But, under this setting, the later AFTER triggers don't fire. 檢閱應用程式的巢狀觸發程序,以判斷當 [巢狀觸發程序] 伺服器設定選項設為 0 時,應用程式是否會遵循您的商務規則。Review your applications for nested triggers to determine if the applications follow your business rules when the nested triggers server configuration option is set to 0. 若否,請進行適當的修改。If not, make the appropriate modifications.

延遲名稱解析Deferred Name Resolution

SQL ServerSQL Server 允許 Transact-SQLTransact-SQL 預存程序、觸發程序和批次參考在編譯時期並不存在的資料表。allows for Transact-SQLTransact-SQL stored procedures, triggers, and batches to refer to tables that don't exist at compile time. 這項功能稱為延遲名稱解析。This ability is called deferred name resolution.

權限Permissions

若要建立 DML 觸發程序,必須具備要建立觸發程序之資料表或檢視表的 ALTER 權限。To create a DML trigger, it requires ALTER permission on the table or view on which the trigger is being created.

若要建立伺服器範圍 (ON ALL SERVER) 的 DDL 觸發程序或登入觸發程序,需要伺服器的 CONTROL SERVER 權限。To create a DDL trigger with server scope (ON ALL SERVER) or a logon trigger, requires CONTROL SERVER permission on the server. 若要建立資料庫範圍 (ON DATABASE) 的 DDL 觸發程序,需要目前資料庫的 ALTER ANY DATABASE DDL TRIGGER 權限。To create a DDL trigger with database scope (ON DATABASE), requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

範例Examples

A.A. 使用包含提示訊息的 DML 觸發程序Using a DML trigger with a reminder message

當任何人嘗試在 AdventureWorks2012AdventureWorks2012 資料庫的 Customer 資料表中新增或變更資料時,以下 DML 觸發程序會向用戶端列印一則訊息。The following DML trigger prints a message to the client when anyone tries to add or change data in the Customer table in the AdventureWorks2012AdventureWorks2012 database.

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

B.B. 使用包含提示電子郵件訊息的 DML 觸發程序Using a DML trigger with a reminder e-mail message

MaryM 資料表有了改變時,以下範例會向指定的人 (Customer) 傳送一則電子郵件訊息。The following example sends an e-mail message to a specified person (MaryM) when the Customer table changes.

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.C. 使用 DML AFTER 觸發程序在 PurchaseOrderHeader 與 Vendor 資料表之間執行商務規則Using a DML AFTER trigger to enforce a business rule between the PurchaseOrderHeader and Vendor tables

由於 CHECK 條件約束只會參考定義了資料行層級或資料表層級條件約束的資料行,因此您必須將任何跨資料表的條件約束 (此案例為商務規則) 定義為觸發程序。Because CHECK constraints reference only the columns on which the column-level or table-level constraint is defined, you must define any cross-table constraints (in this case, business rules) as triggers.

以下範例會在 AdventureWorks2012 資料庫中建立 DML 觸發程序。The following example creates a DML trigger in the AdventureWorks2012 database. 試圖在 PurchaseOrderHeader 資料表中插入新的採購單時,這個觸發程序會檢查確認供應商的信用評等良好 (非 5 顆星)。This trigger checks to make sure the credit rating for the vendor is good (not 5) when there's an attempt to insert a new purchase order into the PurchaseOrderHeader table. 為了取得供應商的信用評等,必須參考 Vendor 資料表。To get the credit rating of the vendor, the Vendor table must be referenced. 如果信用評等太低,即會顯示訊息,且不會執行插入動作。If the credit rating is too low, a message appears and the insertion doesn't happen.

-- 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 *  
           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.D. 使用僅限於資料庫的 DDL 觸發程序Using a database-scoped DDL trigger

以下範例利用 DDL 觸發程序避免卸除資料庫中的同義字。The following example uses a DDL trigger to prevent any synonym in a database from being dropped.

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.E. 使用僅限於伺服器的 DDL 觸發程序Using a server-scoped DDL trigger

以下範例會在目前伺服器執行個體出現任何 CREATE DATABASE 事件時,利用 DDL 觸發程序來列印訊息,並利用 EVENTDATA 函數來擷取對應 Transact-SQLTransact-SQL 陳述式的文字。The following example uses a DDL trigger to print a message if any CREATE DATABASE event occurs on the current server instance, and uses the EVENTDATA function to retrieve the text of the corresponding Transact-SQLTransact-SQL statement. 如需更多在 DDL 觸發程序中使用 EVENTDATA 的範例,請參閱使用 EVENTDATA 函式For more examples that use EVENTDATA in DDL triggers, see Use the EVENTDATA Function.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

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.F. 使用登入觸發程序Using a logon trigger

在以下登入觸發程序範例中,如果已有三個使用者工作階段以 login_test 登入執行,則嘗試以該登入的成員身分登入 SQL ServerSQL Server 時會遭拒。The following logon trigger example denies an attempt to log in to SQL ServerSQL Server as a member of the login_test login if there are already three user sessions running under that login.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

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.G. 檢視引發觸發程序的事件Viewing the events that cause a trigger to fire

以下範例會查詢 sys.triggerssys.trigger_events 目錄檢視,判別引發 safety 觸發程序的 Transact-SQLTransact-SQL 語言事件。The following example queries the sys.triggers and sys.trigger_events catalog views to determine which Transact-SQLTransact-SQL language events cause trigger safety to fire. 觸發程序 safety 是在上面的範例 'D' 中所建立。The trigger, safety, is created in example 'D', found above.

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  

另請參閱See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
ALTER TRIGGER (Transact-SQL) ALTER TRIGGER (Transact-SQL)
COLUMNS_UPDATED (Transact-SQL) COLUMNS_UPDATED (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
DROP TRIGGER (Transact-SQL) DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL) ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL) DISABLE TRIGGER (Transact-SQL)
TRIGGER_NESTLEVEL (Transact-SQL) TRIGGER_NESTLEVEL (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL) sys.dm_sql_referencing_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sp_help (Transact-SQL) sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL) sp_helptrigger (Transact-SQL)
sp_helptext (Transact-SQL) sp_helptext (Transact-SQL)
sp_rename (Transact-SQL) sp_rename (Transact-SQL)
sp_settriggerorder (Transact-SQL) sp_settriggerorder (Transact-SQL)
UPDATE() (Transact-SQL) UPDATE() (Transact-SQL)
取得關於 DML 觸發程序的詳細資訊 Get Information About DML Triggers
取得 DDL 觸發程序的資訊 Get Information About DDL Triggers
sys.triggers (Transact-SQL) sys.triggers (Transact-SQL)
sys.trigger_events (Transact-SQL) sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL) sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL) sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL) sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)sys.server_assembly_modules (Transact-SQL)