Using "after" triggers
Video: Using "after" triggersIn this chapter, I'll be working with two new tables; a products table and a categories table. I've created a script that will generate those tables and populate them with some data. There in your exercise files. You can copy and paste all of that into Management Studio and run it. And it should've given me two new tables. And let's double check and make sure we have some data in those tables. Oh, that looks good. The first type of trigger I'd like to talk about is called an 'after' trigger.
- Next steps
Viewers: in countries Watching now:
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.
- Comparing triggers, functions, and stored procedures
- Installing and configuring SQL Server
- Creating a stored procedure
- Returning data using data sets
- Creating user-defined functions
- Using "after," "instead," and nested triggers
- Modifying existing stored procedures
- Implementing logging on DELETE
- Choosing between T-SQL and CLR
- Executing a stored procedure
- Passing parameters
Using "after" triggers
In this chapter, I'll be working with two new tables; a products table and a categories table. I've created a script that will generate those tables and populate them with some data. There in your exercise files. You can copy and paste all of that into Management Studio and run it. And it should've given me two new tables. And let's double check and make sure we have some data in those tables. Oh, that looks good. The first type of trigger I'd like to talk about is called an 'after' trigger.
It's called this because it executes immediately after an insert, update or DELETE statement. The trigger allows the original statement to occur with no modifications. Then it immediately takes over and starts doing some work. In this example, we're going to use the trigger to enforce a business rule. The business rule states that whenever a category is marked as inactive, all of the products in that category should immediately be marked as inactive also. So I have some code staged for this in your exercise files.
Just like with functions and stored procedures, we start with the keyword Create and then we use the keyword Trigger. Then we have to give it a name. I called mine CategoryDeactivation. Line 2, we are setting a relationship with a table. Every 'after' trigger is related to one table and is going to react to actions only on that table. Line 3 says AFTER UPDATE. So that's the one verb I'm interested in. This trigger will ignore, insert and DELETE statements. It will only run immediately after an UPDATE statement.
Then let's look at the bulk of the code; line 6 through 14. We start off on line 6 creating a variable to hold the isActive bit. I want to make sure with the update that came in, was someone trying to set a category as active or trying to set it as inactive. So on line 11 there, I'm looking at the isActive bit in saying it whether or not it is equal to zero. If it is in fact equal to zero, I'll perform the actions that start on line 12 which is UPDATE Products.
Set active equal to zero and then we are saying where category ID is in the category ID from inserted. Notice there on the line 14, where you are querying in a table called inserted. Inserted is a temporary table that's only available inside of the trigger. It's automatically created by the machine and it holds all of the new information that someone is trying to put into the table. So let's go ahead and run this, command(s) completed successfully. That's good.
And now we should be able to go under the categories table and see a new trigger. And there it is, CategoryDeactivation. So to test this, let's first look again at the products table, and we see three items in category one all of them are active. Now I'm going to issue a command that will mark the category one as inactive. When I execute this, it should perform that action and then the trigger will fire and update the product table, and mark everything in category 1 as inactive.
That would appear to be successful. Let's look at the product table just to make sure. And yes, our three products in category 1 are now inactive. We use the trigger to enforce the business rule that whenever a category is marked inactive all of the products should immediately be marked as inactive also.
There are currently no FAQs about SQL Server: Triggers, Stored Procedures, and Functions.