Use the EVENTDATA Function
Applies To: SQL Server 2016
Information about an event that fires a DDL trigger is captured by using the EVENTDATA function. This function returns an xml value. The XML schema includes information about the following:
The time of the event.
The System Process ID (SPID) of the connection when the trigger executed.
The type of event that fired the trigger.
Depending on the event type, the schema then includes additional information such as the database in which the event occurred, the object against which the event occurred, and the Transact-SQL statement of the event. For more information, see DDL Triggers.
For example, the following DDL trigger is created in the AdventureWorks2012 sample database:
CREATE TRIGGER safety ON DATABASE FOR CREATE_TABLE AS PRINT 'CREATE TABLE Issued.' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)','nvarchar(max)') RAISERROR ('New tables cannot be created in this database.', 16, 1) ROLLBACK ;
CREATE TABLE statement is then run:
CREATE TABLE NewTable (Column1 int);
EVENTDATA() statement in the DDL trigger captures the text of the
CREATE TABLE statement that is not allowed. This is achieved by using an XQuery statement against the xml data that is generated by EVENTDATA and retrieving the <CommandText> element. For more information, see XQuery Language Reference (SQL Server).
If the application retrieves the
The event data for the ALTER_TABLE and ALTER_DATABASE events also includes the names and types of other objects affected by the DDL statement and the action performed on these objects. The ALTER_TABLE event data includes the names of the columns, constraints, or triggers affected by the ALTER TABLE statement and the action (create, alter, drop, enable, or disable) performed on the affected objects. The ALTER_DATABASE event data includes the names of any files or filegroups affected by the ALTER DATABASE statement and the action (create, alter, or drop) performed on the affected objects.
For example, create the following DDL trigger in the AdventureWorks sample database:
CREATE TRIGGER ColumnChanges ON DATABASE FOR ALTER_TABLE AS -- Detect whether a column was created/altered/dropped. SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)', 'nvarchar(max)') RAISERROR ('Table schema cannot be modified in this database.', 16, 1); ROLLBACK;
Then execute the following ALTER TABLE statement that violates a constraint:
ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;
The EVENTDATA() statement in the DDL trigger captures the text of the
ALTER TABLE statement that is not permitted.
You can use the EVENTDATA function to create a log of events. In the following example, a table is created to store event information. A DDL trigger is then created on the current database that populates the table with the following information whenever any database-level DDL event occurs:
The time of the event (using the GETDATE function).
The database user against whose session the event occurred (using the CURRENT_USER function).
The type of the event.
The Transact-SQL statement that comprised the event.
Again, the last two items are captured by using XQuery against the xml data that is generated by EVENTDATA.
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)', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)') ) ; GO --Test the trigger CREATE TABLE TestTable (a int) DROP TABLE TestTable ; GO SELECT * FROM ddl_log ; GO
A similar DDL trigger example is provided with the AdventureWorks2012 sample database. To obtain the example, locate the Database Triggers folder by using SQL Server Management Studio. This folder is located under the Programmability folder of the AdventureWorks2012 database. Right-click ddlDatabseTriggerLog and select Script Database Trigger as. By default, DDL trigger ddlDatabseTriggerLog is disabled.