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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98181 Viewers
61 Video lessons · 85482 Viewers
71 Video lessons · 69439 Viewers
56 Video lessons · 101790 Viewers
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.
Your file was successfully uploaded.