SQL Server DML Triggers Are Evil

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.

This entry was posted in SQL Server 2008. Bookmark the permalink.

One Response to SQL Server DML Triggers Are Evil

  1. Pingback: Enforcing Complex Constraints with Indexed Views « spaghettidba

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s