It is late evening. Something is wrong with a database. You narrow down the possibilities, getting more frustrated and puzzled. Stay calm. Check the inputs systematically. No! The data going into that table is right, but when you then read it in the table, it's wrong. Why did I stop believing in the supernatural?
Then it hits you. Every time it comes as a surprise. They're using triggers.
I've always been big fan of stored procedures, and of implementing data-centric business logic in the database, where it need only be implemented once, and cannot be circumvented. I am much less convinced, however, of the value of triggers.
Show me a constraint, or a rule, and I'm all smiles. The only time you know they're working for you is when you need to know. Triggers, on the other hand, may have outlasted their usefulness. They were devised to ensure referential integrity in the bad old days before constraints. There is little need for them now unless you are unfortunate enough to allow direct access to the base tables.
Triggers are not only difficult to test and debug, but they are also masters of the art of concealment. They are not called explicitly, they just "happen" as a result of other code executing. It is very easy to fail to spot them in the SSMS Object Browser, and the old Enterprise Manager seemed to deliberately conceal them. They are easily hidden away in the DDL and forgotten about. This leads to "unexpected behaviour" and a maintenance headache. Triggers also have a reputation for bad performance with some DBAs, possibly due to their "encouragement" of line-by-line rather than set-based processing.
I don't like to see triggers being used to compensate for a lack of basic integrity control in the database, via appropriate use of keys, check constraints and so on. I think Joe Celko was spot on in his recent Constraint Yourself article, when he suggested that not using all the CHECK constraints you can is a big mistake. They are there not just for data integrity but are also used by the optimizer and can result in better query plans and performance.
Last year at PASS I heard one person proudly proclaim that we "like our databases dumb!" He, like many others, wanted to avoid putting any logic in the database in favour of placing it all in a middle-tier layer. This, they argue, leads to a more scalable and portable architecture.
Would you agree with the wild men of the 'Dumb Database' persuasion? Would you still use triggers to implement business logic when building new applications? Or is Conor Cunningham right that most designs that implement triggers are "legacy" ones.
Finally, speaking of PASS, SQLServerCentral will be at the event again this year. I look forward to meeting some of you at the SSC party! If anyone is interested in saying hello, I'll see you there, or you can drop me a mail at "tdavis at sqlservercentral.com" to arrange a different time.
Cheers,
Tony Davis.
