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

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

• 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, DDL stands for Data Definition Language. In this post, we’ll discuss DDL triggers, and then we have CLR triggers, 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 events. So the immediate obvious question that comes to our mind is what are DDL events and when are these events raised?

whenever you create, alter, or drop a database object, then a corresponding DDL event is raised. For example, when you create a table using the create table DDL statement, the associated event is create_table. So that event is raised.

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

Similarly, when you drop a stored procedure, the drop_procedure event is raised. When you create a function, 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 the DML statements are going to fire, DDL triggers. We also have system stored procedures that perform DDL like operations and these systems stored procedure can also fire DDL triggers.

One Such system stored procedure is sp_rename. We use 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. So whenever we do that using the system procedure, it’s going to raise 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 the triggers are fired in response to DDL events. So you can put whatever you want to execute within the body of that trigger. And whenever you create a table, create_table event is raised and the associated trigger is going to execute that code.
• Similarly, if you want to prevent changes to your database schema. You can use a DDL trigger. For example, let’s say you want to prevent users from creating, altering, or dropping tables. You can do that using a DDL trigger.
• you can also use a trigger to audit the changes that the users are making to the database structure. Now, let’s say whatever changes people are making to the database schema, I want to capture all those changes and I want to audit those changes and maybe store them in a table.We can very easily achieve that using a DDL trigger.
• For example, if somebody modifies a table, I want to capture information like what is the login name of the user who modified that?What is the name of the database in which that table is present? What is the DateTime? who did that modification and What is the name of the table? . What is the exact statement that they have executed to do that modification? All that information can be captured using a trigger.

Syntax for creating a DDL trigger

here we have the syntax for creating a DDL trigger. So here,  we start with create trigger and then we have the trigger name, and then we use the ‘on‘ keyword and then we specify the scope of the trigger.

We can create DDL triggers in a specific database or a server-wide trigger. If you want to create a trigger whose scope is the database, then you use the database keyword otherwise, server.

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 this triggers to be fired for three events, Create_table, drop_table, and alter_table, You simply separate those events using a comma and then you use the “AS” would begin , END within begin and END you’ll have your trigger body.

Let’s look at an example now. So here we have a very simple example. Create trigger the name of the trigger and then we use the ON Keywood and 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 whose SCOPE is database and then for CREATE_TABLE. So the name of the event is CREATE_TABLE.

whenever you execute the create table DML statement, CREATE_TABLE event is raised and we have a trigger associated with that event. So whenever you create a table, this trigger will be automatically be fired. That’s going to print this message. You have created a table in database

How to find database scoped ddl triggers in sql server

Go to the programmability folder and within that expand database triggers. If you can’t find the trigger that you have just created.

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 created a table statement, which is going to create a table with one column. So when we execute this, it should automatically print a message because this trOnTableCreateTrigger  trigger will be fired.

So 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 that you can prevent certain changes to your database schema. let’s say whenever somebody tries to create or alter or drop a table, I want I don’t want that to happen

I want to prevent those changes. I can do that using a DDL trigger. So within the trigger, I am simply going to say rollback.

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. And 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. and 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 be now created in that database. And if we try to create a table within the DemoDB database, that trigger should prevent us from doing that. we get the error message.

Now, this trigger is present only within that DemoDB database because this is a trigger that 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 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 database in our server, we want to prevent users from creating, altering, or dropping tables.

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

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

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

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

So this is the case when server code triggers are going to come in handy.Creating server code triggers is very similar to creating database scoped triggers. All you have to do is change the scope from the database to all servers. So let’s create a server scoped to 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 create?

This is actually created on the server level. So we have the server objects folder. If we expand that, we have got 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 here we are using a SQL command to do that. Now, if I try 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 that and selecting delete and when I click, OK, I notice that we still get an error message.

And if you look at what the error message is, it says drop fail for table test.

And here’s the message. The transaction ended in the trigger. We get the same error message. Irrespective 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 preventing it right now to create a trigger.

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

The post DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers appeared first on Software Development | Programming Tutorials.