Professional SQL Server™ 2005 Programming

Chapter 13. Triggers

Hi, ho Trigger! And away!

Okay, so it's a little cliché even for me. What's more, it glorifies what is, although really wonderful, also really dastardly. I am often asked, "Should I use triggers?" The answer is, as with most things in SQL, "It depends." There's little that's black and white in the wonderful world of SQL Server — triggers are definitely a very plain shade of gray.

Know what you're doing before you go the triggers route — it's important for the health and performance of your database. The good news is that's what we're here to learn.

As with most of the core subjects we've covered in this book (save for a few that were just too important to rush), we're going to be moving along quickly in the assumption that you already know the basics. In this chapter, we'll try to look at triggers in all of their colors — from black all the way to white and a whole lot in between. The main issues we'll be dealing with include:

• What is a trigger (the very quick and dirty version)?

• Using triggers for more flexible referential integrity

• Using triggers to create flexible data integrity rules

• Using INSTEAD OF triggers to create more flexible updateable views

• Other common uses for triggers

• Controlling the firing order of triggers

• Performance considerations

By the time we're done, you should have an idea of just how complex the decision about when and where not to use triggers is. You'll also have an inkling of just how powerful and flexible they can be.

Most of all, if ...