INSTEAD OF Triggers

Keep data changes in check with this new trigger type

Since its first release, SQL Server has had a mechanism to provide an automated response whenever a user or an application changes data. This mechanism is a trigger, which is a special kind of stored procedure. Before SQL Server 2000, the only type of trigger available was an AFTER trigger—that is, a set of statements that automatically executes after a data modification takes place. SQL Server 2000 has extended the power of triggers by letting you create a second kind of trigger, called an INSTEAD OF trigger. In this issue, I show you some details about creating and working with INSTEAD OF triggers, which give you the added benefit of being able to examine proposed changes to your data before those changes take place. In addition, you'll see that you can use INSTEAD OF triggers to update certain kinds of views that aren't usually updateable.

Other database products have mechanisms that define a set of actions for the database system to perform before it carries out the specified data modification. That type of mechanism is a true BEFORE trigger. SQL Server's new INSTEAD OF triggers replace the data-modification statement and instruct SQL Server to perform an alternative action. INSTEAD OF triggers are similar to BEFORE triggers in that they are invoked before the data modification takes place, but a true BEFORE trigger would perform the trigger action, then perform the requested modification. Of course, the introduction of INSTEAD OF triggers in SQL Server 2000 doesn't mean that the product will never have true BEFORE triggers—that type of trigger is an option that Microsoft is considering for a future release.

What Was Supposed to Happen?

INSTEAD OF triggers give you the ability to evaluate the changes that would have taken place if the data modification statement had actually executed. Like AFTER triggers, each INSTEAD OF trigger gives you access to two virtual tables called Inserted and Deleted. For a DELETE trigger, the virtual table Deleted holds all the deleted rows, and for an INSERT trigger, the virtual table Inserted holds all the new rows. (For INSTEAD OF triggers, the Deleted table holds the rows that would have been deleted, and the Inserted table holds the rows that would have been inserted, had the modification taken place.) An UPDATE trigger populates both the Inserted and Deleted tables; the Deleted table stores the old version of the rows, and the Inserted table stores the new version.

With all triggers, the Inserted and Deleted tables change their structure to reflect the columns and data types of the table the trigger is attached to. For example, if you have a trigger on the Titles table in the Pubs database, the Inserted and Deleted tables will have all the same columns that the Titles table has, with the same names and data types.

Here's a simple example that compares an INSTEAD OF trigger with an AFTER trigger. Suppose you want to make sure that when someone updates any prices in the Titles table in the Pubs database, the change is no more than 10 percent of the original price. You need to look at both the old price value and the new value to see whether the change is more than 10 percent. You can get the old value from the Deleted table and the new value from Inserted. You must also make sure that the primary key title_id doesn't change so that you can use that key to relate rows in Deleted to the corresponding rows in Inserted.

Listing 1, page 40, contains the AFTER trigger that performs the price check. In SQL Server 2000, you can use the word AFTER instead of FOR; the meaning is equivalent. If your application includes both kinds of triggers, you might use the word AFTER for consistency and to clearly distinguish the two kinds of triggers. And triggers you've created with the word FOR in previous SQL Server releases will still work. Because the AFTER trigger fires after a client updates the Titles table, the trigger must force a rollback if it encounters an error. If no ROLLBACK TRANSACTION occurs, SQL Server has accepted the change and lets it persist.

Now look at the INSTEAD OF trigger that Listing 2, page 40, shows. This trigger fires before the changes happen, but the rows that would have been affected are available in the Inserted and Deleted tables. The basic comparison for price changes greater than 10 percent is the same in this trigger as in the AFTER trigger. The most difficult aspect of using the INSTEAD OF trigger is determining what to have SQL Server do if it deems the update acceptable. If you want to carry out the intended update, you could delete all the rows that match the rows in the Deleted table and insert all the rows from the Inserted table. But in this case, the Titles table has foreign-key references from other tables, so you can't delete rows from Titles. My simple solution, which Listing 2 shows, works on the premise that the price was updated, so the trigger can update the price values in the Titles table based on the Inserted table's contents. If the original UPDATE had set many columns to new values, this trigger would be more difficult to write.

