April 27, 2016 at 8:41 am
Hi,
Can you have multiple triggers on a table? If so, what are some of the things I need to make sure that it works properly?
Regards,
SQLisAwe5oMe.
April 27, 2016 at 8:49 am
SQLisAwE5OmE (4/27/2016)
Hi,Can you have multiple triggers on a table? If so, what are some of the things I need to make sure that it works properly?
Yes, you can. It will work. But it will be hard to maintain and it can jeapordize performance. (Well, all triggers can do that...)
You have limited control over the order in which the triggers execute. Using sp_settriggerorder, you can set one to be first and one to be last; all others fire in whatever order SQL Server sees fit.
When possible and when I need to use triggers, I try to combine all logic in a single trigger, with a few exceptions as listed below. To ensure maintainabilty, I clearly mark different code blocks for different functionality.
* Exception 1: I will create separate triggers for insert, update, and delete unless the code for each trigger is exactly identical. As soon as I find myself coding logic based on whether a trigger is fired by insert or by update, I will duplicate the entire trigger and remove the parts I don't need.
* Exception 2: If I have a need for generating triggers in a standard form (e.g. for auditing changes), I will accept those triggers being separate from the rest. I once did this for a low-budget audit tool where I wrote a script to generate audit triggers on all key tables. After changing a table's deifnition, I only had to exeecute the script and the audit trigger would be updated.
April 27, 2016 at 9:10 am
+1 to Hugo's thoughts, though I'd also note I want to be sure everyone (other sysadmins and developers) know the triggers exist. They are easy to forget about.
May 2, 2016 at 10:02 am
As always with triggers, the biggest issue is to make the sure the triggers are coded properly, i.e. they deal with sets and not a row at a time, and that they are as efficient as possible, even if that sacrifices some clarity.
In particular, under no circumstances do any table meta-data lookup at run time. Don't lookup the type, name or number of columns. The trigger code should be static.
However, that static code can be dynamically generated, so that when the table schema changes it can be regenerated, but the trigger itself should not be dynamic.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply