共用方式為


DDL 觸發器

DDL 觸發程式會在發生各種資料定義語言(DDL)事件時執行。 這些事件主要對應於以關鍵字 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 或 UPDATE STATISTICS 開始的 Transact-SQL 語句。 某些執行著類似 DDL 操作的系統預存程式也可以引發 DDL 觸發器。

當您要執行下列動作時,請使用 DDL 觸發程式:

  • 防止對資料庫架構進行某些變更。

  • 使資料庫中發生事件,以回應資料庫結構的變更。

  • 記錄資料庫架構中的變更或事件。

這很重要

測試您的 DDL 觸發程式,以判斷其對執行之系統預存程式的回應。 例如,CREATE TYPE 語句和 sp_addtype 預存程式都會引發在CREATE_TYPE事件上建立的 DDL 觸發程式。

DDL 觸發程式的類型

Transact-SQL DDL 觸發程式
特殊類型的 Transact-SQL 預存程式,會執行一或多個 Transact-SQL 語句,以回應伺服器範圍或資料庫範圍事件。 例如,在執行 ALTER SERVER CONFIGURATION 這類語句或使用 DROP TABLE 刪除資料表時,DDL 觸發器可能會被觸發。

CLR DDL 觸發程式
CLR 觸發程式不是執行 Transact-SQL 預存程式,而是執行一或多個以受管理的代碼撰寫的方法,這些方法是作為 .NET Framework 中建立的組件的成員,並在 SQL Server 中上傳。

只有在觸發它們的 DDL 語句執行之後,DDL 觸發程序才會被觸發。 DDL 觸發器不能作為 INSTEAD OF 觸發器使用。 DDL 觸發程式不會針對影響本機或全域臨時表和預存程式的事件引發。

DDL 觸發程式不會建立特殊 inserteddeleted 數據表。

使用 EVENTDATA 函式擷取引發 DDL 觸發程式的事件相關信息,以及觸發程式所造成的後續變更。

要為每個 DDL 事件建立多個觸發器。

不同於 DML 觸發程式,DDL 觸發程式的範圍不會限定為架構。 因此,OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 等函式無法用於查詢有關 DDL 觸發程式的元數據。 請改用目錄檢視。

伺服器範圍的 DDL 觸發程式會出現在 [觸發程式 ] 資料夾中的 SQL Server Management Studio 物件總管中。 此資料夾位於 [伺服器物件 ] 資料夾底下。 資料庫範圍的 DDL 觸發程式會出現在 [資料庫觸發程式 ] 資料夾中。 此資料夾位於對應資料庫的 [可程式性 ] 資料夾底下。

這很重要

觸發程式內的惡意代碼可以在提升的許可權下執行。 如需如何協助降低此威脅的詳細資訊,請參閱 管理觸發程序安全性

DDL 觸發程式範圍

DDL 觸發器可能會在回應目前資料庫或伺服器上處理的 Transact-SQL 事件時觸發。 觸發條件的範圍取決於事件。 例如,為了回應CREATE_TABLE事件而建立的 DDL 觸發程式,可以在資料庫或伺服器實例發生CREATE_TABLE事件時執行此動作。 為了回應CREATE_LOGIN事件而建立的 DDL 觸發程式只有在伺服器實例中發生CREATE_LOGIN事件時,才能執行此動作。

在下列範例中,DDL 觸發程式safety會在資料庫中發生 DROP_TABLEALTER_TABLE 事件時觸發。

CREATE TRIGGER safety   
ON DATABASE   
FOR DROP_TABLE, ALTER_TABLE   
AS   
   PRINT 'You must disable Trigger "safety" to drop or alter tables!'   
   ROLLBACK;  

在下列範例中,如果目前伺服器實例發生任何 CREATE_DATABASE 事件,DDL 觸發程式就會列印訊息。 此範例會使用 函 EVENTDATA 式來擷取對應 Transact-SQL 語句的文字。 如需如何搭配 DDL 觸發程式使用 EVENTDATA 的詳細資訊,請參閱 使用 EVENTDATA 函數

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  
  

本主題中「選取特定 DDL 語句以引發 DDL 觸發程式」一節所提供的連結,包含了將 Transact-SQL 語句對應到可以為其指定的範圍的清單。

資料庫範圍的 DDL 觸發程式會以物件的形式儲存在建立它們的資料庫中。 您可以在 master 資料庫中建立 DDL 觸發程式,其行為就像在使用者設計資料庫中建立的觸發程式一樣。 您可以查詢 sys.triggers 目錄檢視,以取得 DDL 觸發程式的相關信息。 您可以在建立觸發程式的資料庫內容中查詢 sys.triggers ,或將資料庫名稱指定為標識符,例如 master.sys.triggers

伺服器範圍內的 DDL 觸發程式會儲存為 master 資料庫中的物件。 不過,您可以在任何資料庫內容中查詢 sys.server_triggers 目錄檢視,以取得伺服器範圍 DDL 觸發程式的相關信息。

指定 Transact-SQL 語句或語句群組

選擇特定的 DDL 語法以觸發 DDL 觸發器

DDL 觸發程式可以設計為在執行一或多個特定 Transact-SQL 語句之後引發。 在上述範例中,觸發器 safety 會在任何 DROP_TABLEALTER_TABLE 事件之後觸發。 如需可指定來引發 DDL 觸發程式的 Transact-SQL 語句清單,以及觸發程式可以引發的範圍,請參閱 DDL 事件

選擇預先定義的 DDL 語句群組以觸發 DDL 觸發程式

DDL 觸發程式可能會在執行屬於類似事件預先定義群組的任何 Transact-SQL 事件之後引發。 例如,如果您想要在執行任何 CREATE TABLE、ALTER TABLE 或 DROP TABLE 語句之後觸發 DDL 觸發程式,您可以在 CREATE TRIGGER 語句中指定 FOR DDL_TABLE_EVENTS。 執行 CREATE TRIGGER 之後,事件群組所涵蓋的事件會新增至 sys.trigger_events 目錄檢視。

在 SQL Server 2005 中,如果在事件群組上建立觸發程式, sys.trigger_events 不包含事件群組的相關信息, sys.trigger_events 只包含該群組所涵蓋個別事件的相關信息。 在 SQL Server 2008 和更新版本中, sys.trigger_events 會保存有關觸發程式建立之事件群組的元數據,以及事件群組涵蓋的個別事件。 因此,SQL Server 2008 和更新版本中事件群組所涵蓋之事件的變更不適用於 SQL Server 2005 中在這些事件群組上建立的 DDL 觸發程式。

如需 DDL 觸發程式可用的預先定義 DDL 語句群組清單、事件群組涵蓋的特定語句,以及這些事件群組可進行程式設計的範圍,請參閱 DDL 事件群組

任務 主題
描述如何建立、修改、刪除或停用 DDL 觸發程式。 實作 DDL 觸發程式
描述如何建立 CLR DDL 觸發程式。 建立 CLR 觸發程式
描述如何傳回 DDL 觸發程式的相關信息。 取得 DDL 觸發程式的相關信息
描述如何使用 EVENTDATA 函式傳回引發 DDL 觸發程式之事件的相關信息。 使用 EVENTDATA 函數
描述如何管理觸發事件的安全性。 管理觸發程式安全性

另請參閱

DML 觸發程序
登入觸發程序
創建觸發器 (Transact-SQL)