ALTER TRIGGER (Transact-SQL)ALTER TRIGGER (Transact-SQL)

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

修改 CREATE TRIGGER 陳述式先前所建立之 DML、DDL 或登入觸發程序的定義。Modifies the definition of a DML, DDL, or logon trigger that was previously created by the CREATE TRIGGER statement. 觸發程序是使用 CREATE TRIGGER 建立的。Triggers are created by using CREATE TRIGGER. 您可以直接從 Transact-SQLTransact-SQL 陳述式建立觸發程序,也可以使用 MicrosoftMicrosoft .NET Framework.NET Framework 通用語言執行平台 (CLR) 所建立的組件方法來建立觸發程序,再將其上傳到 SQL ServerSQL Server 執行個體。They 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 ServerSQL Server. 如需用於 ALTER TRIGGER 陳述式的參數詳細資訊,請參閱 CREATE TRIGGER (Transact-SQL)For more information about the parameters that are used in the ALTER TRIGGER statement, see CREATE TRIGGER (Transact-SQL).

主題連結圖示 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)  

ALTER TRIGGER schema_name.trigger_name   
ON  ( table | view )   
[ WITH <dml_trigger_option> [ ,...n ] ]  
 ( FOR | AFTER | INSTEAD OF )   
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }   
[ NOT FOR REPLICATION ]   
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <method specifier>   
[ ; ] }   
  
<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ <EXECUTE AS Clause> ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name  
  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table 
-- (DML Trigger on memory-optimized tables)  

ALTER TRIGGER schema_name.trigger_name   
ON  ( table  )   
[ WITH <dml_trigger_option> [ ,...n ] ]  
 ( FOR | AFTER )   
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }   
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)  
  
ALTER TRIGGER trigger_name   
ON { DATABASE | ALL SERVER }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type [ ,...n ] | event_group }   
AS { sql_statement [ ; ] | EXTERNAL NAME <method specifier>   
[ ; ] }  
}   
  
<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ <EXECUTE AS Clause> ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name  
  
-- Trigger on a LOGON event (Logon Trigger)  

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 ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name  
-- Azure SQL Database Syntax   
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)   
  
ALTER TRIGGER schema_name. trigger_name   
ON (table | view )   
 [ WITH <dml_trigger_option> [ ,...n ] ]   
 ( FOR | AFTER | INSTEAD OF )   
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }   
AS { sql_statement [ ; ] [...n ] }   
  
<dml_trigger_option> ::=   
    [ <EXECUTE AS Clause> ]   
  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE statement (DDL Trigger)   
  
ALTER TRIGGER trigger_name   
ON { DATABASE }   
 [ WITH <ddl_trigger_option> [ ,...n ] ]   
{ FOR | AFTER } { event_type [ ,...n ] | event_group }   
AS { sql_statement   
[ ; ] }  
}   
  
<ddl_trigger_option> ::=   
    [ <EXECUTE AS Clause> ]  

引數Arguments

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. 只有當 DML 觸發程序和其對應資料表或檢視都屬於預設結構描述時,schema**_name 才是選擇性的。schema**_name is optional only if the DML trigger and its corresponding table or view belong to the default schema. 您不能為 DDL 或登入觸發程序指定 schema_nameschema_name cannot be specified for DDL or logon triggers.

trigger_nametrigger_name
這是要修改的現有觸發程序。Is the existing trigger to modify.

table | viewtable | view
這是執行 DML 觸發程序的資料表或檢視。Is the table or view on which the DML trigger is executed. 您可以選擇性地指定資料表或檢視的完整名稱。Specifying the fully-qualified name of the table or view is optional.

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.

加密包含 ALTER TRIGGER 陳述式文字的 sys.syscommentssys.sql_modules 項目。Encrypts the sys.syscommentssys.sql_modules entries that contain the text of the ALTER 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 cannot be specified for CLR triggers.

注意

如果觸發程序是利用 WITH ENCRYPTION 來建立的,就必須在 ALTER TRIGGER 陳述式中重新指定它,這個選項才會維持啟用狀態。If a trigger is created by using WITH ENCRYPTION, it must be specified again in the ALTER TRIGGER statement for this option to remain enabled.

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

如需詳細資訊,請參閱 EXECUTE AS 子句 (Transact-SQL)For more information, see EXECUTE 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.

AFTERAFTER
指定只在順利執行觸發的 SQL 陳述式時,才引發觸發程序。Specifies that the trigger is fired only after the triggering SQL statement is executed successfully. 所有參考串聯動作和條件約束檢查也都必須成功之後,才會引發這個觸發程序。All referential cascade actions and constraint checks also must have been successful before this trigger fires.

如果只指定 FOR 關鍵字,AFTER 便是預設值。AFTER is the default, if only the FOR keyword is specified.

只有資料表可以定義 DML AFTER 觸發程序。DML AFTER triggers may be defined only on tables.

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 views created by using WITH CHECK OPTION. 將 INSTEAD OF 觸發程序加入已指定 WITH CHECK OPTION 的檢視表時,SQL ServerSQL Server 會產生錯誤。SQL ServerSQL Server raises an error when an INSTEAD OF trigger is added to a view for which WITH CHECK OPTION was specified. 使用者必須在定義 INSTEAD OF 觸發程序之前,利用 ALTER VIEW 來移除這個選項。The user must remove that option using ALTER VIEW before defining the INSTEAD OF trigger.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } | { [INSERT ] [ , ] [ UPDATE ] }{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } | { [INSERT ] [ , ] [ UPDATE ] }
指定當試圖針對這份資料表或檢視表來執行資料修改陳述式時,資料修改陳述式會啟動 DML 觸發程序。Specifies the data modification statements, when tried against this table or view, activate the DML trigger. 至少必須指定一個選項。At least one option must be specified. 您可以在觸發程序定義中,依照任何順序來指定這些選項的任何組合。Any combination of these in any order is allowed in the trigger definition. 如果指定了多個選項,請用逗號來分開這些選項。If more than one option is specified, separate the options with commas.

