Use the EVENTDATA Function

Information about an event that fires a DDL trigger is captured by using the EVENTDATA

function. This function returns an

value. The XML schema includes information about the

following:

The time of the event.

The Session 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

sample database:

The following

statement is then run:

The

statement in the DDL trigger captures the text of the

statement

that is not allowed. This is achieved by using an XQuery statement against the

data that is

generated by EVENTDATA and retrieving the element. For more information,

see

XQuery Language Reference (SQL Server).

Ｕ

Caution

AdventureWorks2025
CREATE TABLE
CREATE TABLE NewTable (Column1 int);
EVENTDATA()
CREATE TABLE
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
;