DDL Triggers in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

DDL Triggers in SQL Server with Examples

In this article, I am going to discuss the DDL Triggers in SQL Server with examples. Please read our previous article where we discussed Instead of Triggers in SQL Server. At the end of this article, you will understand the following pointers in detail which are related to DDL Triggers in SQL Server.

1. What are DDL TRIGGERS in SQL Server?
2. Types of DDL triggers?
3. Database Scoped DDL Triggers in SQL Server
4. How to Create a Database-Scoped DDL Trigger?
5. Where can I find the Database-scoped DDL triggers?
6. How to drop, enable and disable a Database Scoped DDL trigger?
7. Server-scoped DDL Triggers in SQL Server
8. How to Create a Server-Scoped DDL Trigger?
9. Where can I find the Server-scoped DDL triggers?
10. How to drop, enable and disable a Server Scoped DDL trigger?
11. A real-time example of DDL Trigger

What are DDL TRIGGERS in SQL Server?

The DDL triggers in SQL Server are fired in response to a variety of data definition language (DDL) events such as Create, Alter, Drop, Grant, Denay, and Revoke (Table, Function, Index, Stored Procedure, etc…). That means DDL triggers in SQL Server are working on a database.

DDL triggers are introduced from SQL Server 2005 version which will be used to restrict the DDL operations such as CREATE, ALTER and DROP commands.

We can think of a DDL trigger as a special kind of stored procedure that executes in response to a server scoped or database scoped events. We will discuss the examples of both server scoped and database scoped.

The point to remember is that the DDL triggers fire only after the DDL statements execute so we cannot use the “Instead Of Triggers” here and moreover the DDL triggers will not fire in response to events that affect the local temporary tables.

Syntax:

Here Event_Type refers to the event that will fire the trigger which can be anything like Create_Table, Drop_Table, Alter_Table, etc.

Types of DDL triggers in SQL Server?

There are two types of DDLs triggers available in SQL Server. They are as follows:

1. Database Scoped DDL Trigger
2. Server Scoped DDL Trigger

The DDL triggers can be created in a specific database or at the server level. If we set the scope to server-level then it is applied to all the databases of that server. Here, in this article, first, we will discuss the database scoped triggers, and then, we will discuss server scoped triggers.

Database Scoped DDL Triggers in SQL Server

First Create a database with the name SQL_TESTING_DB

Example1: Create a trigger that will restrict creating a new table on a specific database.

USE SQL_TESTING_DB
GO

CREATE  TRIGGER  trRestrictCreateTable 
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  PRINT 'YOU CANNOT CREATE A TABLE IN THIS DATABASE'
  ROLLBACK TRANSACTION
END

Where can I find the Database-Scoped DDL triggers?

In the Object Explorer window expand the SQL_TESTING_DB database by clicking on the plus symbol. Expand the Programmability folder. Then Expand the Database Triggers folder as shown below

Note: If you can’t find the trigger that you just created in the SQL_TESTING_DB database, make sure to refresh the Database Triggers folder. When you execute the following code to create a table, the trigger will automatically fire and will print the message – YOU CANNOT CREATE A TABLE IN THIS DATABASE

CREATE TABLE tblTest (ID INT)

Example2: Create a trigger that will restrict ALTER operations on a specific database table.

CREATE TRIGGER  trRestrictAlterTable  
ON DATABASE
FOR  ALTER_TABLE
AS
BEGIN
  PRINT 'YOU CANNOT ALTER TABLES'
  ROLLBACK TRANSACTION
END

Example3: Create a trigger that will restrict dropping the tables from a specific database.

CREATE TRIGGER  trRestrictDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
  PRINT 'YOU CANNOT DROP TABLES'
  ROLLBACK TRANSACTION
END

Note: We cannot implement business logic in DDL Trigger. To be able to create, alter or drop a table we either have to disable or delete the trigger.

How to drop a Database Scoped DDL trigger in SQL Server?

Right-click on the trigger in object explorer and select “Delete” from the context menu. We can also drop the trigger using the following T-SQL command

DROP TRIGGER trRestrictCreateTable ON DATABASE
DROP TRIGGER trRestrictAlterTable ON DATABASE
DROP TRIGGER trRestrictDropTable ON DATABASE

Let us see an example of how to prevent users from creating, altering, or dropping tables from a specific database using a single trigger.

CREATE TRIGGER trRestrictDDLEvents
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN 
   PRINT 'You cannot create, alter or drop a table'
   ROLLBACK TRANSACTION
END

How to disable a Database Scoped DDL trigger in SQL Server?

Right-click on the trigger in object explorer and select “Disable” from the context menu. We can also disable the trigger using the following T-SQL command

DISABLE TRIGGER trRestrictDDLEvents ON DATABASE

How to enable a Database Scoped DDL trigger in SQL Server?

Right-click on the trigger in object explorer and select “Enable” from the context menu. We can also enable the trigger using the following T-SQL command

ENABLE TRIGGER trRestrictDDLEvents ON DATABASE

Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. The following trigger will be fired whenever we rename a database object using the sp_rename system stored procedure.

CREATE TRIGGER trRenameTable
ON DATABASE
FOR RENAME
AS
BEGIN
    PRINT 'You just renamed something'
END

Let’s create a table and test this.

First, disable the trRestrictDDLEvents trigger: DISABLE TRIGGER trRestrictDDLEvents ON DATABASE

Then create a table using the command: CREATE TABLE tblTest (ID INT)

The following code changes the name of the table tblTest to tblTestChanged. When this code is executed, it will fire the trigger trRenameTable automatically.

