Triggers: Very Good, or Very Bad?
A colleague said this morning "I try to steer clear of triggers" Part of me immediately thought: "That's stupid! Triggers are powerful and they should be used wherever they're the best tool." But then a calm inner-voice rose up and reminded me of the dangers of triggers: "With great power comes great responsibility." (more on trigger-thinking...)
Testing and maintaining a trigger-happy database is comparable to waltzing through a jungle full of trip-wires, booby-traps and landmines.
Hence, as a general rule of thumb we should try to steer clear of triggers.
Before implementing a trigger, ask yourself this much, at least:
- "Is there an alternative technique?" (i.e. a technique that doesn't require a trigger)
- "Is the alternative technique no more than twice* as complex as the trigger technique?"
[* 'twice' may not be the figure you prefer -- determine a suitable factor for your own methods.]
If the answer is "no" to either of those questions then it's Trigger Time baby!
I do love a good trigger!
One little tip: you might want to have some nice strongly-typed methods in your DAL for turning the triggers on and off at will. [Note to self: this is particularly important when working on a HR system with a trigger that sends email notifications for application-level events (such as termination of employees) to board-level executives. Forgetting to disarm such a trigger during testing might cause undue stress to your boss, their boss, their boss's boss, and in turn, to yourself.]
'Jon Galloway' on Tue, 06 Jul 2004 05:46:10 GMT, sez: I worked on an application which ran against a SQL Server 6.5 database which made heavy use of triggers. They'd actually run into the 8K limit on triggers on the Orders table, and had to change variable names to one or two characters to make necessary additions. Inserts often took 30 seconds, and no one could really explain what was going on for those 30 seconds.
So I guess I'm with your friend in trying to avoid triggers when I can. I think your rule sounds good, but the trigger difficulty should include the additional maintenance / debugging headaches you'll be passing on to those who inherit your code.
'Matthew Martin' on Tue, 06 Jul 2004 12:50:40 GMT, sez: We use them to replace the buggy updates executed by our COTS software with better code.
'secretGeek' on Tue, 06 Jul 2004 20:43:28 GMT, sez: I had to google for 'COTS', and now know: 'COTS' = 'Commercial Off the Shelf'
Thanks Matthew. I know the feeling.
Yep Jon, you've hit the nail on the head -- the factor used for evaluating trigger-suitability, should relate to how much maintenance is expected, what other sources will cause updates etc.
'Scott' on Wed, 07 Jul 2004 05:28:19 GMT, sez: We now use SPs for all our CRUD actions; no new app is allowed to access tables directly. This allows some level of logic to be performed during all CRUD actions. In the insert SP you can implement any logic that you might otherwise have implemented as a trigger.
We do use triggers to populate/update the created and modified columns in tables where they exist. No logic checks, just simple, mundane, tasks.
'secretGeek' on Wed, 07 Jul 2004 21:15:56 GMT, sez: hiya Scott, sounds good.
nice to hear from you - hope the UK is going swell!
And I agree, the less logic in your triggers/sprocs the better.
i particularly like using triggers for creating archived versions of records - because it's so much quicker to develop than any other archiving scheme, but it does make other maintenance rather hellish, and makes your db grow very quick.
some people love triggers for generating email via xp_sendmail -- i'm not a big fan of this and would rather the app controlled things like email generation -- but it's still a nice trick to have in your toolkit.
the main case for triggers is where you can't control the input mechanisms - eg COTS, or mutliple inputs, e.g. web service, web apps, legacy desktop apps and biztalk server all update the same database. triggers can provide a uniform response to the stimulus, regardless of its source. it's hard to use anything other than triggers in such a case.
we use a code generator to write all our CRUD sprocs, and it also generates a combined DAL/BLL which calls the sprocs and allows us to code against strongly typed objects. it allows us to basic maintenance apps together pretty fast -- and get on with worrying about the real issues outside of CRUD.
best of luck!
lb
|
secretGeek .:dot Nuts about dot Net:.
Just Wally
Complete secretGeek Archives
The Best Software Writing I
The Business Of Software (Eric Sink)
