Professional SQL Server™ 2005 Programming

13.7. Performance Considerations

I've seen what appear almost like holy wars happen over the pros and cons, evil and good, and light and dark of triggers. The worst of it tends to come from purists — people who love the theory, and that's all they want to deal with, or people that have figured out how flexible triggers are and want to use them for seemingly everything.

My two bits worth on this is, as I stated early in the chapter, use them when they are the right things to use. If that sounds sort of noncommittal and ambiguous — good! Programming is rarely black and white, and databases are almost never that way. I will, however, point out some facts for you to think about.

13.7.1. Triggers Are Reactive Rather Than Proactive

What I mean here is that triggers happen after the fact. By the time that your trigger fires, the entire query has run and your transaction has been logged (but not committed and only to the point of the statement that fired your trigger). This means that, if the trigger needs to roll things back, it has to undo what is potentially a ton of work that's already been done. Slow! Keep this knowledge in balance though. How big an impact this adds up to depends strongly on how big your query is.

"So what?" you say. Well, compare this to the notion of constraints, which are proactive — that is, they happen before your statement is really executed. That means that they prevent things that will eventually fail from happening before the majority of the work has been ...