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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) 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 executes when an event occurs in the database server. 當使用者試圖透過資料操作語言 (DML) 事件來修改資料時,便會執行 DML 觸發程序。DML triggers execute 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 is fired, regardless of whether or not any table rows are affected. 如需詳細資訊,請參閱 DML TriggersFor more information, see DML Triggers.

DDL 觸發程序會根據各種資料定義語言 (DDL) 事件的不同而執行。DDL triggers execute 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 is raised when a user's session is being established. 您可以直接從 Transact-SQLTransact-SQL 陳述式建立觸發程序,也可以使用 MicrosoftMicrosoft .NET Framework.NET Framework 通用語言執行平台 (CLR) 所建立的組件方法來建立觸發程序,再將其上傳到 [SQL Server]SQL Server 執行個體。Triggers can be created 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 Server]SQL Server. [SQL Server]SQL Server 允許針對任何特定陳述式建立多個觸發程序。allows for creating 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 topic. 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 Server]SQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始)。Applies to: Azure SQL DatabaseSQL Database, [SQL Server]SQL 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 觸發程序所屬的結構描述名稱。Is 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 are created. 您不能為 DDL 或登入觸發程序指定 schema_nameschema_name cannot be specified for DDL or logon triggers.

trigger_nametrigger_name
這是觸發程序的名稱。Is the name of the trigger. trigger_name 必須符合識別碼的規則,不過 trigger_name 開頭不能是 # 或 ##。A trigger_name must comply with the rules for identifiers, except that trigger_name cannot start with # or ##.

table | viewtable | view
這是執行 DML 觸發程序的資料表或檢視,有時稱為觸發程序資料表或觸發程序檢視。Is the table or view on which the DML trigger is executed and 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 觸發程序可以參考檢視。A view can be referenced only by an INSTEAD OF trigger. 在本機或全域暫存資料表上,不能定義 DML 觸發程序。DML triggers cannot be defined 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 陳述式的文字。Obfuscates the text of the CREATE TRIGGER statement. 使用 WITH ENCRYPTION 可防止在 [SQL Server]SQL Server 複寫中發行這個觸發程序。Using WITH ENCRYPTION prevents the trigger from being published as part of [SQL Server]SQL Server replication. CLR 觸發程序不能指定 WITH ENCRYPTION。WITH ENCRYPTION cannot be specified for CLR triggers.

EXECUTE ASEXECUTE AS
指定用來執行這個觸發程序的安全性內容。Specifies the security context under which the trigger is executed. 可讓您控制 [SQL Server]SQL Server 執行個體要利用哪個使用者帳戶來驗證觸發程序所參考之任何資料庫物件的權限。Enables you to control which user account the instance of [SQL Server]SQL 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 that are referenced by a trigger cannot be dropped or altered.

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

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

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

檢視不能定義 AFTER 觸發程序。AFTER triggers cannot be defined on views.

INSTEAD OFINSTEAD OF
指定要執行 DML 觸發程序,而「不是」觸發的 SQL 陳述式,因此會覆寫觸發陳述式的動作。Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. DDL 或登入觸發程序不能指定 INSTEAD OF。INSTEAD OF cannot be specified for DDL or logon triggers.

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

使用 WITH CHECK OPTION 的可更新檢視上不可使用 INSTEAD OF 觸發程序。INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. 當 INSTEAD OF 觸發程序加入所指定之可更新檢視 WITH CHECK OPTION 時, [SQL Server]SQL Server 會產生錯誤。 [SQL Server]SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. 使用者必須在定義 INSTEAD OF 觸發程序之前,利用 ALTER VIEW 來移除這個選項。The user must 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 is tried against this table or view. 至少必須指定一個選項。At least one option must be specified. 您可以在觸發程序定義中,依照任何順序來指定這些選項的任何組合。Any combination of these options in any order is allowed in the trigger definition.

對於 INSTEAD OF 觸發程序而言,含有指定了重疊顯示動作 ON DELETE 的參考關聯性之資料表不能使用 DELETE 選項。For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. 同樣地,含有指定了串聯動作 ON UPDATE 的參考關聯性之資料表也不能使用 UPDATE 選項。Similarly, the UPDATE option is not 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 cannot be used with INSTEAD OF triggers or if AFTER trigger is explicitly stated. 只有在為了與舊版相容而指定 FOR,且不含 INSTEAD OF 或 AFTER 時,才能使用 WITH APPEND。WITH APPEND can be used only when FOR is specified, without INSTEAD OF or AFTER, for backward compatibility reasons. 如果指定了 EXTERNAL NAME (也就是說,觸發程序是一個 CLR 觸發程序),便不能指定 WITH APPEND。WITH APPEND cannot be specified if EXTERNAL NAME is specified (that is, if the trigger is a CLR trigger).

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

