使用 EVENTDATA 函數

發行項
05/04/2023

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

使用 EVENTDATA 函數擷取引發 DDL 觸發程序之事件的相關資訊。此函數會傳回 xml 值。 XML 結構描述包括有關下列項目的資訊：

事件的時間。
執行觸發程序時連接的系統處理序識別碼 (SPID)。
引發觸發程序的事件類型。

視事件類型而定，結構描述會包括其他資訊，例如發生事件的資料庫、發生事件的物件以及事件的 Transact-SQL 陳述式。如需詳細資訊，請參閱 DDL 觸發程序。

例如，下列 DDL 觸發程序是在 AdventureWorks2022 範例資料庫中建立：

CREATE TRIGGER safety   
ON DATABASE   
FOR CREATE_TABLE   
AS   
    PRINT 'CREATE TABLE Issued.'  
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
   RAISERROR ('New tables cannot be created in this database.', 16, 1)   
   ROLLBACK  
;

接著會執行下列 CREATE TABLE 陳述式：

CREATE TABLE NewTable (Column1 int);

DDL 觸發程序中的 EVENTDATA() 陳述式，會擷取到不容許的 CREATE TABLE 陳述式文字。對 EVENTDATA 產生的 xml 資料使用 XQuery 陳述式並擷取 <CommandText> 項目，即可完成這項作業。如需詳細資訊，請參閱 XQuery 語言參考 (SQL Server)。

警告

EVENTDATA 會擷取 CREATE_SCHEMA 事件的資料，以及對應之 CREATE SCHEMA 定義的 <schema_element> (如果有的話)。此外，EVENTDATA 還會將 <schema_element> 定義識別為個別事件。因此，在 CREATE_SCHEMA 事件和 CREATE SCHEMA 定義之 <schema_element> 代表的事件上建立的 DDL 觸發程序，可能會傳回相同的事件資料兩次，例如 TSQLCommand 資料。例如，假設在 CREATE_SCHEMA 和 CREATE_TABLE 兩個事件上建立 DDL 觸發程序，並執行下列批次：

CREATE SCHEMA s

CREATE TABLE t1 (col1 int)

如果應用程式擷取 CREATE_TABLE 事件的 TSQLCommand 資料，請注意這項資料可能會出現兩次：一次是在發生 CREATE_SCHEMA 事件時，另一次則是在發生 CREATE_TABLE 事件時。請避免同時在 CREATE_SCHEMA 事件和任何對應之 CREATE SCHEMA 定義的 <schema_element> 文字上建立 DDL 觸發程序，或在應用程式中建立邏輯，使應用程式不會重複處理相同的事件。

ALTER TABLE 和 ALTER DATABASE 事件

ALTER_TABLE 和 ALTER_DATABASE 事件的事件資料也包含受到 DDL 陳述式所影響之其他物件的名稱和類型，以及在這些物件上執行的動作。 ALTER_TABLE 事件資料包含受到 ALTER TABLE 陳述式所影響之資料行、條件約束或觸發程序的名稱，以及在這些受影響物件上執行的動作 (建立、更改、卸除、啟用或停用)。 ALTER_DATABASE 事件資料包含受到 ALTER DATABASE 陳述式所影響之任何檔案或檔案群組的名稱，以及在這些受影響物件上執行的動作 (建立、更改或卸除)。

例如，您可以在 AdventureWorks 範例資料庫中建立下列 DDL 觸發程序：

CREATE TRIGGER ColumnChanges  
ON DATABASE   
FOR ALTER_TABLE  
AS  
-- Detect whether a column was created/altered/dropped.  
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')  
RAISERROR ('Table schema cannot be modified in this database.', 16, 1);  
ROLLBACK;

然後，執行下列違反條件約束的 ALTER TABLE 陳述式：

ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;

DDL 觸發程序中的 EVENTDATA() 陳述式會擷取不允許的 ALTER TABLE 陳述式文字。

範例

您可以使用 EVENTDATA 函數來建立事件的記錄。在下列範例中，會建立儲存事件資訊的資料表。每次發生資料庫層級 DDL 事件時，便會在使用下列資訊擴展資料表的目前資料庫上建立 DDL 觸發程序：

事件時間 (使用 GETDATE 函數)。
其工作階段上發生事件的資料庫使用者 (使用 CURRENT_USER 函數)。
事件的類型。
組成事件的 Transact-SQL 陳述式。

再次對 EVENTDATA 產生的 xml 資料使用 XQuery，即可擷取後兩個項目。

USE AdventureWorks2022;  
GO  
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));  
GO  
CREATE TRIGGER log   
ON DATABASE   
FOR DDL_DATABASE_LEVEL_EVENTS   
AS  
DECLARE @data XML  
SET @data = EVENTDATA()  
INSERT ddl_log   
   (PostTime, DB_User, Event, TSQL)   
   VALUES   
   (GETDATE(),   
   CONVERT(nvarchar(100), CURRENT_USER),   
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),   
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;  
GO  
--Test the trigger  
CREATE TABLE TestTable (a int)  
DROP TABLE TestTable ;  
GO  
SELECT * FROM ddl_log ;  
GO

注意

若要傳回事件資料，建議您使用 query() 方法的 XQuery value() 方法。 query() 方法會在輸出中傳回 XML 和 & 符號逸出歸位字元及換行字元 (CRLF) 執行個體， value() 方法則會將 CRLF 執行個體轉譯成在輸出中不可見。

AdventureWorks2022 範例資料庫中則提供了相似的 DDL 觸發程序範例。若要取得此範例，請使用 SQL Server Management Studio 來尋找 [Database Triggers] 資料夾。此資料夾位於 AdventureWorks2022 資料庫的 [Programmability] 資料夾下。以滑鼠右鍵按一下 [ddlDatabaseTriggerLog]，然後選取 [編寫資料庫觸發程序的指令碼為]。依預設，會停用 DDL 觸發程序 ddlDatabaseTriggerLog。

另請參閱

DDL 事件
 DDL 事件群組

共用方式為

使用 EVENTDATA 函數

ALTER TABLE 和 ALTER DATABASE 事件

範例

另請參閱

意見反應

其他資源