SQL Server Triggers Firing Each other Question

Question

I have an auditing trigger that automatically places the time something was updated and the user that updated in fields in all my tables. I have another set of triggers that write event information from updates to an events table. The issue is when someone updates something, the event information is fired twice because of the first triggers. How to I suppress the duplicate entries?

Answer 1

I guess you have 2 choices:

1. Either combine both sets of triggers into one.

2. Or, there is a per database setting that allows recursive firing of triggers to be disabled.

Answer 2

Look into TRIGGER_NESTLEVEL function. It returns the current level of trigger nesting. You can check that to prevent the duplicates.

Answer 3

You can also turn off nested triggers all together, if you have enough permissions (some webhosts don't allow you to do this). There is a stored procedure (Sql 2005) called sp_configure that lets you modify the server's configuration. The statement below disables nested triggers, which will stop a trigger from firing another trigger.

exec sp_configure 'nested triggers', 0

Answer 4

Disabling trigger-fires-trigger behavior can also be done in SS2005 by going into SS Mgt Studio and selecting the icon of the server in question, right-clicking, then select "Properties". Then select "Advanced" from the list on the left and set the value for "Allow Triggers to Fire Others" to 'False'.