| Author |
Topic |
|
sfortner
Yak Posting Veteran
63 Posts |
 Posted - 2011-07-22 : 11:44:45
|
| I've researched the pros and cons of triggers, but didn't see one con that I've come to accept over the years. First of all, I don't really use triggers, mostly because everything complicated I've done is in a stored proc/fcn so it's visible and part of an overall transaction when more than one insert/update/delete occurs. I had a manager years ago that professed to know something about databases and it seems like others who questioned the reliability of triggers. Are they guaranteed to run every time? What happens if they fail - does the calling process also fail? Can they fail more readily?As far as context, I had wondered about using triggers when another application (Sage Timesheet) updated time and logging that change with user ID and so forth for tracking purposes. Or is there a better way?--Steve |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-22 : 11:51:34
|
| Triggers wont run for bulk inserts unless you've fire triggers set to true. Using triggers also make maintainability poor as they're not directly visible so would require good supplimentary documentation for making life of somebody supporting the system smooth. Using lots of triggers will really add to confusion and will really make it difficult to understand beyond a point. ALso using triggers will slow down process especially if there are lots of data insertion happening especially in case of overnight jobs that populate warehouses etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-07-22 : 11:55:00
|
| When you say "for tracking purposes" do you mean putting that changed data in another palce (table) for auditing? If so then triggers work just fine for that. The one thing to make sure is that your trigger(s) are writting in such a way so that they can handle when mutiple rows are affected.I'm sure arguments could be make for or against triggers for any reason. However, I tend to only use them for auditing, enforcing temporal constraints and temporal cascades (for data warehousing). |
|
|
|
sfortner
Yak Posting Veteran
63 Posts |
Posted - 2011-07-22 : 13:25:48
|
quote:
Triggers wont run for bulk inserts
No bulk inserts are occurring. We would only be using the update and delete triggers I believe to ensure tracking/auditing traps changes and deletions once time has been entered for someone to make sure we capture cases when someone changes or deletes it to make sure we're paying our people right. I have researched the other pros and cons and understand all you're saying visakhm. Was only wondering about their reliability for, yes Lamprey, auditing purposes. quote:
make sure is that your trigger(s) are writting in such a way so that they can handle when mutiple rows are affected
Didn't know about that Lamprey, so glad I asked. However, I think only single inserts occur (which we're not wanting to track), and AFAIK the updates and deletes are based on a single row only. The custom time keeping app we've written is definitely one row at a time, but we're having to also keep Timesheet around for more advanced users (at least for now). However, I will check what Timesheet is doing with a sql trace to make sure. And probably ask our consultants to confirm this with Sage. We are only in a asking phase at this point, but I believe it's something that our Accounting dept is wanting to pursue.EDIT: Hmm, on second thought, after I re-read this post, someone could also INSERT some new time to get additional pay now too couldn't they? Doh! Okay, so maybe we need insert/update/delete triggers!--Steve |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-07-22 : 15:36:51
|
| triggers are great - nothing wrong with triggers - they are completely reliable.However they are just a tool and any tool can be misused. Triggers are the most misused sql server tool I've ever seen.I love this thread: trigger madnessBe One with the OptimizerTG |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-22 : 16:05:12
|
Regarding audit triggers:- ALWAYS write them for multi-row operations. ALWAYS. I don't care who says they will always insert only one row at a time.- I've only audited UPDATE and DELETE, never INSERT. Seems redundant to store 2 copies of the same row if it's never changed. YMMV- Watch out for "accidental" updates (someone forgets a WHERE clause). Consider a @@ROWCOUNT check and roll it back if it exceeds a threshold (say 10,000 rows)- If you do implement a threshold-rollback, but need to do a full-table update at some point, you might consider a special login or some other setting the trigger can detect and allow it to go through. You can also optionally choose to have it not audit those rows under that setting.These suggestions are based on my past experiences, all of them due to being bitten by some particular audit trigger problem. Feel free to disregard.  I can say that triggers can be abused pretty easily. One vendor, who had an otherwise excellent database structure, had mountains of trigger code. They also wrote them as one-row-at-a-time operations, which utterly destroyed performance, since their application only ever worked with one row at a time. When we tried to migrate millions of rows of data, you can imagine what happened. (turns out none of their previous clients had to migrate as much data as we did) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-07-25 : 07:05:35
|
| <<ALWAYS write them for multi-row operations. ALWAYS. I don't care who says they will always insert only one row at a time>>+1 for thisI always suggest my co-workers to do it no matter how many rows that inserted/deleted can have inside a trigger.MadhivananFailing to plan is Planning to fail |
|
|
|
sfortner
Yak Posting Veteran
63 Posts |
Posted - 2011-07-25 : 08:08:50
|
| Good point Rob on the insert trigger. I think that was probably what I was thinking when I originally wrote that we only needed update/delete fcnality. For some reason I second-guessed myself? Great suggestions everyone - this definitely helps!--Steve |
|
|
|
|
Disadvantages of Triggers