event_groupevent_group
這是 Transact-SQLTransact-SQL 語言事件之預設定義群組的名稱。Is the name of a predefined grouping of Transact-SQLTransact-SQL language events. 在執行屬於 event_group 的任何 Transact-SQLTransact-SQL 語言事件之後,便會引發 DDL 觸發程序。The DDL trigger fires after execution 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 should not be executed when a replication agent modifies the table that is involved in the trigger.

sql_statementsql_statement
這是觸發程序條件和動作。Is 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 performed.

當嘗試作業時, 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 not 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 are 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 Server]SQL Server 允許使用資料表或檢視的 INSTEAD OF 觸發程序來更新 textntextimage 資料行。allows for the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

重要

MicrosoftMicrosoft [SQL Server]SQL Server 的未來版本將會移除 ntexttextimage 資料類型。ntext, text, and image data types will be removed in a future version of MicrosoftMicrosoft [SQL Server]SQL 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 Server]SQL Server 識別碼,且必須以類別的形式存在於可以顯示的組件中。class_name must be a valid [SQL Server]SQL 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 cannot be a nested class.

注意

依預設,會關閉 [SQL Server]SQL Server 執行 CLR 程式碼的能力。By default, the ability of [SQL Server]SQL Server to run CLR code is off. 您可以建立、修改及卸除參考受控碼模組的資料庫物件,但除非您使用 sp_configure 來啟用 clr enabled 選項;否則,這些參考就不會在 [SQL Server]SQL Server 中執行。You can create, modify, and drop database objects that reference managed code modules, but these references will not execute in an instance of [SQL Server]SQL 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 Server]SQL Server 利用 ALTER TABLE 及 CREATE TABLE 陳述式提供宣告式參考完整性 (DRI)。provides declarative referential integrity (DRI) through the ALTER TABLE and CREATE TABLE statements. 不過,DRI 並不提供跨資料庫的參考完整性。However, DRI does not 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 are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. 如果違反條件約束,便會回復 INSTEAD OF 觸發程序動作,且不會引發 AFTER 觸發程序。If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.

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

如果 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 the order value must be reset by using sp_settriggerorder.

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

如果資料表所定義的 INSTEAD OF 觸發程序是針對通常會再引發 INSTEAD OF 觸發程序的資料表來執行陳述式,這個觸發程序並不稱為遞迴運作。If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. 相反地,此時會依照資料表沒有 INSTEAD OF 觸發程序的方式來處理陳述式,且會啟動條件約束作業的鏈結,執行 AFTER 觸發程序。Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. 例如,如果觸發程序定義為資料表的 INSTEAD OF INSERT 觸發程序,且這個觸發程序會在相同的資料表上執行 INSERT 陳述式,INSTEAD OF 觸發程序所執行的 INSERT 陳述式便不會重新呼叫這個觸發程序。For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. 觸發程序所執行的 INSERT 會啟動執行條件約束動作及引發定義給資料表之任何 AFTER INSERT 觸發程序的程序。The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.

如果檢視所定義的 INSTEAD OF 觸發程序是針對通常會再引發 INSTEAD OF 觸發程序的檢視來執行陳述式,它並不稱為遞迴運作。If an INSTEAD OF trigger defined on a view executes a statement against the view that would ordinarily fire the INSTEAD OF trigger again, it is 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 觸發程序,且這個觸發程序會執行參考相同檢視的 UPDATE 陳述式,INSTEAD OF 觸發程序所執行的 UPDATE 陳述式便不會重新呼叫這個觸發程序。For example, if a trigger is defined as an INSTEAD OF UPDATE trigger for a view, and the trigger executes an UPDATE statement referencing the same view, the UPDATE statement executed by the INSTEAD OF trigger does not call the trigger again. 觸發程序所執行的 UPDATE 是依照檢視沒有 INSTEAD OF 觸發程序的方式,針對檢視來處理的。The UPDATE executed by the trigger is processed against the view as if the view did not 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 perform 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 are performed on multiple columns and 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 cannot 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 having results returned to an application because of a trigger firing, do not include either SELECT statements that return results or statements that perform variable assignment in a trigger. 包括將結果傳回給使用者的 SELECT 陳述式或執行變數指派的陳述式之觸發程序,需要特殊處理;這些傳回的結果必須寫入允許修改觸發程序資料表的每個應用程式中。A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written 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 does not activate a trigger because the operation does not log individual row deletions. 但是,只有具有執行 TRUNCATE TABLE 陳述式之權限的使用者才需要擔心這種方式會不小心阻止 DELETE 觸發程序。However, only those users with permissions to execute a TRUNCATE TABLE statement need be concerned about inadvertently circumventing a DELETE trigger this way.

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

