April 25, 2005 at 9:40 am
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.
April 25, 2005 at 11:33 am
Hello,
I too support many vendor created databases and I also have a policy to never update the vendors objects or code. Even if it's a trigger.
If we have anything 'added' to the vendors database, such as a table or view, we usually prefix our internal objects with something like 'DBA_'.
But when it comes to updating triggers or updating existing objects, we will not touch this. For one, if something goes wrong with the application, sometimes vendors will tell you that they cannot be held responsible if some portion of their application fails, and will not be able to provide support.
And even if the vendor never knows about the updates, then if there is a massive application upgrade in which the vendor decides to drop/recreate the table, then your 'custom' trigger code is lost.
Lastly, if you decide the leave the company, the next DBA who comes along may never know that custom changes were ever made..
TH
April 25, 2005 at 12:05 pm
Please, Do not Double Post. See my point view on this thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=177164
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply