DDL Triggers In SQL Server

Introduction

In this tutorial, I am going to explain the concept of DDL Triggers in SQL Server. This detailed article will cover the following topics,

1. Introduction
2. Introduction to DDL Triggers
3. Types of DDL Triggers
4. Scope of DDL Triggers
5. Enable Triggers
6. Disable Triggers
7. Drop Triggers
8. Difference Between DDL and DML Triggers
9. Conclusion

 First, let's create a database with some tables containing some dummy data. Here, I am providing you with the database along with the tables containing the records, on which I am showing you the various examples. Let's see.

CREATE DATABASE OnkarSharma_DDLTriggers
PRINT 'New Database ''OnkarSharma_DDLTriggers'' Created'
GO

USE [OnkarSharma_DDLTriggers]
GO

CREATE TABLE [dbo].[Employee] (
	EmployeeID INT IDENTITY (31100,1),
	EmployerID BIGINT NOT NULL DEFAULT 228866,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	Email VARCHAR(255) NOT NULL UNIQUE,
	DepartmentID VARCHAR(100) NOT NULL,
	Age INT  NOT NULL,
	GrossSalary BIGINT NOT NULL,
	PerformanceBonus BIGINT,
	ContactNo VARCHAR(25),
	PRIMARY KEY (EmployeeID)
);

CREATE TABLE [dbo].[Restaurant](
	RestaurantId INT IDENTITY (51, 1),
	RestaurantName VARCHAR(MAX) NOT NULL,
	Email VARCHAR(100),
	City VARCHAR(100),
	State VARCHAR(100) NOT NULL,
	Country VARCHAR(100) NOT NULL,
	PRIMARY KEY(RestaurantId)
);

CREATE TABLE [dbo].[tbl_Menu] (
	MenuId INT IDENTITY (81, 1) PRIMARY KEY,
	FoodCategoryID INT NOT NULL,
	FoodName VARCHAR (255) NOT NULL,
	TypeofFood VARCHAR (100) NOT NULL,
	Price DECIMAL(6, 2) NOT NULL
);

Let's check our following tables by using the following queries.

1) To get the data from the "Employee" table, use the following query.

SELECT * FROM OnkarSharma_DDLTriggers..Employee

2) To get the data from the "tbl_Menu" table, use the following query.

SELECT * FROM OnkarSharma_DDLTriggers..tbl_Menu

DDL Triggers

SQL Server DDL triggers fire in response to various DDL (Data Definition Language) events. These events mainly correspond to T-SQL statements that begin with one of the following keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS. DDL triggers can be used for the following cases:

1. Respond to changes in the database schema.
2. Prevent some changes to the database schema.
3. Record changes or events in the database schema.

Key Points

• DDL Triggers were introduced in SQL Server 2005.
• Multiple triggers can be created for each DDL event.
• DDL triggers can be used to restrict DDL operations.
• DDL triggers cannot be used as "INSTEAD OF" triggers.
• DDL triggers do not create the special inserted and deleted tables.
• DDL triggers can also be triggered by system stored procedures that execute DDL-like events.
• DDL triggers are not triggered by events affecting local or global temporary tables and stored procedures.

Syntax

CREATE TRIGGER <trigger_name>
ON [ALL SERVER | DATABASE]
WITH <ddl_trigger_option>
FOR [event_type(s)]
AS
BEGIN
<trigger_body>
END

Arguments

• trigger_name: Choose a name for your new trigger. A "trigger_name" must follow the rules for identifiers, and it cannot begin with # or ##.
• ALL SERVER: Implements the scope of "DDL Trigger(s)" on the current server. (Note: Applies to SQL Server 2008 and later).
• DATABASE: Implements the scope of "DDL Trigger(s)" on the current database.
• WITH <ddl_trigger_option>
  1. ENCRYPTION: It prevents the trigger from being published as part of SQL Server replication. In other words, "WITH ENCRYPTION" encrypts the trigger's code. But, it can't be specified for CLR triggers. (Note: Applies to SQL Server 2008 and later).
  2. EXECUTE AS: It specifies the security context under which the trigger is executed.
  3. SCHEMABINDING: This ensures that the tables referenced by the trigger cannot be altered or dropped. This option is required for triggers on memory-optimized tables and is not supported for triggers on traditional tables.
• event_type: Name of a Transact-SQL language event that, after launch, fires a DDL trigger. Valid events for DDL triggers are listed in DDL Events.

Types of DDL Triggers

T-SQL DDL Trigger

A special type of T-SQL stored procedure that executes one or more T-SQL statements in response to database-scoped or server-scoped events. For example, a DDL trigger may fire if a table is altered or dropped using ALTER or DROP TABLE.

CLR DDL Trigger

