Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The triggers we've seen thus far are all at the table level, meaning they react to an INSERT, UPDATE, or DELETE statement issued to one table. SQL Server also supports triggers at the database level. These trigger fire whenever a statement is issued that would change the structure of that database. I have an example staged for you in your exercise files. This trigger we're saying is on the database on line 2, so that means it applies to the entire database.
Line 3 says it is for any attempt to drop a table or any attempt to alter a table. The body of the trigger is very simple. It's going to PRINT a message say you do not have permission to drop or alter to the table and then is going to ROLLBACK. In other word it's going to undo whatever someone was attempting to do. So I'll execute this, and now we can find these triggers under the Programmability menu. We have a folder called Database Triggers. If I refresh that, I see the new trigger I just created.
So let's go ahead and test this trigger. I'm going to attempt to drop a table and the triggers should stop that from happening. It did in fact say you do not have permission to drop or alter tables. It also said the batch has bit aborted. Meaning it completely blocked me from happening. And if I do a refresh, I see clearly the Authors table is still there. So good job trigger! You blocked me from possibly making a mistake. I do implement this type of trigger and production databases often, because in my opinion no one should be changing my production databases.
You can make the triggers slightly more robust and check permissions. So check who is trying to do drop and then possibly allow or do not based on that. But this is a nice way to stop some mistakes from happening or possibly even stop some malicious users from doing bad things to your database.
Get unlimited access to all courses for just $25/month.Become a member