Trigger Execution Types

A valuable feature in SQL Server 2005 is the capability to determine when a trigger executes. In addition to the FOR clause (FOR INSERT, FOR UPDATE, or FOR DELETE), you can define two execution types for a trigger as follows:

AFTER   This trigger is fired only after the statement that fired it completes. This is the default for SQL Server 2005. On an UPDATE statement, for example, the trigger will be activated only after the UPDATE statement has completed (and the data has been modified). If the DML statement fails, the AFTER trigger is never fired. You can have multiple AFTER triggers on a table (views are not supported, by the way) and list the triggers in an order of execution (see “Managing Triggers” later in this chapter). (By-the-by, AFTER triggers are never executed if a constraint violation arises.)

INSTEAD OF   This trigger is fired instead of the actual triggering action. For example, if an UPDATE arrives on the wire and an INSTEAD OF trigger is defined, the UPDATE is never executed but the trigger statement is executed instead. By contrast with its AFTER sibling, you can define an INSTEAD OF trigger for either a table or a view. INSTEAD OF triggers can be used for many scenarios, but one of the fanciest features is the capability to update view data, which are not normally updatable. As explained in the Chapter 15, it is not a simple matter to just obtain a fresh view of data using a view that has been around for a while.

Although the INSTEAD OF triggers are fired instead of the DML statement sent to the server, they fire before anything else, including any constraints that may have been defined (which is a big difference between the INSTEAD OF trigger and the AFTER trigger). The triggers are also not recalled, because SQL Server checks for recursion that might develop as the result of the actions the trigger itself takes on the table. In other words, it is certainly possible to create a trigger that practically replaces exactly what the original DML had intended to do to the table or view. It thus seems logical that the trigger would cause itself to be refired, but this unintended recursion is dampened.

Note 

While trigger overhead is very low, a time may come when you need to squeeze every drop of bandwidth out of your application. It thus makes sense to write trigger code within a trigger, such as flow-control logic (IF, CASE), that checks if the trigger really needs to run its course. For example, an INSTEAD OF trigger might check the underlying table state before it performs anything, and exit out if it determines the entire statement is not required. Also, if the constraint can be catered to using the check constraints described in Chapter 12, go with those because they incur much less overhead than triggers for simple integrity constraints.