A CLR trigger, instead of executing a T-SQL stored procedure, executes one or more managed code methods that are members of an assembly created in the .NET Framework and uploaded to SQL Server.

DDL Trigger Scope

The scope of the trigger depends on the event. DDL triggers can be created either on a specific database or on the server. DDL triggers can be either in database or server scope.

• DDL triggers with SERVER level scope are triggered in response to DDL events having SERVER Scope,
  such as CREATE_DATABASE, DROP _DATABASE, CREATE_LOGIN, ALTER_LOGIN, CREATE_LINKED_SERVER, and so on.

• DDL triggers with DATABASE Scope, on the other hand, fire in response to DDL events with DATABASE Scope,
  such as CREATE _TABLE,  ALTER _TABLE,  DROP_TABLE, CREATE _PROCEDURE, ALTER_PROCEDURE,
  CREATE _FUNCTION, ALTER _FUNCTION, CREATE _VIEW, ALTER_VIEW, and so on.

Note:

Database-scoped DDL triggers are stored as objects in the database in which they are created. To view, Database Scoped Triggers, navigate to <Database> -> Programmability -> Database Triggers folder. (Be sure to refresh, Database Triggers). You can also get information about DDL triggers by querying "sys.triggers". You can query "sys.triggers" in the database context in which the triggers are created or by specifying the database name as an identifier, such as "master.sys.triggers".

Server-scoped DDL triggers are stored in the Server Objects -> Triggers folder (Be sure to refresh, Triggers). However, you can also obtain information about server-scoped DDL triggers by querying the "sys.server_triggers" catalog view in any database context.

ENABLE Trigger

The ENABLE TRIGGER statement enables a trigger so that it will be fired whenever an event happens.

Syntax

ENABLE TRIGGER <trigger_name>
ON [ALL SERVER | DATABASE | Object_Name]

DISABLE Trigger

The DISABLE TRIGGER statement deactivates a trigger so that it cannot be fired. Sometimes, we need to disable it.

Syntax

DISABLE TRIGGER <trigger_name>
ON [ALL SERVER | DATABASE | Object_Name]

DROP Trigger

The DROP TRIGGER statement is used to drop one or more triggers from the database or server.

Syntax

DROP TRIGGER [IF EXISTS] <trigger_name>
ON [ALL SERVER | DATABASE]

The examples in this section demonstrate the usage of DDL Triggers. Let's see.

Respond to changes in the database schema

Follow the given steps to respond to changes in the database schema. Let's see.

Step 1

Execute the following trigger statement to create a DDL Trigger.

IF EXISTS (SELECT * FROM sys.server_triggers WHERE NAME = 'Tr_RespondChanges')  
DROP TRIGGER Tr_RespondChanges  
ON ALL SERVER 
GO

CREATE TRIGGER Tr_RespondChanges   
ON ALL SERVER   
FOR CREATE_DATABASE   
AS   
BEGIN
    SELECT 'New Database Created Successfully...' AS [Welcome Message]
    PRINT '--*----- New Database Created Successfully -----*--'
	PRINT '--*-------------- Have a good day --------------*--'
	PRINT '--*-------- Warm Regards, Onkar Sharma ---------*--'
END
GO 

Note:

You can find the Server Scoped Triggers in, Server Objects -> Triggers. (Be sure to refresh, Triggers)

Step 2

 Now, execute the following query to create the database. And, you will get a custom message on the screen in response to the "Create Database" event.

CREATE DATABASE TestingRC_Trigger

Record Changes or Events (Audit) in the database schema

Follow the given steps to record changes or events in the database schema in SQL Server. Let's see.

Step 1

 First, create a new table named AuditTable to log table schema changes.

CREATE TABLE AuditTable (
    LogId INT IDENTITY PRIMARY KEY,
    Event_Data XML NOT NULL,
    ChangedBy SYSNAME NOT NULL,
	ChangedOn DATETIME NOT NULL
);
GO

Step 2

Now, create a DDL trigger to track table schema changes and insert event data in the AuditTable table.

IF EXISTS (SELECT * FROM sys.triggers WHERE NAME = N'Tr_AuditTables' AND PARENT_CLASS_DESC = N'DATABASE')
BEGIN
	DROP TRIGGER Tr_AuditTables
	ON DATABASE
END
GO

CREATE TRIGGER Tr_AuditTables
ON DATABASE
FOR	
    CREATE_TABLE,
    ALTER_TABLE, 
    DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO AuditTable (
        Event_Data,
        ChangedBy,
		ChangedOn
    )
    VALUES (
        EVENTDATA(),
        USER,
		GETDATE()
    );
END
GO

ENABLE TRIGGER Tr_AuditTables
ON DATABASE
GO

Note:

