CodeinQueries: DDL Trigger Full Tutorial
with Example
What is a DDL Trigger?
A DDL (Data Definition Language) Trigger in SQL Server is a special kind of trigger that
responds to DDL events like:
CREATE
ALTER
DROP
GRANT, DENY, REVOKE
Unlike DML triggers (which respond to INSERT, UPDATE, DELETE), DDL triggers fire on
schema changes or security-related actions.
When to Use DDL Triggers
Audit schema changes (e.g., log when someone creates/drops a table)
Prevent changes (e.g., block dropping of certain tables or logins)
Enforce policies (e.g., restrict use of certain commands)
Basic Syntax
CREATE TRIGGER trigger_name
ON { DATABASE | ALL SERVER }
FOR { event_type | event_group }
AS
BEGIN
-- Your T-SQL logic
END;
DATABASE: Trigger fires for the current database.
ALL SERVER: Trigger fires at the server level.
event_type: Specific DDL event like CREATE_TABLE, DROP_PROCEDURE, etc.
event_group: Group of DDL events like DDL_TABLE_EVENTS.
Example 1: Audit Table Creation
Step 1: Create Audit Table
CREATE TABLE DDL_Audit_Log (
ID INT IDENTITY PRIMARY KEY,
EventType NVARCHAR(100),
ObjectName NVARCHAR(256),
ObjectType NVARCHAR(100),
EventTime DATETIME DEFAULT GETDATE(),
LoginName NVARCHAR(256),
CommandText NVARCHAR(MAX)
);
Step 2: Create Trigger on Table Creation
CREATE TRIGGER trg_AuditTableCreation
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
DECLARE @EventData XML = EVENTDATA();
INSERT INTO DDL_Audit_Log (
EventType,
ObjectName,
ObjectType,
LoginName,
CommandText
)
VALUES (
@[Link]('(/EVENT_INSTANCE/EventType)[1]',
'NVARCHAR(100)'),
@[Link]('(/EVENT_INSTANCE/ObjectName)[1]',
'NVARCHAR(256)'),
@[Link]('(/EVENT_INSTANCE/ObjectType)[1]',
'NVARCHAR(100)'),
@[Link]('(/EVENT_INSTANCE/LoginName)[1]',
'NVARCHAR(256)'),
@[Link]('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'NVARCHAR(MAX)')
);
END;
Step 3: Test the Trigger
CREATE TABLE TestTableTrigger (
ID INT
);
Step 4: View the Audit Log
SELECT * FROM DDL_Audit_Log;
Example 2: Prevent Dropping Tables
CREATE TRIGGER trg_PreventDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
PRINT 'You are not allowed to drop tables!';
ROLLBACK;
END;
Test:
DROP TABLE TestTableTrigger; -- Will fail due to the trigger
Disable the Trigger Temporarily
You can disable the trigger, drop the table, then re-enable it:
DISABLE TRIGGER trg_PreventDropTable ON DATABASE; -- for database-level
-- Your DROP TABLE command
DROP TABLE [dbo].[DATA1];
ENABLE TRIGGER trg_PreventDropTable ON DATABASE;
Common DDL Events
Event Name Description
CREATE_TABLE Fires when a table is created
ALTER_TABLE Fires when a table is altered
DROP_TABLE Fires when a table is dropped
CREATE_PROCEDURE Fires when a stored procedure is created
DROP_LOGIN Fires when a login is dropped
GRANT_DATABASE Fires when permissions are granted
Using Event Groups
Instead of listing each event, you can use predefined groups.
Example:
CREATE TRIGGER trg_AuditTableEvents
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
BEGIN
PRINT 'A table-related DDL operation occurred.';
END;
DDL_TABLE_EVENTS includes:
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
EVENTDATA() Function
DDL triggers often use the EVENTDATA() function, which returns XML about the event.
You can extract:
EventType
ObjectName
ObjectType
TSQLCommand
LoginName
Use XPath queries like:
SELECT
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)')
Drop DDL Trigger
DROP TRIGGER trg_AuditTableCreation ON DATABASE;
DROP TRIGGER trg_PreventDropTable ON DATABASE;
Or for server-level:
DROP TRIGGER trigger_name ON ALL SERVER;
Best Practices
Always log important DDL operations.
Be careful with ROLLBACK in DDL triggers — can interfere with deployments.
Secure DDL triggers (e.g., make only sysadmins able to disable them).
Use XML parsing wisely for performance.