DDL Triggers in SQL Server

DDL Triggers in SQL Server

In this article, I will discuss the DDL Triggers in SQL Server with some examples. If you want to learn the DML Triggers in SQL Server, then I strongly recommended you to read the below articles where we discussed the DML triggers with real-time examples in SQL Server.

DML Triggers in SQL Server

Magic Tables in SQL Server

DML Trigger Real-Time Examples in SQL Server

Instead Of Insert Trigger in SQL Server

The Instead Of Delete Trigger in SQL Server

Instead Of Update Trigger in SQL Server

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 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 more over the DDL triggers will not fire in response to events that affect the local temporary tables.

Syntax:

DDL Triggers in SQL Server

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

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 database of that server.

When to use DDL triggers?

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 have 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)

In this article, we will discuss database scoped triggers and in the next article, we will discuss server scoped triggers.

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

First Create a database with the name SQL_TESTING_DB

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

To check whether the trigger is created or not

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

DDL Triggers in SQL Server

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 which 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 which 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.

To drop trigger

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
To disable trigger

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

To enable trigger

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 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 below 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.

DDL Triggers in SQL Server

In the next article, I will discuss the server-scoped DDL Triggers in SQL Server with some examples.

SUMMARY

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.

Leave a Reply

Your email address will not be published. Required fields are marked *