CREATE TRIGGER (Transact-SQL)

語法

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

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE 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 ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a LOGON event (Logon Trigger)
CREATE 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

引數

• schema_name
  這是 DML 觸發程序所屬的結構描述名稱。DML 觸發程序的範圍是建立其資料表或檢視的結構描述。DDL 或登入觸發程序無法指定 schema_name。

• trigger_name
  這是觸發程序的名稱。trigger_name 必須符合識別碼的規則，不過，trigger_name 開頭不能是 # 或 ##。

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

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

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

• WITH ENCRYPTION
  模糊化 CREATE TRIGGER 陳述式的文字。使用 WITH ENCRYPTION 可防止在 SQL Server 複寫中發行這個觸發程序。CLR 觸發程序不能指定 WITH ENCRYPTION。

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

  如需詳細資訊，請參閱＜EXECUTE AS 子句 (Transact-SQL)＞。

• FOR | AFTER
  AFTER 指定只在觸發的 SQL 陳述式指定的所有作業都執行成功時，才引發 DML 觸發程序。所有參考的重疊顯示動作和條件約束檢查也都必須成功之後，才會引發這個觸發程序。

  當指定的關鍵字只有 FOR 時，預設值便是 AFTER。

  檢視不能定義 AFTER 觸發程序。

• INSTEAD OF
  指定執行 DML 觸發程序來取代觸發的 SQL 陳述式，因此，會覆寫觸發陳述式的動作。DDL 或登入觸發程序不能指定 INSTEAD OF。

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

  在使用 WITH CHECK OPTION 的可更新檢視上，不允許使用 INSTEAD OF 觸發程序。當在指定了 WITH CHECK OPTION 的可更新檢視上加入 INSTEAD OF 觸發程序時，SQL Server 會產生錯誤。使用者必須在定義 INSTEAD OF 觸發程序之前，利用 ALTER VIEW 來移除這個選項。

• { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
  指定當試圖針對這份資料表或檢視來執行時，會啟動 DML 觸發程序的資料修改陳述式。至少必須指定一個選項。您可以在觸發程序定義中，依照任何順序來指定這些選項的任何組合。

  對於 INSTEAD OF 觸發程序而言，含有指定了重疊顯示動作 ON DELETE 的參考關聯性之資料表不能使用 DELETE 選項。同樣地，含有指定了重疊顯示動作 ON UPDATE 的參考關聯性之資料表也不能使用 UPDATE 選項。

• event_type
  這是在執行之後會引發 DDL 觸發程序的 Transact-SQL 語言事件名稱。對 DDL 觸發程序而言有效的事件會列在＜DDL 事件＞中。

• event_group
  這是 Transact-SQL 語言事件之預設定義群組的名稱。在執行屬於 event_group 的任何 Transact-SQL 語言事件之後，便會引發 DDL 觸發程序。對 DDL 觸發程序而言有效的事件群組會列在＜DDL 事件群組＞中。

  CREATE TRIGGER 執行完成之後，藉著將所涵蓋的事件類型加入 sys.trigger_events 目錄檢視中，event_group 也可以當做巨集來執行。

• WITH APPEND
  指定應該加入現有類型的其他觸發程序。當使用 INSTEAD OF 觸發程序時，或明確指示 AFTER 觸發程序時，便不能使用 WITH APPEND。只有在為了與舊版相容而指定 FOR，且不含 INSTEAD OF 或 AFTER 時，才能使用 WITH APPEND。如果指定了 EXTERNAL NAME (也就是說，觸發程序是一個 CLR 觸發程序)，便不能指定 WITH APPEND。

  重要事項
  在下一個 Microsoft SQL Server 版本中，將移除 WITH APPEND。請勿在新的開發工作中使用 WITH APPEND，並規劃修改目前使用 WITH APPEND 的應用程式。

• NOT FOR REPLICATION
  指出當複寫代理程式修改觸發程序所涉及的資料表時，不應執行觸發程序。如需詳細資訊，請參閱＜使用 NOT FOR REPLICATION 控制條件約束、識別和觸發程序＞。

• sql_statement
  這是觸發程序條件和動作。觸發程序準則指定一些其他條件來決定所嘗試的 DML、DDL 或登入事件是否引起執行觸發程序動作。

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

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

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

  SELECT *
  FROM deleted
  

  如需詳細資訊，請參閱＜使用 inserted 或 deleted 資料表＞。

  DDL 和登入觸發程序會利用 EVENTDATA (Transact-SQL) 函數來擷取觸發事件的相關資訊。如需詳細資訊，請參閱＜使用 EVENTDATA 函數＞。

  SQL Server 允許利用資料表或檢視的 INSTEAD OF 觸發程序來更新 text、ntext 或 image 資料行。

  重要事項
  在未來的 Microsoft SQL Server 版本中，將移除 ntext、text 和 image 等資料類型。請避免在新的開發工作中使用這些資料類型，並規劃修改目前在使用這些資料類型的應用程式。請改用 nvarchar(max)、varchar(max) 和 varbinary(max)。AFTER 和 INSTEAD OF 觸發程序都支援 inserted 和 deleted 資料表中的 varchar(MAX)、nvarchar(MAX) 和 varbinary(MAX) 資料。

• < method_specifier >
  對於 CLR 觸發程序，指定繫結觸發程序的組件方法。這個方法不能有任何引數，且傳回空值。class_name 必須是有效的 SQL Server 識別碼，且必須以組件中的類別存在，而組件必須具有組件可見性。如果該類別的名稱符合命名空間規定，利用 '.' 來分隔命名空間的各個部分，您就必須用 [ ] 或 " " 分隔字元來分隔類別名稱。這個類別不能是巢狀類別。

  [!附註]

  依預設，會關閉 SQL Server 執行 CLR 程式碼的能力。您可以建立、修改和卸除參考 Managed 程式碼模組之資料庫物件，但除非您利用 sp_configure 來啟用 clr enabled 選項，否則，便不會在 SQL Server 執行個體中執行這些參考。

備註

DML 觸發程序

DDL 觸發程序

如同標準觸發程序，DDL 觸發程序也會執行預存程序來回應事件。但它們不像標準觸發程序，並不會為了回應資料表或檢視的 UPDATE、INSERT 或 DELETE 陳述式而執行。相反地，執行它們主要是為了回應資料定義語言 (DDL) 陳述式。其中包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 陳述式。執行類似 DDL 作業的某些系統預存程序也可能引發 DDL 觸發程序。

重要事項
請測試 DDL 觸發程序，以判斷它們對執行之系統預存程序的回應。例如，CREATE TYPE 陳述式以及 sp_addtype 和 sp_rename 預存程序都會引發在 CREATE_TYPE 事件上建立的 DDL 觸發程序。

如需有關 DDL 觸發程序的詳細資訊，請參閱＜DDL 觸發程序＞。

對於影響區域或全域暫存資料表與預存程序的事件，DDL 觸發程序不會為了回應它而引發。

DDL 觸發程序不像 DML 觸發程序，並不以結構描述為範圍。因此，OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 等函數無法用來查詢有關 DDL 觸發程序的中繼資料。請改用目錄檢視。如需詳細資訊，請參閱＜取得 DDL 觸發程序的相關資訊＞。

[!附註]

以伺服器為範圍的 DDL 觸發程序出現在 SQL Server Management Studio 物件總管的 [觸發程序] 資料夾中。這個資料夾在 [伺服器物件] 資料夾之下。以資料庫為範圍的 DDL 觸發程序出現在 [資料庫觸發程序] 資料夾中。這個資料夾在對應資料庫的 [可程式性] 資料夾之下。

登入觸發程序

一般觸發程序考量

權限

若要建立 DML 觸發程序需要建立觸發程序的資料表或檢視的 ALTER 權限。

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

範例

A. 使用含提示訊息的 DML 觸發程序

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
   DROP TRIGGER Sales.reminder1;
GO
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B. 使用含提示電子郵件訊息的 DML 觸發程序

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2008R2 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

C. 利用 DML AFTER 觸發程序來強制執行 PurchaseOrderHeader 和 Vendor 資料表之間的商務規則

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader 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. 使用資料庫範圍的 DDL 觸發程序

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO
CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_SYNONYM
AS 
   RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
   ROLLBACK
GO
DROP TRIGGER safety
ON DATABASE;
GO

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

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
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. 使用登入觸發程序

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. 檢視造成引發觸發程序的事件

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

請參閱

參考

概念