Disabled or enabled trigger from another trigger

Question

I have two tables: dbo.old_roles and dbo.new_roles. I need to sync between the two.

When created or modified role in one table it should update the other one. To do it I created a trigger in each of the tables.

To prevent a loop, I want to disable the trigger in one table when a trigger is fired from the other table.

When I call to ALTER TRIGGER statement., I get an error.

What can I do?

Answer 1

You have three options:

1. Disable "nested triggers" using sp_configure. This is a server/instance -level option, so it might disable functionality in other areas where you might have triggers calling other triggers. But it should at least be mentioned. You can see the current setting using the following:

   EXEC sp_configure 'nested triggers';
   

   And you can disable this ability using the following:

   EXEC sp_configure 'nested triggers', 0;
   RECONFIGURE;
   

   You can read more about this setting here: Configure the nested triggers Server Configuration Option.

2. Have each trigger detect how many levels deep they are using TRIGGER_NESTLEVEL function. Use this at the beginning of each trigger to simply exit if it is not the 1st or 2nd trigger execution in the stack. Something along the lines of:

   IF (TRIGGER_NESTLEVEL() > 2)
   BEGIN
     -- Uncomment the following PRINT line for debugging
     -- PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
     RETURN;
   END;
   

   This will require a little bit of testing to see how it is affected by the initial insert being done by yet another trigger (in case that ever becomes an issue, but it might not). If it doesn't work as expected when called by another trigger, then try setting some of the parameters to this function. Please see the documentation (linked above) for details.

3. Set a flag in the session-based "context info" using SET CONTEXT_INFO. Context info is a VARBINARY(128) value that exists at the session level and retains its value until overwritten or until the session ends. The value can be retrieved either by using the CONTEXT_INFO function or selecting the context_info column from either of the following DMVs: sys.dm_exec_requests and sys.dm_exec_sessions.

   You could place the following at the beginning of each of those triggers:

   IF (CONTEXT_INFO() = 0x01)
   BEGIN
     -- Uncomment the following PRINT line for debugging
     --PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
     RETURN;
   END;
   ELSE
   BEGIN
     -- Uncomment the following PRINT line for debugging
     --PRINT 'Initial call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
     SET CONTEXT_INFO 0x01;
   END;
   

   This option doesn't work so well if you are already using Context Info for some other reason. But, anyone using SQL Server 2016 can make use of SESSION_CONTEXT, which is a new session-based set of key-value pairs.