Conor Cunningham (formerly of Microsoft, and now with SQLSkills) has a good post up about the performance disadvantages of DML triggers. I completely agree with him here. I am happy to say that we have zero DML triggers in any of our Production databases at NewsGator.
When I first started at NewsGator nearly two and half years ago, one of the first things I said was that “Triggers Are Bad”, which I still get teased about to this day. Back in late 2003, at a previous job, I witnessed first hand the consequences of over-use of DML triggers in a large, n-tier client/server application.
We had application code that allowed the administrator(s) for the customer to add rules to audit data changes for legal and compliance reasons. This code ended up adding DML triggers for each audit rule. Only about 10 rules were actually legally required, but the customer administrators went a little wild, adding several hundred extra rules (and triggers), which ended up causing severe performance problems for SQL Server 2000. This was quite the lesson that I will not soon forget.