Trigger to same tables

1

We had a logging trigger on a Table. After Update or Delete, it saves the record of the Table to a Logging table.

Now, a second trigger got added to this Table. It updates a column on the same Table. So the problem is now, every update to the table results in 2 records into the Logging Table.

Any ideas?

2
  1. Turn off recursive triggers for that db (except in the very unlikely event you need them for some other table in that db)
  2. Decide whether you want the update from the new trigger to be included in the logging trigger.
    A) If you do, designate the logging trigger to run last.
    B) If you don't, designate the logging trigger to run first.

USE [your_db_name];
EXEC sp_dboption 'your_db_name', 'recursive triggers', 'OFF'
EXEC sp_settriggerorder 'logging_trigger_name', 'LAST' /* or 'FIRST' */, 'UPDATE', 'DATABASE'

1 Like