Trigger Best Practices - Long
-
I read previously that placing multiple triggers on a table is not a "best practice" since the order in which triggers fire cannot be controlled. Therefore, there is always the potential for conflicting triggers and more maintenance overhead. (Not to mention 1 trigger firing multiple triggers on another table - nesting triggers).
My personal practice has always been to use a single trigger per table with 'FOR INSERT, UPDATE, DELETE' and a test for each of the events. All code that is executed when the event fires follows each test.
If a table has 3 existing triggers, one for each event, then instead I add my t-sql code in a logical place in each existing trigger... and identify the modification rather than creating a second UPDATE trigger, a second INSERT trigger, a second DELETE trigger - or - rather than adding a fourth trigger with a 'for insert, update, delete' clause in addition to the 3 original event triggers. In short, I try to avoid multiple triggers whenever possible for the sake of clarity, maintenance, and to avoid potential conflicts.
Background info to my post: I maintain a large-scale (canned) financial system. My policy is to never modify the vendor's database objects. When customizations are required, custom objects are created and identified as such. The one exception I've made is in the case of triggers - and that is in order to follow what I believe to be the best practice and maintain the least risk. Someone has challenged my exception and their argument is a good one. When doing a version upgrade of the financial system, this can be a problem since I modified the vendor's trigger - that change has to be tracked. However, I thought this through already and my response to that is we do a pre-update evaluation before an upgrade anyway to identify all customizations and post-upgrade enhancements. This is just one more item for consideration in a process that takes place every 3-5 years... not much of a consideration compared to adding 1-300 records a month to the triggered table.
Does anyone have another practice or opinion? Is there a generally accepted 'best practice' for coding and maintaining triggers?
Thanks for any response. Shelley V.
-
Double post much
-
I do think that "should" be almost always the right way but this is not a one size fit all position.
For management flexibility if you have separated triggers you can:
- Disable them independently (helps with certain data backfills
- They are indpendetly compiled therefore you "may" get faster execution plans for a given operation
- The smaller pieces of code are easier to change without affecting the rest of the Triggers.
Note that for this to make sense the triggers should be completely unrelated regarding target tables
I have work with systems on which at times you had 30+ Triggers on one table
Just my $0.02
* Noel -
30 triggers on the same table????? what the hell kind of business rules did you have in there??
-
I know, I know...
- There were summary tables created at many levels (indexed views may have helped but system backward compatibility was an important issue)
- There were Journal Tables
- There were Audit Tables
- There were Queues for Offline/ Job Processing
and many more...
All could have being very well changed to stored porcedures and a workflow applied to them but it required a major re-write (something that management does not like to hear
)As a consultant you "may" want the job $$$
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply