Chapter 13: Triggers

Overview

The next quest in meeting the optimum performance data processing requirements and the integrity and business rule compliance requirements discussed in the preceding chapter is to master the art of creating and managing stored procedures and triggers. I say it is an art because both objects in SQL Server 2005 require some creative juices on your part and quite a bit of that brain power.

Stored procedures and triggers are very closely related. They are programmed in T-SQL or in managed code with a language like C#, and both are objects that are attached to your databases. When it boils down to what you can do with either object, it pays to rather list what you cannot do, and then the rest is up to your imagination.

Note 

The SQL-SMO object model provides objects for creation, editing, and management of triggers and stored procedures. It is, however, more important for you to master these elements in T-SQL, using a query window in Management Studio.

While the code in a trigger can be identical in function to the code of a stored procedure, the major difference between the two is that the trigger is connected to a table or view object, while a stored procedure is exposed as a database object and has to be explicitly called, like a function, and be passed parameters. If you think of your table or view as art object, which you should be doing, then think of the trigger as an event (like OnClick) that fires when an inbound DML statement “triggers” the table or the view.

Triggers are central to ensuring integrity and business rule adherence procedurally, while stored procedures are central to providing functionality and business services and functions on a broad scale. How each object is used and created will now be covered in its respective section. Because triggers are the easier of the two constructs to grasp, and because they follow up our treatise on integrity in the preceding chapter, let’s start with them first By the way, well also discuss some of the new features of triggers in SQL Server 2005, and I’ll point these out as we progress. From here on this chapter will deal exclusively with triggers, while Chapter 14 is devoted to stored procedures.