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.

CREATETRIGGER tr_DatabaseScopeTrigger

ONDATABASE

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

CREATETRIGGER tr_ServerScopeTrigger

ONALLSERVER

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 triggers1. In the Object Explorer window, expand "Server Objects" folder2. Expand Triggers folder

To disable Server-scoped ddl trigger1. 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

DISABLETRIGGER tr_ServerScopeTrigger ONALLSERVER

To enable Server-scoped ddl trigger1. 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 commandENABLETRIGGER tr_ServerScopeTrigger ONALLSERVER

To drop Server-scoped ddl trigger1. 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 commandDROPTRIGGER tr_ServerScopeTrigger ONALLSERVER