What is DDL Trigger?

Data Definition Language (DDL) Triggers are special kind of Stored Procedure or an operation that gets executed automatically when a DDL Statements like CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements are executed. Certain system stored procedures that perform DDL like operations can also fire DDL triggers.

Scope of DDL Triggers

DDL triggers can be DATABASE scoped or SERVER scoped. The DDL triggers with Server level scope gets fired in response to a DDL statement with server scope like CREATE DATABASE, CREATE LOGIN, GRANT_SERVER, ALTER DATABASE, ALTER LOGIN etc. Where as DATABASE scoped DDL triggers fire in response to DDL statement with DATABASE SCOPE like CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, ALTER TABLE, ALTER PROCEDURE, ALTER FUNCTION etc.

[ALSO READ] Data Manipulation Language (DML) Triggers in Sql Server

Uses of DDL Triggers

DDL triggers will be very handy to audit and control the DDL changes. Below are such example scenarios:

• Track the DLL changes
  • Track the DDL T-Sql statement which is fired
  • Who has fired the DDL statement. For example we may be interested in identifying who has dropped the table.
  • When the DDL statement is fired etc
• Block user from doing some DDL changes like DROP TABLE, DROP PROCEDURE etc
• Allow DDL changes only during specified window (i.e. only during particular hours of the day)

Understand DDL Triggers with extensive list of examples

To understand DDL triggers with extensive list of examples, let us create a demo database with a Table and a Stored Procedure by executing the following Script:

