Reasons to Avoid Triggers

It's no secret that developers can run afoul of scalability problems when creating solutions backed by SQL Server or other relational databases. In fact, earlier this month, I complained about developers who mistakenly assume that NoSQL is the solution to all scalability and performance problems they encounter when building applications based on relational databases. In my experience, however, one of the easiest ways developers cause themselves significant pain with scalability problems is by using triggers.

The Problems with Triggers

In theory, triggers serve as specialized code that fires when a triggering event occurs within a database. Within SQL Server, there are two primary types of triggers: Data Definition Language (DDL) triggers and Data Manipulation Language (DML) triggers. DDL triggers, as the name implies, 'fire' when data definition language events (like the creation of new code, structures, logins, etc.) are executed—and those triggers are not the subject of this article. DML triggers, the triggers that execute when data is modified or manipulated are, however, subject to a number of major concerns as follows:

When to Use Triggers

Given that triggers are complex, easy to miss when troubleshooting, and cause major problems in larger and more heavily-concurrent systems, is there a place or role for them in any system? Personally, I argue against using them pretty much anywhere or for anything. By and large, most of the logic they enforce could be moved into stored procedures or into whatever code is being used to modify data already. In fact, I'd argue that the use of triggers typically signals a systemic or architectural problem, because developers frequently start using triggers as a shortcut to enforce business rules against a table—instead of the code that modifies it—as a catch all against the existing logic and functionality that's already in place. This would be analogous in some ways to trying to write pre- and post-filters against web requests to insert or modify HTML headers and footers, instead of finding the code creating these objects elsewhere and modifying that code as needed.

Those considerations and concerns aside, there is one place where triggers can make sense: simple auditing. Auditing that needs to be done for regulatory compliance is typically too extensive and invasive for triggers and should be tackled by a (typically expensive) third party auditing solution. But, for simple scenarios where you need to keep tabs on data in particular rows as they're modified, it's possible to set up simple triggers that will capture and direct INSERTED, DELETED, and UPDATED rows into a 'TableName_Audit' table where you can track the values and metadata about who performed the operation and when it was performed without too much overhead. This isn't to say that using triggers in this fashion ends up being free of the problems listed above—instead, it's just to say that there is one area where I feel that triggers can and do have a role. If, however, you end up building triggers to be used in this fashion, just make sure to account for the fact that INSERT, UPDATE, and DELETE, as well as MERGE operations can and will modify more than a single row at a time, and realize that, as your audit tables become larger and larger, the amount of time it will take to drop rows into them may eventually cause problems if indexing against your audit tables is excessive or not properly optimized for INSERTs. Otherwise, I strongly recommend against using triggers because of the problems they invariably end up imposing.