Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
This course investigates several key database-programming concepts: triggers, stored procedures, functions, and .NET CLR (Common Language Runtime) assemblies. Author Martin Guidry shows how to combine these techniques and create a high-quality database using Microsoft SQL Server 2012. The course also covers real-world uses of the INSERT, UPDATE, and DELETE procedures, and how to build a basic web form to connect to your database.
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.
There are currently no FAQs about SQL Server: Triggers, Stored Procedures, and Functions.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.