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 next type of triggers we will talk about are 'instead of' triggers. These triggers are executed as an alternative to an INSERT, UPDATE, or DELETE statement. These triggers completely block the original statement leaving the data unchanged. However, inside the body of the trigger we can choose to modify data and we usually do. In this example, I'll be of using a trigger to enforce a data integrity rule. The rule states that we cannot ever delete a category. We must just mark that category as inactive.
So if someone tries to delete the category, I want to trigger to block the action and instead update the record and set active equal to 0. I staged some code for you in your exercise file. Let's talk about this code. CREATE TRIGGER CategoryDelete on (dbo).(Categories) and line 3 says INSTEAD OF DELETE. So again that will prevent the DELETE statement from happening. Line 6, 7, and 8 perform an update on the categories table instead of the delete.
It's going to set active equal to 0 where the category is in and then we select from the deleted table. The deleted table is a temporary table automatically created by the trigger that contains the data the user was trying to delete. So let's go ahead and run this. Command successful. We can refresh our trigger section and we should see CategoryDelete. That's good news. Now let's test it. We can select from our categories table and see what it looks like now.
And we see category number 2 Pants is currently marked as active. Let's see what would happen if someone tried to delete that category. What should've happened is the trigger should've caught this DELETE statement, blocked the DELETE statement, and turned it into an update instead. That appears to be exactly what happened. Category two is still there. It has not been deleted it, but instead the trigger marked it as inactive. It certainly looks like success.
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.