From the course: SQL Server Database Triggers

Two triggers types: INSTEAD OF and AFTER - SQL Server Tutorial

From the course: SQL Server Database Triggers

Start my 1-month free trial

Two triggers types: INSTEAD OF and AFTER

“

- [Instructor] Triggers are fired when SQL Server receives a command to perform a specific task on a specific object. Some example triggering events might be when a row insert is performed the customer's table. Or when a table is created in the warehouse database. These triggering events help to find the when component of the trigger. And help answer the question: When will this trigger fire? But there's one more piece of information that SQL Server needs to know in order to fire the trigger at the right time. This is the difference between and after trigger and an instead of trigger. An after trigger will fire once the triggering event has completed successfully. This includes after validating all key and check constraints and making changes to the transaction log. You can think of an after trigger as additional commands that are sent to the server for processing. The instead of trigger, on the other hand, will completely replace the original commands with those contained in the trigger. This effectively takes whatever actions were used to fire the trigger, throws them away, and substitutes an entirely new procedure. There are a couple of differences in how you can apply an after trigger versus an instead of trigger. First, you can have multiple after triggers attached to a triggering event. But you can only have one instead of trigger per event. We'll talk later in this course about how you can ensure your after triggers fire in the intended sequence when you have more than one trigger on the same event. After triggers cannot be applied to view objects, where instead of trigger can. This is useful, for instance, if someone tries to update a row of data in a non-updateable view. You can have an instead of trigger step in and reroute those instructions to the appropriate table objects instead. After triggers can be used on data definition language and login events, where instead of triggers cannot. And finally, it's worth noting that neither after or instead of triggers can be applied to temporary tables. There are a few other fringe restrictions on when you can or cannot use these two trigger types. Mostly, these involve conflicts with other SQL Server features. For instance, you cannot have an instead of delete trigger on a table if the table has a foreign key referential relationship with a cascade on delete specification. These types of limitations apply to very specific situation, and are too numerous to list out every conflict that you might run into here. So if you do run into issues creating a trigger, I'd recommend that you review the full documentation on trigger limitations at this URL.

Contents