DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers

In SQL Server, there are four types of triggers. First, we have DML triggers, where DML stands for Data Manipulation Language. We discuss these triggers in the post below.

• DML triggers ,After Insert,After Delete Trigger with examples
• After Update Trigger in Sql Server With Example
• Instead Of Insert Triggers in Sql server With Example
• Instead Of Update Trigger In Sql Server With Example

And then we have DDL triggers, where DDL stands for Data Definition Language. In this post, we'll discuss DDL triggers. Additionally, we have CLR triggers, where CLR stands for Common Language Runtime, and finally, Log-On triggers.

1. DML triggers
2. CLR triggers
3. Log-On triggers

What Are DDL Triggers?

DDL triggers are executed in response to DDL (Data Definition Language) events. Now, the immediate question that arises is: what are DDL events and when are these events raised?

Whenever you create, alter, or drop a database object, a corresponding DDL event is raised. For instance, when you create a table using the 'CREATE TABLE' DDL statement, the associated event is 'create_table', and this event is raised. 

If you have a trigger associated with that event, then when you create a table, the associated trigger will be automatically fired.

Similarly, when you drop a stored procedure, the 'drop_procedure' event is raised. When you create a function, the 'create_function' DDL event is raised.

For the full list of DDL events, please visit MSDN link- MSDN ddl events

So, whenever you execute DML statements, associated DDL events are raised, and if you have triggers associated with those events, they are fired automatically.

Not only do DML statements fire DDL triggers, but we also have system stored procedures that perform DDL-like operations, and these system stored procedures can also fire DDL triggers.

One such system stored procedure is sp_rename. We use the sp_rename system stored procedure to rename a database object. For example, we can use it to rename a table or a column in a table. Whenever we do that using the system procedure, it raises the rename event, and if you have a trigger associated with that event, it will be fired automatically when you rename an object.

What are the uses of DDL triggers?

There are several uses of DDL triggers .

• For example, if you want to execute some code in response to a specific DDL event, you can do that because triggers are fired in response to DDL events. So, you can put whatever code you want to execute within the body of that trigger. Whenever you create a table, the 'create_table' event is raised, and the associated trigger will execute that code.
• Similarly, if you want to prevent changes to your database schema, you can use a DDL trigger. For instance, if you want to prevent users from creating, altering, or dropping tables, you can achieve that using a DDL trigger.
• You can also use a trigger to audit the changes that users make to the database structure. For example, you can capture all the changes made to the database schema and store them in a table. If somebody modifies a table, you can capture information such as the login name of the user who made the modification, the name of the database in which the table is located, the date and time of the modification, the name of the table, and the exact statement executed to perform the modification. All this information can be captured using a trigger.

Syntax for creating a DDL trigger

Here, we have the syntax for creating a DDL trigger. We begin with 'CREATE TRIGGER', followed by the trigger name. Then, we use the 'ON' keyword to indicate the trigger's scope.

DDL triggers can be created either in a specific database or as a server-wide trigger. If you want to create a trigger scoped to a database, we use the 'DATABASE' keyword; otherwise, use 'SERVER' for a server-wide trigger."

Database scoped  DDL triggers

CREATE TRIGGER [Your_Trigger_Name]
ON [Trigger Scope (Server OR Database)]
FOR [Event1, Event2, Event3, ...Eventn],
AS
BEGIN
-- Your Trigger Body
END

So, if you want these triggers to be fired for three events: 'CREATE_TABLE', 'DROP_TABLE', and 'ALTER_TABLE', you simply separate those events using commas. Then, you use the 'AS' keyword followed by 'BEGIN' and 'END'. Within 'BEGIN' and 'END', you'll have your trigger body.

Let’s look at an example now. Here we have a very simple example: 'CREATE TRIGGER', followed by the name of the trigger. Then, we use the 'ON' keyword and specify the database scope.

CREATE TRIGGER trOnTableCreateTrigger
ON Database
FOR CREATE_TABLE
AS
BEGIN
Print 'You have created a table in database'
END

We are creating a trigger with a database scope for the 'CREATE_TABLE' event. Whenever you execute the 'CREATE TABLE' DML statement, the 'CREATE_TABLE' event is raised. With a trigger associated with that event, whenever you create a table, this trigger will automatically be fired. It will print this message: 'You have created a table in the database'.

How to find database scoped ddl triggers in sql server

Navigate to the 'Programmability' folder, and within that, expand 'Database Triggers'. If you can't find the trigger that you have just created, check this location.

Right-Click on that and select refresh from the context menu and you should find the trigger.

So that's our trigger.

Now, here we have a create table statement, which is going to create a table with one column. When we execute this, it should automatically print a message because the 'trOnTableCreateTrigger' trigger will be fired.

