ALTER TRIGGER (Transact-SQL)

發行項
03/07/2014

修改 CREATE TRIGGER 陳述式先前所建立之 DML、DDL 或登入觸發程序的定義。觸發程序是使用 CREATE TRIGGER 建立的。您可以直接從 Transact-SQL 陳述式建立它們，也可以利用 Microsoft .NET Framework Common Language Runtime (CLR) 所建立之組件的方法來建立它們，再將它們上傳到 SQL Server 執行個體中。如需有關 ALTER TRIGGER 陳述式所用之參數的詳細資訊，請參閱＜CREATE TRIGGER (Transact-SQL)＞。

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

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

語法

-- SQL Server Syntax 
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
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 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

-- Windows 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> ]

引數

schema_name
這是 DML 觸發程序所屬的結構描述名稱。 DML 觸發程序只會針對其據以建立的資料表或檢視表的結構描述。只有當 DML 觸發程序及其對應資料表或檢視都屬於預設結構描述時，schema_name 才是選擇性的。 DDL 或登入觸發程序不可指定 schema_name。
trigger_name
這是要修改的現有觸發程序。
table |view
這是執行 DML 觸發程序的資料表或檢視。您可以選擇性地指定資料表或檢視的完整名稱。
DATABASE
將 DDL 觸發程序的範圍套用在目前資料庫上。如果指定的話，每當目前資料庫中出現 event_type 或 event_group 時，都會引發這個觸發程序。
ALL SERVER

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

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

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

加密包含 ALTER TRIGGER 陳述式文字的 sys.syscomments sys.sql_modules 項目。使用 WITH ENCRYPTION 可防止在 SQL Server 複寫中發行這個觸發程序。 CLR 觸發程序不能指定 WITH ENCRYPTION。

注意

如果觸發程序是利用 WITH ENCRYPTION 來建立的，就必須在 ALTER TRIGGER 陳述式中重新指定它，這個選項才會維持啟用狀態。
EXECUTE AS
指定用來執行這個觸發程序的安全性內容。可讓您控制 SQL Server 執行個體用來驗證觸發程序所參考的任何資料庫物件之權限的使用者帳戶。

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

如果只指定 FOR 關鍵字，AFTER 便是預設值。

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

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

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

對於 INSTEAD OF 觸發程序而言，含有指定了串聯動作 ON DELETE 的參考關聯性之資料表不能使用 DELETE 選項。同樣地，含有指定了串聯動作 ON UPDATE 的參考關聯性之資料表也不能使用 UPDATE 選項。如需詳細資訊，請參閱＜ALTER TABLE (Transact-SQL)＞。
event_type
這是在執行之後會引發 DDL 觸發程序的 Transact-SQL 語言事件名稱。對 DDL 觸發程序而言有效的事件會列在＜DDL 事件＞中。
event_group
這是 Transact-SQL 語言事件之預設定義群組的名稱。在執行屬於 event_group 的任何 Transact-SQL 語言事件之後，便會引發 DDL 觸發程序。對 DDL 觸發程序而言，有效的事件群組會列在＜DDL 事件群組＞中。 ALTER TRIGGER 執行完成之後，event_group 也會藉著將所涵蓋事件類型加入 sys.trigger_events 目錄檢視，當做巨集來執行。
NOT FOR REPLICATION

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

指出當複寫代理程式修改觸發程序所涉及的資料表時，不應執行觸發程序。
sql_statement
這是觸發程序條件和動作。
EXTERNAL NAME <method_specifier>

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

指定繫結觸發程序之組件的方法。此方法不可使用任何引數，且必須傳回空值。 class_name 必須是有效的 SQL Server 識別碼，且必須以類別的形式存在於可以顯示的組件中。這個類別不能是巢狀類別。

備註

如需有關 ALTER TRIGGER 的詳細資訊，請參閱＜CREATE TRIGGER (Transact-SQL)＞中的＜備註＞一節。

注意

自主資料庫無法使用 EXTERNAL_NAME 和 ON_ALL_SERVER 選項。

DML 觸發程序

ALTER TRIGGER 支援透過資料表和檢視表上的 INSTEAD OF 觸發程序來手動更新檢視。 SQL Server 會對所有觸發程序種類 (AFTER、INSTEAD-OF) 都以相同方式來套用 ALTER TRIGGER。

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

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

只有在觸發的 SQL 陳述式已執行成功之後，才會執行 AFTER 觸發程序。所謂執行成功，包括與已更新或刪除之物件關聯的所有參考串聯動作和條件約束檢查。 AFTER 觸發程序作業會檢查觸發陳述式的效果，也會檢查觸發陳述式所造成的所有參考串聯 UPDATE 和 DELETE 動作。

當因為父資料表 DELETE 的 CASCADE 結果而造成子系或參考資料表的 DELETE 動作時，會在這份子資料表上定義 DELETE 的 INSTEAD OF 觸發程序，而且觸發程序會被忽略，且會執行 DELETE 動作。

DDL 觸發程序

DDL 觸發程序不像 DML 觸發程序，並不以結構描述為範圍。因此，當查詢有關 DDL 觸發程序的中繼資料時，不能使用 OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTY(EX)。請改用目錄檢視。如需詳細資訊，請參閱＜取得 DDL 觸發程序的詳細資訊＞。

登入觸發程序

Windows Azure SQL 資料庫不支援登入事件的觸發程序。

權限

變更 DML 觸發程序需要定義觸發程序的資料表或檢視表的 ALTER 權限。

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

範例

下列範例會建立當使用者試圖在 SalesPersonQuotaHistory 資料表中新增或變更資料時，將使用者定義訊息列印到用戶端的 DML 觸發程序。之後，會利用 ALTER TRIGGER 來修改觸發程序，只在 INSERT 活動上套用觸發程序。這個觸發程序很有用，因為它會提醒使用者在這份資料表中更新或插入資料列，以便同時通知 Compensation 部門。

USE AdventureWorks2012;
GO
IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL
    DROP TRIGGER Sales.bonus_reminder;
GO
CREATE TRIGGER Sales.bonus_reminder
ON Sales.SalesPersonQuotaHistory
WITH ENCRYPTION
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Compensation', 16, 10);
GO
-- Now, change the trigger.
USE AdventureWorks2012;
GO
ALTER TRIGGER Sales.bonus_reminder
ON Sales.SalesPersonQuotaHistory
AFTER INSERT
AS RAISERROR ('Notify Compensation', 16, 10);
GO