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. Still, this also happens to be one of those topics where you can have become a relatively advanced user of SQL Server, and never hit this particular topic. That is, triggers can be needed by the beginner for some installations, and yet never been touched by the "Pro" in others (SQL is just that way...). The result is that, if you've read my Beginning SQL Server 2008 Programming title, then you'll definitely notice some overlap (but you'll find much more depth here). If you're in that group of people, feel free to skip ahead to the
INSTEAD OF triggers section.
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 ...