--Create Demo Database
CREATE DATABASE SqlHintsDDLTriggersDemo
GO
USE SqlHintsDDLTriggersDemo
GO
--Create Customer Table
CREATE TABLE Customer 
( CustomerId INT IDENTITY (1, 1) NOT NULL ,
  FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO
--Create a Stored Procedure
CREATE PROCEDURE GetCustomers
AS
BEGIN
	SELECT * FROM Customer
END
GO

EXAMPLE 1: This example DDL trigger blocks user FROM modifying or dropping any Table in the SqlHintsDDLTriggersDemo database

Execute the following statement to create a Trigger which fires in response to ALTER TABLE and DROP TABLE statements

USE SqlHintsDDLTriggersDemo
GO
CREATE TRIGGER DDLTriggerToBlockTableDDL
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
BEGIN
 PRINT 'Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables'
 ROLLBACK
END

Now try to drop the Customer table by executing the following statement

DROP TABLE Customer 

RESULT:

Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

From the above result we can see that the DDL trigger DDLTriggerToBlockTableDDL is blocking the user from dropping the Customer table

Let us try to execute the following statement to add DOB column to the Customer Table

ALTER TABLE dbo.Customer ADD DOB DATETIME

RESULT:

Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

From the above result we can see that the DDL trigger DDLTriggerToBlockTableDDL is blocking the user from adding DOB column to the Customer table

In Sql Server we have an option to disable the trigger without dropping it. Execute the below script to disable the trigger DDLTriggerToBlockTableDDL:

DISABLE TRIGGER DDLTriggerToBlockTableDDL ON DATABASE

Now try to re-execute the following statement to add the DOB column to the Customer Table

ALTER TABLE dbo.Customer ADD DOB DATETIME
GO
SELECT * FROM dbo.Customer

RESULT

Execute the following statement to enable back the Disabled trigger DDLTriggerToBlockTableDDL

ENABLE TRIGGER DDLTriggerToBlockTableDDL ON DATABASE

Now try to execute the following statement drop the newly added column DOB from the Customer table by executing the following statement

ALTER TABLE dbo.Customer DROP COLUMN DOB

RESULT:

Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

We can drop the DDLTriggerToBlockTableDDL trigger by executing the following statement

DROP TRIGGER DDLTriggerToBlockTableDDL ON DATABASE

[ALSO READ] Logon Triggers in Sql Server

EXAMPLE 2: This example DDL trigger blocks user from creating, modifying or dropping any Stored Procedures in the SqlHintsDDLTriggersDemo database

USE SqlHintsDDLTriggersDemo
GO
CREATE TRIGGER DDLTriggerToBlockProcedureDDL
ON DATABASE
FOR DDL_PROCEDURE_EVENTS
AS
BEGIN
 PRINT 'Disable trigger DDLTriggerToBlockProcedureDDL to create, alter or drop procedures'
 ROLLBACK
END

After creating the above trigger, try to drop the stored procedure GetCustomers by executing the following statement

DROP PROCEDURE GetCustomers

RESULT:

Disable trigger DDLTriggerToBlockProcedureDDL to create, alter or drop procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

In the above example trigger, the trigger action DDL_PROCEDURE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_PROCEDURE, ALTER_PROCEDURE and DROP_PROCEDURE. The system stored procedure SYS.TRIGGER_EVENT_TYPES lists out all the trigger events.

If you want to just block dropping of the stored procedure then in the FOR clause just mention the event DROP_PROCEDURE instead of DDL_PROCEDURE_EVENTS

The below query explains that the DDL_PROCEDURE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_PROCEDURE, ALTER_PROCEDURE and DROP_PROCEDURE.

SELECT * FROM SYS.TRIGGER_EVENT_TYPES 
WHERE parent_type = 10024 or type = 10024

RESULT:

We can drop this stored procedure DDL trigger by executing the following statement:

DROP TRIGGER DDLTriggerToBlockProcedureDDL ON DATABASE

EXAMPLE 3: This example server level trigger blocks user from creating, altering or dropping database

USE MASTER
GO
CREATE TRIGGER DDLTriggerToBlockDatabaseDDL
ON ALL SERVER
FOR DDL_DATABASE_EVENTS
AS
BEGIN
 PRINT 'Disable trigger DDLTriggerToBlockDatabaseDDL to create, alter or drop database'
 ROLLBACK
END

Note: Server scoped triggers need to be created in the MASTER database

After creating the above trigger, try to create the database TestDemoDB

CREATE DATABASE TestDemoDB

RESULT:

Disable trigger DDLTriggerToBlockDatabaseDDL to create, alter or drop database
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

From the above result we can see that the DDL trigger DDLTriggerToBlockDatabaseDDL is blocking the user from Creating the database

In the above example trigger, the trigger action DDL_DATABASE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_DATABASE, ALTER_DATABASE and DROP_DATABASE. The system stored procedure SYS.TRIGGER_EVENT_TYPES lists out all the trigger events.

If you want to just block creating of the new database then in the FOR clause JUST mention the event CREATE_DATABASE instead of DDL_DATABASE_EVENTS

The below query explains that the DDL_DATABASE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_DATABASE, ALTER_DATABASE and DROP_DATABASE.

SELECT * FROM SYS.TRIGGER_EVENT_TYPES
WHERE parent_type = 10004 or type = 10004

RESULT:

We can drop this DDL trigger by executing the following statement

DROP TRIGGER DDLTriggerToBlockDatabaseDDL ON ALL SERVER

[ALSO READ] INSERTED and DELETED Logical Tables in Sql Server

EXAMPLE 4: This example explains how we can use DDL triggers to log the stored procedure DDL statement, statement execution time, the user who has fired the statement etc

Let us execute the following statement to create a Audit log table: LogDDLEvents, to capture the Stored Procedure DDL statement

CREATE TABLE dbo.LogDDLEvents
( 	
 EventTime DATETIME,
 LoginName VARCHAR(50),
 TSQLCommand NVARCHAR(MAX) 
)

Now execute the following statement to create a SP DDL actions. This SP is using the EVENTDATA() function to capture the triggering SP DDL statement into the audit log table LogDDLEvents

USE SqlHintsDDLTriggersDemo
GO
CREATE TRIGGER DDLTriggerForSPDDL
ON DATABASE
FOR DDL_PROCEDURE_EVENTS
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @EventData XML = EVENTDATA()
 INSERT INTO dbo.LogDDLEvents(EventTime,LoginName, TSQLCommand)	
 SELECT @EventData.value('(/EVENT_INSTANCE/PostTime)[1]'
                                  , 'DATETIME'),
  @EventData.value('(/EVENT_INSTANCE/LoginName)[1]'
                                  , 'VARCHAR(50)'),
 @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'
                                  , 'NVARCHAR(MAX)')
END

Now try to create a Stored Procedure by executing the following statement

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
    SELECT * FROM Customers
END

After executing the above statement let us verify the audit table: LogDDLEvents data by executing the following statement

SELECT * FROM dbo.LogDDLEvents

RESULT

Let us check whether apart from adding to the audit log table: LogDDLEvents, the Stored Procedure is created or not by executing the following statement?

SP_HELPTEXT GetAllCustomers

We can drop this DDL trigger by executing the following statement

DROP TRIGGER DDLTriggerForSPDDL ON DATABASE

[ALSO READ]
Introduction to Triggers
Data Manipulation Language (DML) Triggers
INSERTED and DELETED Logical Tables
Logon Triggers