So the trigger is fired in response to a single event. Now, let’s say I want this trigger to be fired for alter and drop table events as well. If that’s the case, you simply need to separate the event names using a comma.

ALTER TRIGGER trOnTableCreateTrigger
ON Database
FOR CREATE_TABLE,ALTER_TABLE, DROP_TABLE
AS
BEGIN
Print 'New table created or modified'
END

Now if you try to create, alter or drop a table, the DDL trigger will fire and you will see the text.

Another use of triggers is to prevent certain changes to your database schema. Let’s say whenever somebody tries to create, alter, or drop a table, I don’t want that to happen. I can achieve this using a DDL trigger. Within the trigger, I simply use the `ROLLBACK` statement.

CREATE TRIGGER [trOnTableCreateTrigger]
ON Database
FOR CREATE_TABLE,ALTER_TABLE, DROP_TABLE
AS
BEGIN
Rollback
Print 'You do not have permission to change the database'
END
GO

Now if you try to create, alter or drop a table, the DDL trigger will fire and you will see the below message.

Now, the only way to create, alter, or drop a table is by either disabling the trigger or deleting that trigger. To disable the trigger, you can use the simple command:

• DISABLE TRIGGER trOnTableCreateTrigger ON DATABASE
• ENABLE TRIGGER trOnTableCreateTrigger ON DATABASE
• DROP TRIGGER trOnTableCreateTrigger ON DATABASE

Server scoped  DDL triggers

Here we have a database-scoped trigger. We have specified the scope as a database. Look at what the trigger is doing. It’s preventing users from creating, altering, or dropping a table.

ALTER TRIGGER [trOnTableCreateTrigger]
ON Database
FOR CREATE_TABLE,ALTER_TABLE, DROP_TABLE
AS
BEGIN
Rollback
Print 'You do not have permission to change the database'
END
GO

Let’s we have two databaste in our server DemoDB  and Demo Database.

We created this trigger statement within the context of the DemoDB database. So this trigger will now be created in that database. If we try to create a table within the DemoDB database, that trigger should prevent us from doing that, and we get the error message.

Now, this trigger is present only within that DemoDB database because it is scoped to that database.

I have another database in our server. Now, if I try to create a table within that database, will I be allowed to do that? Yes, when I execute the create table statement in the context of Demo, notice that we can create the table without any problem.

Now let’s say, for some reason, even in the demo database or in all databases on our server, we want to prevent users from creating, altering, or dropping tables.

Now, one way to achieve this is by creating the same trigger in all databases. This approach is okay if we have just one or two databases.

However, imagine if we have 100 databases on an instance of SQL Server. And in all those 100 different databases, we want to prevent users from creating, altering, or dropping tables.

In this case, creating the same trigger in all those 100 different databases is not the right approach. And it’s not right for two reasons.

Firstly, it is tedious and error-prone. Maintainability is going to be a nightmare because if we have to change the logic in the trigger, then we will have to make the change in all the 100 different databases, which again, is going to be tedious and error-prone.

So, this is the case where server-scoped triggers are going to come in handy. Creating server-scoped triggers is very similar to creating database-scoped triggers. All you have to do is change the scope from the database to all servers. Let’s create a server-scoped trigger first."

Server Scoped DDL Trigger

CREATE TRIGGER tr_ServerScopeTblTrigger
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
ROLLBACK
Print 'You do not have permission to change the on the server'
END

Now, let’s go ahead and execute this `CREATE TRIGGER` statement. Where does this trigger get created?

This trigger is actually created at the server level. So, we have the server objects folder. If we expand that, we have the triggers folder, and when we expand that, we can find our server-scoped trigger there.

Now let’s try to create a table within DemoDB database, but we should still be prevented from doing that.

Let’s try to create a table with a Demo database, but we should still be prevented from doing that.

Now, if I attempt to delete the table using the graphical user interface of SQL Server Management Studio, will I be allowed to do that? No.

When I try to delete it by clicking on it, selecting delete, and then clicking OK, I notice that we still get an error message.

And if you look at the error message, it says 'Drop failed for table test'.

The message states: 'The transaction ended in the trigger.' We receive the same error message. Regardless of whether you use a graphical user interface or a SQL command, you will still not be able to do that because the trigger is currently preventing it.

How disable Server-scoped DDL trigger?

We can easily disable the trigger using the below SQL command

DISABLE TRIGGER tr_ServerScopeTblTrigger ON ALL SERVER

How to enable the Server-scoped DDL trigger?

We can easily disable the trigger using the below SQL command

ENABLE TRIGGER tr_ServerScopeTblTrigger ON ALL SERVER

How to drop the Server-scoped DDL trigger?

We can easily disable the trigger using the below SQL command

DROP TRIGGER tr_ServerScopeTblTrigger ON ALL SERVER