DML 觸發程序中不可使用下列 Transact-SQLTransact-SQL 陳述式:The following Transact-SQLTransact-SQL statements are not 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 are not allowed inside the body of a DML trigger when it is used against the table or view that is 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 Server]SQL Server 不支援系統資料表的使用者自訂觸發程序,因此,我們建議您不要在系統資料表上建立使用者自訂觸發程序。Because [SQL Server]SQL 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 are open, they lock resources. 鎖定會留在原處,直到確認 (使用 COMMIT) 或拒絕 (使用 ROLLBACK) 交易為止。The lock will remain 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 will be blocked. 因此,觸發程序的撰寫應該盡可能減少其持續時間。Therefore, triggers should be written in a way to decrease their duration whenever possible. 達成此目的的方法之一,是在 DML 陳述式變更 0 個資料列時釋放觸發程序。One way to achieve this is to release a trigger when a DML statement changes 0 rows.

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

下列 T-SQL 程式碼片段會達到這個目的,並且應該出現在每個 DML 觸發程序的開頭:The following T-SQL code snippet will achieve this, and 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, execute stored procedures in response to an event. 但它們不像標準觸發程序,並不會為了回應資料表或檢視的 UPDATE、INSERT 或 DELETE 陳述式而執行。But unlike standard triggers, they do not execute in response to UPDATE, INSERT, or DELETE statements on a table or view. 相反地,執行它們主要是為了回應資料定義語言 (DDL) 陳述式。Instead, they primarily execute in response to data definition language (DDL) statements. 其中包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 陳述式。These include CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements. 執行類似 DDL 作業的某些系統預存程序也可能引發 DDL 觸發程序。Certain system stored procedures that perform 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 will fire a DDL trigger that is created on a CREATE_TYPE event.

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

對於影響區域或全域暫存資料表與預存程序的事件,DDL 觸發程序不會為了回應它而引發。DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.

DDL 觸發程序不像 DML 觸發程序,並不以結構描述為範圍。Unlike DML triggers, DDL triggers are not scoped to schemas. 因此,OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 等函數無法用來查詢有關 DDL 觸發程序的中繼資料。Therefore, functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX cannot be used for querying metadata about DDL triggers. 請改用目錄檢視。Use the catalog views instead. 如需詳細資訊,請參閱取得 DDL 觸發程序的詳細資訊For more information, see Get Information About DDL Triggers.

注意

伺服器範圍的 DDL 觸發程序會出現在 Transact-SQLSQL Server Management Studio 物件總管的 [觸發程序] 資料夾中。Server-scoped DDL triggers appear in the Transact-SQLSQL 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 execute stored procedures in response to a LOGON event. [SQL Server]SQL Server執行個體建立使用者工作階段時,就會引發這個事件。This event is raised when a user session is established with an instance of [SQL Server]SQL Server. 登入觸發程序會在登入驗證階段結束之後,但在使用者工作階段實際建立之前引發。Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. 因此,從觸發程序內產生且一般會顯示給使用者的所有訊息,例如錯誤訊息和來自 PRINT 陳述式的訊息,都會轉至 [SQL Server]SQL Server 錯誤記錄檔。Therefore, 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 Server]SQL Server error log. 如需詳細資訊,請參閱登入觸發程序For more information, see Logon Triggers.

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

登入觸發程序不支援分散式交易。Distributed transactions are not supported in a logon trigger. 若引發含有分散式交易的登入觸發程序,會傳回錯誤 3969。Error 3969 is returned when a logon trigger containing a distributed transaction is fired.

停用登入觸發程序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 are not designed to work with them. 請在新的開發工作中避免從觸發程序傳回結果集,並計畫修改目前如此運作的應用程式。Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this. 若要避免觸發程序傳回結果集,請將不允許來自觸發程序的結果選項設為 1。To prevent triggers from returning result sets, set the disallow results from triggers option to 1.

