T-SQL DDL Triggers

Master T-SQL DDL triggers for schema-event auditing — CREATE/ALTER/DROP events, EVENTDATA() XML for accurate event details, ON DATABASE vs ON ALL SERVER scope, blocking dangerous operations with ROLLBACK, common event groups, and best practices.

A DDL trigger fires in response to a schema-changing event — anything that changes the structure of a database object rather than its data. The events include CREATE, ALTER, DROP (of tables, views, procedures, indexes, users, logins, etc.), plus GRANT, DENY, REVOKE, and a few others.

DDL triggers exist to audit, log, or block schema changes. They sit at one of two scopes:

This is the most important function in DDL triggers. EVENTDATA() returns an XML document describing the event that fired the trigger — what happened, to which object, by which user, when, and the exact T-SQL command that ran.

Useful XPath expressions inside the EVENTDATA XML:

Build a simple log that records every CREATE / ALTER / DROP of a table in the current database.

CREATE TABLE TableChangeLog (
    ChangeID    INT IDENTITY(1,1) PRIMARY KEY,
    EventType   NVARCHAR(50),
    ObjectName  NVARCHAR(255),
    LoginName   NVARCHAR(128),
    ChangeTime  DATETIME2 DEFAULT SYSDATETIME(),
    TSQLCommand NVARCHAR(MAX)
);

CREATE OR ALTER TRIGGER LogTableChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML = EVENTDATA();

    INSERT INTO TableChangeLog (EventType, ObjectName, LoginName, TSQLCommand)
    VALUES (
        @data.value('(/EVENT_INSTANCE/EventType)[1]',                   'NVARCHAR(50)'),
        @data.value('(/EVENT_INSTANCE/ObjectName)[1]',                  'NVARCHAR(255)'),
        @data.value('(/EVENT_INSTANCE/LoginName)[1]',                   'NVARCHAR(128)'),
        @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',     'NVARCHAR(MAX)')
    );
END;
GO

Now test the trigger by making schema changes:

CREATE TABLE TestTable (id INT PRIMARY KEY, name NVARCHAR(50));

ALTER TABLE TestTable ADD active BIT DEFAULT 1;

DROP TABLE TestTable;

Inspect the log:

SELECT ChangeID, EventType, ObjectName, LoginName, ChangeTime
FROM   TableChangeLog
ORDER BY ChangeID;

DDL triggers can also roll back the event that fired them — a great way to enforce policies. This trigger refuses to drop any table:

CREATE OR ALTER TRIGGER PreventTableDrops
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT 'DROP TABLE is disabled. Disable this trigger first.';
    ROLLBACK;
END;
GO

-- Try to drop a table
DROP TABLE TestTable;

The DROP didn't happen — ROLLBACK inside a DDL trigger undoes the event that fired it.

To catch events across every database on the instance, use ON ALL SERVER. Useful for tracking new database creation, login changes, etc.

USE master;
GO

CREATE OR ALTER TRIGGER AuditDatabaseCreation
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
    DECLARE @data XML = EVENTDATA();
    PRINT 'New database created: ' +
          @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)') +
          ' by ' + @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)');
END;
GO

Using a group instead of listing individual events keeps the trigger working as Microsoft adds new event types.

-- Database-level DDL triggers
SELECT name, parent_class_desc, is_disabled
FROM   sys.triggers
WHERE  parent_class = 0;             -- 0 = database

-- Server-level DDL triggers
SELECT name, parent_class_desc, is_disabled
FROM   sys.server_triggers;

• DDL triggers fire on schema-changing events — CREATE, ALTER, DROP, GRANT etc.
• Two scopes: ON DATABASE for events in one database, ON ALL SERVER for events across the entire instance.
• Always use EVENTDATA() to find out what happened — never sys.objects without context. EVENTDATA returns XML you query with .value(xpath, type).
• ROLLBACK inside a DDL trigger undoes the event — useful for blocking unwanted schema changes.
• Use event groups like DDL_TABLE_EVENTS to cover related events; otherwise list specific events like CREATE_TABLE.
• Server-level DDL triggers live in master and need CONTROL SERVER permission to create.