對於 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. 如需詳細資訊,請參閱 ALTER TABLE (Transact-SQL)For more information, see ALTER TABLE (Transact-SQL).

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. 在完成執行 ALTER TRIGGER 之後,您也可以將其所涵蓋的事件類型新增至 sys.trigger_events 目錄檢視,以將 event_group 作為巨集。After ALTER 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 involved in the trigger.

sql_statementsql_statement
這是觸發程序條件和動作。Is the trigger conditions and actions.

針對經記憶體最佳化資料表上的觸發程序,唯一允許的最上層 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.

EXTERNAL NAME <method_specifier>EXTERNAL NAME <method_specifier>
適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

指定繫結觸發程序之組件的方法。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. 這個類別不能是巢狀類別。The class cannot be a nested class.

RemarksRemarks

如需 ALTER TRIGGER 的詳細資訊,請參閱 CREATE TRIGGER (Transact-SQL)中的<備註>一節。For more information about ALTER TRIGGER, see Remarks in CREATE TRIGGER (Transact-SQL).

注意

自主資料庫無法使用 EXTERNAL_NAME 和 ON_ALL_SERVER 選項。The EXTERNAL_NAME and ON_ALL_SERVER options are not available in a contained database.

DML 觸發程序DML Triggers

ALTER TRIGGER 支援透過資料表和檢視表上的 INSTEAD OF 觸發程序來手動更新檢視。ALTER TRIGGER supports manually updatable views through INSTEAD OF triggers on tables and views. SQL ServerSQL Server 會對所有觸發程序種類 (AFTER、INSTEAD-OF) 都以相同方式來套用 ALTER TRIGGER。applies ALTER TRIGGER the same way for all kinds of triggers (AFTER, INSTEAD-OF).

您可以利用 sp_settriggerorder 來指定資料表所要執行的第一個和最後一個 AFTER 觸發程序。The first and last AFTER triggers to be executed on a table can be specified by using sp_settriggerorder. 資料表只能指定第一個和最後一個 AFTER 觸發程序各一個。Only one first and one last AFTER trigger 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 觸發程序作業會檢查觸發陳述式的效果,也會檢查觸發陳述式所造成的所有參考串聯 UPDATE 和 DELETE 動作。The AFTER trigger operation checks for the effects of the triggering statement and also all referential cascade UPDATE and DELETE actions that are caused by the triggering statement.

當因為父資料表 DELETE 的 CASCADE 結果而造成子系或參考資料表的 DELETE 動作時,會在這份子資料表上定義 DELETE 的 INSTEAD OF 觸發程序,而且觸發程序會被忽略,且會執行 DELETE 動作。When a DELETE action to a child or referencing table is the result of a CASCADE on a DELETE from the parent table, and an INSTEAD OF trigger on DELETE is defined on that child table, the trigger is ignored and the DELETE action is executed.

DDL 觸發程序DDL Triggers

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

登入觸發程序Logon Triggers

Azure SQL DatabaseAzure SQL Database 不支援登入事件的觸發程序。does not support triggers on logon events.

權限Permissions

變更 DML 觸發程序需要定義觸發程序的資料表或檢視表的 ALTER 權限。To alter a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

若要變更以伺服器範圍 (ON ALL SERVER) 定義的 DDL 觸發程序或登入觸發程序,則需要伺服器的 CONTROL SERVER 權限。To alter a DDL trigger defined 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 alter a DDL trigger defined with database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

範例Examples

下列範例會在 AdventureWorks 2012 資料庫中建立 DML 觸發程序,當使用者試圖於 SalesPersonQuotaHistory 資料表中新增或變更資料時,即可將使用者定義訊息列印至用戶端。The following example creates a DML trigger in the AdventureWorks 2012 database, that prints a user-defined message to the client when a user tries to add or change data in the SalesPersonQuotaHistory table. 之後,會利用 ALTER TRIGGER 來修改觸發程序,只在 INSERT 活動上套用觸發程序。The trigger is then modified by using ALTER TRIGGER to apply the trigger only on INSERT activities. 這個觸發程序很有用,因為它會提醒使用者在這份資料表中更新或插入資料列,以便同時通知 Compensation 部門。This trigger is helpful because it reminds the user that updates or inserts rows into this table to also notify the Compensation department.

CREATE TRIGGER Sales.bonus_reminder  
ON Sales.SalesPersonQuotaHistory  
WITH ENCRYPTION  
AFTER INSERT, UPDATE   
AS RAISERROR ('Notify Compensation', 16, 10);  
GO  

-- Now, change the trigger.  
ALTER TRIGGER Sales.bonus_reminder  
ON Sales.SalesPersonQuotaHistory  
AFTER INSERT  
AS RAISERROR ('Notify Compensation', 16, 10);  
GO  

另請參閱See Also

DROP TRIGGER (Transact-SQL) DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL) ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL) DISABLE TRIGGER (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
sp_helptrigger (Transact-SQL) sp_helptrigger (Transact-SQL)
建立預存程序 Create a Stored Procedure
sp_addmessage (Transact-SQL) sp_addmessage (Transact-SQL)
交易 Transactions
取得關於 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)
對發行集資料庫進行結構描述變更Make Schema Changes on Publication Databases