In the old days of T-SQL, back when we wrote "CREATE TRIGGER …. FOR INSERT" we could only have one insert/update/delete trigger for each table. Eventually SQL Server allowed us to have multiple triggers, and even have some control over in what order the triggers fired.
Triggers are often hidden objects that confound DBAs who aren't aware they exist. It's not easy to tell when a table has a trigger on it, and since we don't often use triggers, it's not the first place people look when something strange happens.
However triggers are useful, and it seems that there are many people using them. For this Friday, I wanted to ask how people implement triggers in their applications.
Do you prefer one trigger for each table action or multiple triggers?
I'm curious what's the 80 in your 0/20 rule for triggers. Should all update actions be handled in one trigger? Or should there be one trigger for business logic and a separate one for auditing? I'm not sure it matters a lot for performance, but I can see that it might be easier to manage and track fewer triggers. The flip side is that something like auditing can be handled with one trigger, and business logic with another: a clean separation.
Triggers aren't usually my first solution to a problem, but I do think there is value in using them. However I don't see a lot of guidance about how to best implement them, so I'm hoping your answers today will help.
Steve Jones
The Voice of the DBA Podcasts
- Windows Media Podcast - 17.9MB WMV
- iPod Video Podcast - 13.2MB MP4
- MP3 Audio Podcast - 3.1MB MP3
The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.
