Say it isn’t so. “It isn’t so.”  Glenn Berry thinks so in his post here. When I read his post I thought I was going to really get into it with Conor’s post here, called the Trouble with Triggers (a title which I had to post because I like Star Trek too,) but frankly I agree with Conor.  Triggers are great tools, when applied correctly.  When they are used in a bad way (which is quite often, from the code I have seen), well, agree with Glenn I must…

In my book, I advocate triggers for a few things:

* Cross-database referential integrity (RI): Just basic RI, but SQL Server doesn’t manage declarative constraints across database boundaries.

* Intra-table, inter-row constraints: For example, when you need to see that the sum of a column value over multiple rows is less than some value (possibly in another table).

* Inter-table constraints: For example, if a value in one table relies on the value in another. This might also be written as a functions-based CHECK constraint, but it is often more maintainable to use a trigger.

* Introducing desired side effects to your queries: For example, cascading inserts, maintaining denormalized data, and so on.

(and I give examples of each.) but I also am careful to say to be extremely careful with them. When my tech reviewer got to the start of my section on triggers, he quickly said that he didn’t like them and they shouldn’t be used.  By the end he agreed with my examples.  The problem with triggers is just like the problem with several tools that SQL Server gives you.  For example:

* Stored procedures: If used to encapsulate set based SQL calls into precompiled batches…good.  Used as a functional language to do work row by row, often with cursors…not so good.  Used to implement lots of business rules…well, that depends on the business rules but often this is where the real trouble comes (that is a future post)

* Cursors: Used to do some repetitive task, usually for some maintenance use…good.  Used in place of set based operations because the programmer cannot write good SQL…baaaddd (said properly should sound like a bleating sheep.)

* Clustered indexes: Used to cluster on the right sort of key (like a small monotonically increasing value)…good.  Used to cluster on a random value, like a guid…not so good.  Never used as a search argument of any kind, forcing bookmark lookups constantly….well, yuck.

So just like you don’t hammer in a nail with a wrench, or hammer in a screw with any implement, triggers are not to be used improperly.  The problem is that of education.  There are so many people out there who just do what it is they feel like without regards for what is actually correct.  SQL programmers try to do everything in SQL (I have been guilty of this many times) and functional programmers try to do everything one operation at a time.  Some day, once we all trust the SQLCLR to the extent we ought to, perhaps the exact balance can be achieved.

And Glenn, I completely agree with your reasons to hate triggers: “I witnessed first hand the consequences of over-use of DML triggers in a large, n-tier client/server application.”  I think the important thing in there is the word “over-use.”  I mean, a person could probably get fat eating only celery and drinking water if they over did that.