sp_rename ‘tblTest’, ‘tblTestChanged’

When we execute the above code, it will display the below output.

Server-scoped DDL Triggers in SQL Server:

Let’s understand the need for a Server-Scoped DDL Trigger with an example. We already created the following trigger.

CREATE TRIGGER trRestrictDDLEvents
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN 
   PRINT 'You cannot create, alter or drop a table'
   ROLLBACK TRANSACTION
END

The above trigger is an example of a Database Scoped DDL Trigger. This trigger will prevent the users from creating, altering, or dropping tables only from the database on which it is created. 

But, if we have another database on the same server, then the users will be able to create, alter or drop tables in that database. So, if we want to prevent the users from creating, altering, or dropping tables from that database then we need to create the trigger again in that particular database.

Think of a situation, where we have 50 databases on a particular server and we want to prevent the users from creating, altering, or dropping tables from all those 50 databases. Creating the same trigger again and again for all those 50 databases is bad for the following two reasons.

1. It is a tedious process as well as error-prone
2. Maintainability is a nightmare. If for some reason we have to change the trigger, then we will have to do it in all the 50 databases, which again is a tedious process as well as error-prone.

This is the ideal situation where the Server-Scoped DDL triggers come into the picture. When we create a server-scoped DDL trigger, then it will be fired in response to the DDL events happening in all of the databases on that particular server.

How to Create a Server-Scoped DDL Trigger in SQL Server?

Creating a server scoped DDL trigger in SQL Server is very much similar to creating a database scoped DDL trigger, except that we will have to change the scope to ALL Server as shown in the below example.

CREATE TRIGGER trServerScopedDDLTrigger
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN 
   PRINT 'You cannot create, alter or drop a table in any database of this server'
   ROLLBACK TRANSACTION
END

Now if you will try to create, alter or drop a table in any of the databases on that particular server, then the above Server-Scoped DDL trigger will be fired.

Where can I find the Server-scoped DDL triggers?

To find the Server-Scoped DDL Triggers in SQL Server Follow the below steps

1. In the Object Explorer window, expand the “Server Objects” folder
2. Then Expand the Triggers folder as shown in the below image

How to disable Server-Scoped DDL trigger in SQL Server?

Right-click on the trigger in object explorer and select “Disable” from the context menu. We can also disable the trigger using the SQL Command as DISABLE TRIGGER trServerScopedDDLTrigger ON ALL SERVER

How to enable Server-Scoped DDL trigger in SQL Server?

Right-click on the trigger in object explorer and select “Enable” from the context menu. We can also enable the trigger using the T-SQL command as ENABLE TRIGGER trServerScopedDDLTrigger ON ALL SERVER 

How to drop Server-scoped DDL trigger in SQL Server?

Right-click on the trigger in object explorer and select “Delete” from the context menu. We can also drop the trigger using the SQL command as DROP TRIGGER trServerScopedDDLTrigger ON ALL SERVER

DDL Trigger Real-Time Example in SQL Server:

The DDL triggers in SQL Server will be very much handy to audit and control the DDL changes in a database. Below are such real-time scenarios:

1. To Track the DLL changes
2. Track the DDL statement which is fired
3. Who has fired the DDL statements? For example, we may be interested in identifying who has dropped the table or who has modified the table.
4. When the DDL statement is fired.
5. Block the user from doing some DDL changes like DROP TABLE, DROP PROCEDURE, etc.
6. Allow the DDL changes only during a specified window (i.e. only during particular hours of the day)

So let us discuss how to audit table changes in SQL Server using a DDL trigger.

-- Create the TableAudit table
CREATE TABLE TableAudit
(
    DatabaseName nvarchar(250),
    TableName nvarchar(250),
    EventType nvarchar(250),
    LoginName nvarchar(250),
    SQLCommand nvarchar(2500),
    AuditDateTime datetime
)
Go

-- The following trigger audits all table changes in all databases on a particular Server
CREATE TRIGGER tr_AuditTableChangesInAllDatabases
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    DECLARE @EventData XML
    SELECT @EventData = EVENTDATA()

    INSERT INTO SQL_TESTING_DB.dbo.TableAudit
    (DatabaseName, TableName, EventType, LoginName,
     SQLCommand, AuditDateTime)
    VALUES
    (
         @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(250)'),
         @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(250)'),
         @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)'),
         @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(250)'),
         @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2500)'),
         GetDate()
    )
END

In the above example, we are using the EventData() function which will returns event the data in XML format. The following XML is returned by the EventData() function when I created a table with name = MyTestTable in SQL_TESTING_DB database.

CREATE TABLE MyTestTable
(
    Id INT,
    Name VARCHAR(50),
    Gender VARCHAR(50),
    Salary INT
)

Once you create the above table then select the TableAudit as shown below

SELECT * FROM TableAudit will give us the below output.

The EVENTDATA() function give us the data in below XML Format

<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime> 2018-10-10 22:05:37.453 </PostTime>
  <SPID>58</SPID>
  <ServerName> LAPTOP-2HN3PT8T </ServerName>
  <LoginName>LAPTOP-2HN3PT8T\Pranaya</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>SQL_TESTING_DB</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>MyTestTable</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
                ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
                ENCRYPTED="FALSE" />
    <CommandText>
     CREATE TABLE MyTestTable
     (
       Id INT,
       Name VARCHAR(50),
       Gender VARCHAR(50),
       Salary INT
      )
   </CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

In the next article, I am going to discuss the Triggers Execution Order in SQL Server with examples. Here, in this article, I try to explain the DDL Triggers in SQL Server step by step with some real-time examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.