登入觸發程序不允許傳回結果集,而且您無法設定這項行為。Logon triggers always disallow results sets to be returned and this behavior is not configurable. 如果登入觸發程序產生結果集,此觸發程序將無法執行,而引發觸發程序的登入嘗試將被拒絕。If a logon trigger does generate a result set, the trigger fails to execute and the login attempt that fired the trigger is denied.

多重觸發程序Multiple Triggers

[SQL Server]SQL Server 允許每個 DML、DDL 或 LOGON 事件各建立多個觸發程序。allows for multiple triggers to be created for each DML, DDL, or LOGON event. 例如,如果針對已有 UPDATE 觸發程序的資料表來執行 CREATE TRIGGER FOR UPDATE,便會建立其他更新觸發程序。For example, if CREATE TRIGGER FOR UPDATE is executed for a table that already has an UPDATE trigger, an additional update trigger is created. 在舊版的 [SQL Server]SQL Server 中,每份資料表的每個 INSERT、UPDATE 或 DELETE 資料修改事件都只能有一個觸發程序。In earlier versions of [SQL Server]SQL 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 Server]SQL Server 也允許觸發程序的遞迴叫用。 [SQL Server]SQL Server also allows for 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 資料表。In this scenario, trigger T2 then fires and updates table T1.

  • 直接遞迴Direct recursion

    當進行直接遞迴時,應用程式會更新 T1 資料表。With 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 executes each TR1 and TR2 one time. 另外,執行 TR1 會觸發執行 TR1 (遞迴) 和 TR2。Additionally, the execution 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 is no defined order in which multiple triggers defined for a specific event are executed. 每個觸發程序都應該是獨立自足的。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 performs a ROLLBACK TRANSACTION, regardless of the nesting level, no more triggers are executed.

巢狀觸發程序Nested Triggers

觸發程序的巢狀結構,最多可達 32 層。Triggers can be nested to a maximum of 32 levels. 如果觸發程序變更了一份資料表,且這份資料表有另一個觸發程序,就會啟動第二個觸發程序,之後這個觸發程序可能會呼叫第三個觸發程序,依此類推。If a trigger changes a table on which there is another trigger, the second trigger is activated 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 常式、類型或彙總來執行 Managed 程式碼時,在 32 層的巢狀限制中,這項參考算是一層。When a Transact-SQLTransact-SQL trigger executes managed code by referencing a CLR routine, type, or aggregate, this reference counts as one level against the 32-level nesting limit. 從 Managed 程式碼內叫用的方法,不列入這項限制。Methods invoked from within managed code do not 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 allows for nested triggers. 如果關閉巢狀觸發程序,遞迴觸發程序也會停用,不論使用 ALTER DATABASE 設定的 RECURSIVE_TRIGGERS 設定為何,都是如此。If nested triggers are off, recursive triggers are also disabled, regardless of the RECURSIVE_TRIGGERS setting 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 set to 0. 不過,在此設定下,後續的 AFTER 觸發程序不會引發。However, under this setting, later AFTER triggers do not fire. 建議您檢閱應用程式中是否有巢狀觸發程序,以判斷當 [巢狀觸發程序] 伺服器設定選項設定為 0 時,這些應用程式的新行為是否符合您的商務規則,然後進行適當的修改。We recommend that you review your applications for nested triggers to determine whether the applications comply with your business rules with regard to this behavior when the nested triggers server configuration option is set to 0, and then make appropriate modifications.

延遲名稱解析Deferred Name Resolution

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

[權限]Permissions

若要建立 DML 觸發程序,需要建立觸發程序的資料表或檢視的 ALTER 權限。To create a DML trigger 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 can reference only the columns on which the column-level or table-level constraint is defined, any cross-table constraints (in this case, business rules) must be defined 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 an attempt is made to insert a new purchase order into the PurchaseOrderHeader table. 為了取得供應商的信用等級,必須參考 Vendor 資料表。To obtain the credit rating of the vendor, the Vendor table must be referenced. 如果信用等級太低,將會顯示訊息,且不會執行插入動作。If the credit rating is too low, a message is displayed and the insertion does not execute.

-- 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 drop 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 Server]SQL Server 時會遭拒。The following logon trigger example denies an attempt to log in to [SQL Server]SQL 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)