THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Triggers...Evil?

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.

Published Sunday, July 13, 2008 7:16 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Triggers fall into the same category as dynamic SQL, temp tables, and TOP:

- Overused? Yes.

- Misunderstood by many developers and DBAs alike? Yes.

- Useful in the right context? YES!

- Evil? Hardly...

July 13, 2008 9:25 PM
 

Alexander Kuznetsov said:

Hi Louis,

I respectfully disagree with some of your advices. Cross database "referential integrity" has loopholes:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/13/be-aware-of-these-loopholes-in-your-referential-integrity.aspx

Denormalization and Inter-table constraints can be maintained via RI, which in my experience typically performs better and is completely reliable - if you use RI to maintain your denormalized data, it is guaranteed to be always up to date. Example here:

http://www.devx.com/dbzone/Article/34479

What do you think?

July 13, 2008 10:13 PM
 

James Luetkehoelter said:

Hi Alex,

I think Louis was suggesting areas where it would be acceptable and perhaps unavoidable. I tend to agree with you on cross-database RI - I'd prefer to control that at an application layer. Usually if cross db RI is required, it's a shaky data layer architecture to begin with...

Some sorts of demoralization lend themselves to triggers - the example I usually talk about is maintaining a running inventory. That's a good article on Devx though, like it!

July 14, 2008 6:41 PM
 

Fabiano Neves Amorim said:

Hi Louis,

Great post, i Agree with you.

I talk with Conor about that and he ask me some samples, than I wrote this post.

http://fabianosqlserver.spaces.live.com/blog/cns!52EFF7477E74CAA6!486.entry

July 15, 2008 7:40 AM
 

Steve Dassin said:

James Luetkehoelter said:

>Some sorts of demoralization lend themselves to triggers...

I would say this hits the nail on the head. I could understand a developer getting a case of depression triggered by Conors article. Triggers were 'implemented' to work efficiently on tables (sets) not on rows. The principle that's operating here is that how something

was implemented to be most effective is the basis for what's best in application development. Are you kidding me, has everyone gone nuts?:) Because triggers don't consider a row as a primary concept 'functional' programmers, application developers, must 'unlearn' their database contrarian views. This is Celkos 'them' vs. 'us' nonsense. Never mind that the real subject is application development and possibly a theory that would best serve it, the basis for key concepts is what a bunch of programmers did for an MS product manager. Talk about the tail wagging the dog:) Not only is the absence of a 'row' type or at least concept antithetical to a relational dbms but it's central to application development. And people wonder how mismatches come about. Perhaps to even the score MS decided developers should learn entities and unlearn the database entirely, LINQ:)

July 16, 2008 12:02 AM
 

Steve Dassin said:

(My comments seem to have hard time getting thru on this site:(:)

James Luetkehoelter said:

>Some sorts of demoralization lend themselves to triggers...

I would say this hits the nail on the head. I could understand a developer getting a case of depression triggered by Conors article. Triggers were 'implemented' to work efficiently on tables (sets) not on rows. The principle that's operating here is that how something

was implemented to be most effective is the basis for what's best in application development. Are you kidding me, has everyone gone nuts?:) Because triggers don't consider a row as a primary concept 'functional' programmers, application developers, must 'unlearn' their database contrarian views. This is Celkos 'them' vs. 'us' nonsense. Never mind that the real subject is application development and possibly a theory that would best serve it, the basis for key concepts is what a bunch of programmers did for a MS product manager.

Talk about the tail wagging the dog:) Not only is the absence of a 'row' type or at least concept antithetical to a relational dbms but it's central to application development. Perhaps to even the score MS decided developers should learn entities and unlearn sql entirely, LINQ:)

July 16, 2008 9:48 PM
 

Steve Dassin said:

Christ Louis, did you really want to grow up to be an MS censor? How could I have so overestimated your maturity? Be careful your 'you' doesn't get lost amongst your 'we'.