Both these triggers prevent updates to the primary key of the Titles table, and both triggers prevent updates to the price column that would change the price by more than 10 percent. Of course, you probably wouldn't have both these triggers on the Titles table; you would choose to have one or the other because you need to validate the price change only once.

Which Trigger Should You Choose?

The AFTER trigger might appear less efficient because it sometimes needs to undo work that has already been done. If you think your table will have numerous violations that your trigger will need to correct, you might want to choose the INSTEAD OF trigger. However, if the vast majority of your updates will be acceptable, the INSTEAD OF trigger will have more work to do and thus be less efficient. In the case of AFTER triggers, the contents of the Inserted and Deleted tables are directly available from the transaction log. By the time the AFTER trigger executes, the data modification has happened, SQL Server has logged the changes, and the changed records are available internally to SQL Server. In fact, when you use AFTER triggers, you can think of the Inserted and Deleted tables as views of the transaction log.

When INSTEAD OF triggers fire, SQL Server hasn't yet made any changes and, consequently, hasn't logged any changes. SQL Server builds worktables to hold the inserted and deleted records as if the modification had occurred. Then, if the modification takes place, SQL Server must make the changes and log them, adding to the work of creating the worktables. Besides the extra work, another reason I see INSTEAD OF triggers as the less desirable type has to do with updates, such as those in my price-changing example. If many of the table's columns could be included in the SET clause in the UPDATE statement, the trigger would be cumbersome to write, and I would have to check each column to see whether it had a different value in the Deleted or Inserted tables.

What's Different?

Besides using the words INSTEAD OF in place of FOR or AFTER, INSTEAD OF triggers behave differently from AFTER triggers. For example, you can have only one INSTEAD OF trigger on each table for each action (INSERT, UPDATE, and DELETE), and you can't set a firing order for INSTEAD OF triggers. (In SQL Server 2000, you can specify which AFTER trigger should execute first and which should execute last.)

Also, you can't combine INSTEAD OF triggers and foreign keys with CASCADE on a table. For example, if the Sales table has a foreign-key constraint that references the Titles table and specifies CASCADE as the response to DELETE operations on Titles, you'll get an error message if you try to create an INSTEAD OF trigger for DELETE on Sales. However, you can have INSTEAD OF triggers for INSERT or UPDATE. Similarly, if you already have an INSTEAD OF trigger on Sales, you can't alter the table to add a foreign-key constraint with the CASCADE action for the same data-modification operation.

Another difference is that INSTEAD OF triggers can never be recursive, regardless of the setting of the recursive triggers database option. For example, if you execute an INSTEAD OF trigger for INSERT into Titles, and the trigger performs an INSERT into Titles, the second INSERT won't invoke the INSTEAD OF trigger. Instead, SQL Server will process the INSERT as if no INSTEAD OF trigger existed for INSERT, and any constraints and AFTER triggers will take effect.

What's the Point?

Although you might think INSTEAD OF triggers and AFTER triggers could be interchangeable in some situations, the purpose and the real power of INSTEAD OF triggers is to let you update a certain class of views that aren't usually updateable. In fact, you can't create an AFTER trigger on a view, but you can create an INSTEAD OF trigger. For example, you usually can't execute DELETE operations on a view that is based on a join. However, you can write an INSTEAD OF DELETE trigger. The trigger has access (through the Deleted table) to the rows of the view that would have been deleted had the view been a real table. Similarly, in an INSTEAD OF UPDATE or INSTEAD OF INSERT trigger, you can access the new rows through the Inserted table. In the next issue, I will describe the restrictions on updating views and demonstrate how an INSTEAD OF trigger can be a good way around some of these restrictions.