SQL Server tutorial for beginners - Server scoped ddl triggers

ddl trigger server level sql server level ddl trigger ddl trigger on all server create trigger on all server for ddl events In this video we will discuss server-scoped ddl triggers Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 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 Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/server-scoped-ddl-triggers.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/server-scoped-ddl-triggers_10.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists