Making use of INSTEAD-OF triggers in SQL Server 2005

by Tim Chapman | Sep 11, 2006 7:00:00 AM

Tags: Databases, Storage, Microsoft SQL Server 2005, Tim Chapman, INSTEAD-OF INSERT, trigger, INSTEAD-OF trigger, database, Microsoft SQL Server, SQL Server Newsletter

19 comment(s)

Takeaway: INSTEAD-OF triggers are powerful objects in SQL Server 2005. Tim Chapman explores some of the useful features of INSTEAD-OF triggers, and discusses how your organization can use them to its advantage.

A trigger is a database object similar to a stored procedure that executes in response to certain actions that occur in your database environment. SQL Server 2005 is packaged with three flavors of trigger objects: AFTER, data definition language (DDL), and INSTEAD-OF.

AFTER triggers are stored procedures that occur after a data manipulation statement has occurred in the database, such as a delete statement. DDL triggers are new to SQL Server 2005, and allow you to respond to object definition level events that occur in the database engine, such as a DROP TABLE statement. INSTEAD-OF triggers are objects that will execute instead of data manipulation statements in the database engine. For example, attaching an INSTEAD-OF INSERT trigger to a table will tell the database engine to execute that trigger instead of executing the statement that would insert values into that table.

Why use an INSTEAD-OF trigger?

INSTEAD-OF triggers are very powerful objects in SQL Server. They allow the developer to divert the database engine to do something different than what the user is trying to do. An example of this would be to add an INSTEAD-OF trigger to any table in your database that rolls back transactions on tables that you do not want modified. You must be careful when using this method because the INSTEAD-OF trigger will need to be disabled before any specified modifications can occur to this table.

Perhaps a more functional reason to use an INSTEAD-OF trigger would be to add the trigger to a view. Adding an INSTEAD-OF trigger to a view essentially allows you to create updateable views. Updateable views allow you to totally abstract your database schema so you can potentially design a system in such a way that your database developers do not have to worry about the OLTP database schema and instead rely upon a standard set of views for data modifications.

An example

To better illustrate the idea of an updateable view, it's always great to use an example. In this example, I refer to a fictitious scenario that includes a Products lookup table and a Purchases table, which records those instances where products are purchased. Listing A contains the script to create these tables. After running the script to create the tables I will use in the example, I will run the script in Listing B to insert some data into the tables.

Weekly SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

Now that the sample tables have data in them, I can create a view to join these tables and present the data in a meaningful way. Check out Listing C.

This is a pretty typical production-level view. It joins two tables in the database structure, which greatly simplifies data retrieval. However, the data abstraction provided is not the only advantage of using views. Attaching INSTEAD-OF trigger(s) to this view allows me to modify the underlying tables, so that I may never need to modify the data in the underlying tables directly. I'll use the script in Listing D to create an INSTEAD-OF trigger on the vw_ProductPurchases view.

Notice that in the trigger declaration I specify the INSTEAD OF clause. Triggers created in SQL Server are AFTER triggers by default, so I must specify the INSTEAD OF clause in the trigger definition.

The first statement in the trigger is a "check" statement. Here I am checking the INSERTED table to ensure that the ProductID is present, and that either the PurchasePrice or the ProductPrice has been provided.

If the necessary data has been inserted into the view via an INSERT statement, the trigger will insert the specified values into the underlying data table. This is what a sample INSERT statement into the view would look like.

INSERT INTO vw_ProductPurchases(ProductID, PurchasePrice) VALUES(1, 700)

This INSERT statement provides a valid ProductID and PurchasePrice, which means a new record will be inserted into the Purchases table.

Conclusion

With a bit of imagination, it is easy to see the power and flexibility provided by INSTEAD-OF triggers. If your system is not extremely large, using a system of views to abstract your underlying database schema can provide a great way to shield your database programmers from modifying the data in the underlying tables directly.

While my example was a very simple one, the complexity of the triggers your enterprise may require can include security concerns, time constraints, or any possible combination of restrictions you can limit your code to in SQL Server 2005.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

19 comment(s)