July 17, 2008 9:42 PM
 

Todd Barkus said:

I totaly vehemently disagree. I do not think you could become fat eating just celery.

:-)

July 21, 2008 9:14 AM
 

Tom Garth said:

At least one of your assumptions is based on faulty facts.

More calories are burned while eating celery than are consumed. You won't be putting on any weight by eating celery.

July 21, 2008 12:38 PM
 

drsql said:

Sorry for the delay.  Got caught back up in writing..

In the case of Cross-database referential integrity (RI), yes it isn't perfect, but only due to management issues. If you code proper RI triggers they will protect your data from bad data from being created. (Of course, cross-database anything generally stinks). I don't like to let the app layer have complete control, as having a trigger prevents some untested path from corrupting the data. Application layers have more loopholes than any trigger based setup (remember, until 6.5 we did all validation in triggers, and we liked it (not))

"In most cases the easiest and simplest way to enforce a parent-child relationship is to use a built-in tool – do not reinvent the wheel, have both tables in one database and use a foreign key."

For, Intra-table, inter-row constraints, and inter-table constraints, I agree that a constraint will work. But triggers work too and can deal with multiple row operations faster than constraints. In either case, using a function with a constraint can do most of the same things.  If they would finally give us the ability to put a real SQL statement in a constraint, then triggers would really only be good for...(Interesting example in your article, Alex)

Introducing desired side effects to your queries.  I don't mean simple stuff like cascading an insert to a foreign key, or pushing a row into a queue for an asynchronous process to use.  

Like storing a log row when a user deletes a row (and you can't use Audit because you don't have Enterprise Edition). I rarely use triggers, and when I do they are lean and do only the minimum that should be done.  

July 30, 2008 10:36 PM
 

drsql said:

Todd Barkus said:

I totaly vehemently disagree. I do not think you could become fat eating just celery.

Hmm.. I wonder if that is really true?  Time for the Mythbusters?

July 30, 2008 10:44 PM
 

Steve Dassin said:

Lets strike a bargin, I will admit to being a fool if you will admit to being a stand-up guy. As if eating half a worm isn't bad enough, I must bear the burden of squandering a perfectly good insult :) Those with an ounce of maturity should appreciate what you did. Perhaps I'll look at it as flushing out a positive example of what real kahonies is all about. Sir, take a bow as I try to sleep off a case of small mindedness. And I do look forward to seeing your hard work but I wonder if it's I who should be sending you something. You have my sincerest apology.

August 1, 2008 2:13 AM
 

drsql said:

Nah, I know how you feel. Other people's comments get through, yours didn't and you said stuff that was "strong" in nature :)  I would have felt slighted too.  

August 1, 2008 10:12 AM
 

Preethi said:

If a system is desinged properly from begining and implemented the way it was planned, triggers will not have a place.  Triggers are used as a short cut for the missing functionality.

Basically, trigger is evil by the nature of it. But we need to use it if(as)we don't have a perfect system.

August 4, 2008 1:59 AM
 

drsql said:

I don't know, in reality, I agreed with Glenn that they were usually evil in the way they were used, but based on the way constraints work, there are a few things that cannot be done without a trigger, or certainly more efficient.  

Quick example.  Intra-row constraints.  If you want to say that the row you insert will not make the total amount > some value.  You could use a constraint and a function, but the function would run once per row. For "reasonable" numbers of rows, a trigger would be considerably faster as it would only run once.  For larger number of rows that end up causing massive scans, the function might be better as it would run once per row, but every operation might use a key...

Either way it is 100% true that triggers should be on the bottom of the list of things to try, not the top.  And you certainly should consider if the usage is proper before building them.

August 4, 2008 3:12 PM
 

Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:37 AM
 

Louis Davidson said:

A setting that I noticed a while back when looking at sys.configurations was disallow results from triggers.

June 6, 2009 10:38 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement