SQL Server Triggers Firing Each other Question


SQL Server Triggers Firing Each other 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?




How to fetch unmatching records from two SQL tables?

1:



How to use the LIKE keyword in SQL?
Look into TRIGGER_NESTLEVEL function.


A DB2 Trigger to calculate an average in another table
It returns the current level of trigger nesting.


LIKE in dynamic queries
You can check that to prevent the duplicates..
SQL: Optimization problem, has rows?


How do I use DB2 Explain?


Insert Into SQL Statement

2:



sub query with parameter?
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..


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.


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'..



85 out of 100 based on 75 user ratings 1225 reviews

@