DDL トリガーを起動するイベントに関する情報は、EVENTDATA 関数を使用してキャプチャされます。 この関数は、 xml 値を返します。 XML スキーマには、次に関する情報が含まれています。
イベントの時刻。
トリガーの実行時の接続のシステム プロセス ID (SPID)。
トリガーを発生したイベントの種類。
その後、イベントの種類に応じて、スキーマには、イベントが発生したデータベース、イベントが発生したオブジェクト、イベントの Transact-SQL ステートメントなどの追加情報が含まれます。 詳細については、「 DDL トリガー」を参照してください。
たとえば、 AdventureWorks2012 サンプル データベースには、次の DDL トリガーが作成されます。
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 データなど) を 2 回返す場合があります。 たとえば、CREATE_SCHEMA イベントとCREATE_TABLE イベントの両方で作成され、次のバッチが実行される DDL トリガーを考えてみましょう。
CREATE SCHEMA s
CREATE TABLE t1 (col1 int)
アプリケーションがCREATE_TABLE イベントの TSQLCommand データを取得する場合、このデータは、CREATE_SCHEMA イベントが発生したときに 1 回、CREATE_TABLE イベントが発生したときに 2 回表示される可能性があることに注意してください。 CREATE_SCHEMA イベントと、対応する CREATE SCHEMA 定義の <schema_element> テキストの両方に DDL トリガーを作成したり、同じイベントが 2 回処理されないようにロジックをアプリケーションに組み込んだりしないでください。
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 ステートメント。
ここでも、最後の 2 つの項目は、EVENTDATA によって生成される xml データに対して XQuery を使用してキャプチャされます。
USE AdventureWorks2012;
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 インスタンスを出力で非表示にします。
AdventureWorks2012 サンプル データベースには、同様の DDL トリガーの例が用意されています。 この例を取得するには、SQL Server Management Studio を使用してデータベース トリガー フォルダーを見つけます。 このフォルダーは、AdventureWorks2012 データベースの Programmability フォルダーの下にあります。 ddlDatabaseTriggerLog を右クリックし、[スクリプト データベース トリガー] を選択します。 既定では、DDL トリガー ddlDatabaseTriggerLog は無効になっています。