EVENTDATA 関数の使用

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL データベース

DDL トリガーを起動するイベントに関する情報は、EVENTDATA 関数を使用してキャプチャされます。 この関数は、 xml 値を返します。 XML スキーマには、次の項目に関する情報が含まれています。

  • イベントの時刻。

  • トリガーの実行時の接続のセッション ID (SPID)。

  • トリガーを起動したイベントの種類。

イベントの種類に応じて、イベントが発生したデータベース、イベントが発生したオブジェクト、イベントの Transact-SQL ステートメントなどの追加情報がスキーマに含まれます。 詳細については、「 DDL トリガー」を参照してください。

たとえば、次の DDL トリガーが AdventureWorks2025 サンプル データベースに作成されたとします。

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トリガーは、同じイベントデータを2回返すことがあります。例えばTSQLCommandデータなどです。 たとえば、CREATE_SCHEMA イベントと CREATE_TABLE イベントの両方で DDL トリガーが作成され、次のバッチを実行するとします。

CREATE SCHEMA s

CREATE TABLE t1 (col1 int)

アプリケーションで CREATE_TABLE イベントの TSQLCommand データを取得する場合は、このデータが 2 回発生する可能性があることに注意してください。つまり、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 文の影響を受けるファイルやファイルグループの名前、そして影響を受けたオブジェクトに対して実行されたアクション(作成、変更、削除)が含まれます。

たとえば、次の DDL トリガーを AdventureWorks サンプル データベースに作成します。

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 トリガーにより、データベース レベルの DDL イベントが発生するたびに、次の情報がテーブルに設定されます。

  • イベントの時刻 (GETDATE 関数を使用)。

  • イベントが発生したセッションのデータベース ユーザー (CURRENT_USER 関数を使用)。

  • イベントの種類。

  • イベントが含まれる Transact-SQL ステートメント。

最後の 2 つの項目は、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

Note

イベント データを返す場合は、 query() メソッドの代わりに XQuery の value() を使用してください。 query() メソッドでは、XML およびアンパサンドでエスケープされる復帰と改行 (CRLF) インスタンスが出力に返されます。一方 value() メソッドでは、CRLF インスタンスが出力に返されますが、表示はされません。

同様の DDL トリガーの例を、 AdventureWorks2025 サンプル データベースで提供しています。 この例を入手するには、SQL Server Management Studio を使用して Database Triggers フォルダーを探します。 このフォルダはAdventureWorks2025データベースの Programmability フォルダの下にあります。 ddlDatabaseTriggerLog を右クリックし、[データベース トリガーをスクリプト化] を選択します。 既定では、DDL トリガー ddlDatabaseTriggerLog は無効になっています。

参照

DDL イベント
DDL イベント グループ

  • Last updated on