The "EVENTDATA()" function captures information about the event that fires the DDL trigger and the following changes caused by the trigger. The function is only available inside the DDL triggers.

Step 3

Now, create a table in the database "OnkarSharma_DDLTriggers".

CREATE TABLE [dbo].[tbl_Clients] (
	ClientId INT IDENTITY (504, 1),
	ClientFullName VARCHAR(100) NOT NULL,
	ClientAddress VARCHAR(250) NOT NULL,
	EmailId VARCHAR(50) UNIQUE,
	ContactNo BIGINT NOT NULL,
	PRIMARY KEY (ClientId)
);

Step 4

Now, query the data from the "AuditTable" table to check whether the table creation event was properly captured by the trigger.

SELECT * FROM AuditTable

Note:

If you click on a cell in the "Event_Data" column, you can see the XML data for the event as follows.

Prevent Some Changes to Database Schema in SQL Server

Follow the given steps carefully to prevent tables from being created, altered, or dropped (prevent changes to a database schema) in SQL Server. Let's see.

Step 1

Execute the following trigger statement to create a DDL Trigger.

IF EXISTS (SELECT * FROM sys.triggers WHERE NAME = N'Tr_TablesSecurity' AND PARENT_CLASS_DESC = N'DATABASE')
BEGIN
	DROP TRIGGER Tr_TablesSecurity
	ON DATABASE
END
GO

CREATE TRIGGER Tr_TablesSecurity
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
	PRINT 'You should ask your DBA or disable the trigger ''Tr_TablesSecurity'' to create, alter, or drop the table!' 
	ROLLBACK TRANSACTION
END
GO

ENABLE TRIGGER Tr_TablesSecurity
ON DATABASE
GO

Note:

You can find the Database Scoped Triggers in, <Database_Name> -> Programmability -> Database Triggers. (Be sure to refresh, Database Triggers)

Step 2a

CREATE_TABLE: Now, try to create the table by executing the following statement.

CREATE TABLE [dbo].[tbl_Customer] (
	CustomerId INT IDENTITY (504, 1),
	CustomerFullName VARCHAR(100) NOT NULL,
	CustomerAddress VARCHAR(250) NOT NULL,
	EmailId VARCHAR(50) UNIQUE,
	ContactNo BIGINT NOT NULL,
	PRIMARY KEY (CustomerId)
);

Step 2b

ALTER_TABLE: Now, try adding a new column "DOB" to the table.

ALTER TABLE Employee
ADD DOB BIGINT
GO

Step 2c

DROP_TABLE: Now, try to drop the table.

DROP TABLE tbl_Menu;

Step 3

From the above result, we can clearly see that the "Tr_TablesSecurity" trigger is preventing the user from creating, altering, and dropping the table. Now, disable the trigger by executing the following query.

DISABLE TRIGGER Tr_TablesSecurity
ON DATABASE
GO

Step 4

Now, execute the "Step 2a, 2b, and 2c" queries again. And, to confirm, execute the following queries.

--CREATE_TABLE:
SELECT * FROM OnkarSharma_Security..tbl_Customer;

--ALTER_TABLE:
SELECT * FROM OnkarSharma_Security..Employee;

--DROP_TABLE:
SELECT * FROM OnkarSharma_Security..tbl_Menu;

To read more about "Prevent Some Changes to Database Schema in SQL Server", you may also visit my article,

• Prevent Tables from being Created, Altered, Or Dropped In SQL Server

Difference Between DDL and DML Triggers

Now, let's look at the difference between DDL and DML Triggers. Let's see.

S.No.	Key Points	DDL Triggers	DML Triggers
1	Definition	DDL Triggers fire in response to various DDL events (like CREATE_TABLE, ALTER_TABLE, DROP_TABLE, etc).	DML Triggers fire in response to various DML events (like INSERT, UPDATE, and DELETE).
2	History	DDL Triggers were introduced in SQL Server 2005.	---
3	Used Special Tables?	No	Yes, (Inserted and Deleted)
4	Create Multiple triggers on an object?	Yes	Yes
5	Usage	Can be used to respond, audit, and prevent changes in the database schema.	Can be used to enforce business rules and data integrity.

See you in the next article, till then take care and be happy learning.

You can connect me @

• LinkedIn
• Twitter

You may also visit my other articles,

• Difference Between Delete, Truncate, And Drop Statements In SQL Server
• Difference Between HAVING And WHERE Clause In SQL Server
• How To Generate Scripts For Database Objects In SQL Server
• Different Ways To Handle NULL In SQL Server
• IIF Function In SQL Server

Reference

• DDL Triggers - SQL Server | Microsoft Docs

Conclusion

In this article, we have discussed the concept of DDL Triggers in SQL Server with various examples.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.

Thanks for reading.