Server-scoped ddl triggers

Suggested Videos
Part 90 - Difference between union intersect and except in sql server
Part 91 - Cross apply and outer apply in sql server
Part 92 - DDL Triggers in sql server



In this video we will discuss server-scoped ddl triggers



The following trigger is a database scoped trigger. This will prevent users from creating, altering or dropping tables only from the database in which it is created.

CREATE TRIGGER tr_DatabaseScopeTrigger

ON DATABASE

FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE

AS

BEGIN

    ROLLBACK

    Print 'You cannot create, alter or drop a table in the current database'

END

If you have another database on the server, they will be able to create, alter or drop tables in that database. If you want to prevent users from doing this you may create the trigger again in this database.

But, what if you have 100 different databases on your SQL Server, and you want to prevent users from creating, altering or dropping tables from all these 100 databases. Creating the same trigger for all the 100 different databases is not a good approach for 2 reasons.
1. It is tedious and error prone
2. Maintainability is a night mare. If for some reason you have to change the trigger, you will have to do it in 100 different databases, which again is tedious and error prone.

This is where server-scoped DDL triggers come in handy. When you create a server scoped DDL trigger, it will fire in response to the DDL events happening in all of the databases on that server.  

Creating a Server-scoped DDL trigger : Similar to creating a database scoped trigger, except that you will have to change the scope to ALL Server as shown below.

CREATE TRIGGER tr_ServerScopeTrigger

ON ALL SERVER

FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE

AS

BEGIN

    ROLLBACK

    Print 'You cannot create, alter or drop a table in any database on the server'

END

Now if you try to create, alter or drop a table in any of the databases on the server, the trigger will be fired.

Where can I find the Server-scoped DDL triggers
1. In the Object Explorer window, expand "Server Objects" folder
2. Expand Triggers folder


To disable Server-scoped ddl trigger
1. Right click on the trigger in object explorer and select "Disable" from the context menu 
2. You can also disable the trigger using the following T-SQL command

DISABLE TRIGGER tr_ServerScopeTrigger ON ALL SERVER

To enable Server-scoped ddl trigger
1. Right click on the trigger in object explorer and select "Enable" from the context menu 
2. You can also enable the trigger using the following T-SQL command
ENABLE TRIGGER tr_ServerScopeTrigger ON ALL SERVER

To drop Server-scoped ddl trigger
1. Right click on the trigger in object explorer and select "Delete" from the context menu 
2. You can also drop the trigger using the following T-SQL command
DROP TRIGGER tr_ServerScopeTrigger ON ALL SERVER