SQL Server transactional replication issues regarding NOT FOR REPLICATION triggers. Part II

Scenario: Transactional replication with immediate updates among SQL Server 2005 publisher and subscribers. Articles/Tables have user defined triggers marked as NOT FOR REPLICATION (NFR) and those triggers are deployed to subscribers for load balancing.

Problem description: Even though the triggers are marked as NOT FOR REPLICATION (NFR), when the origin of the triggering action is a subscriber, the trigger is fired twice, at both the subscriber (expected behaviour) and at the publisher (changed behaviour from SQL Server 2000 point of view).

The solution: In this page the solution to the problem exposed in SQL Server transactional replication issues regarding NOT FOR REPLICATION triggers is explained: The main idea is using SET CONTEX_INFO logic so that triggers thrown at the server are capable of distinguishing when the call has been made directly by a user (either at the publisher or the subscriber) or has been made in order to replicate a command.

In the former URL we saw an example of a trigger that should not be executed at all when fired at the publisher by means of an insert/update done at the subscriber. The procedure explained here is to avoid the execution of those unwanted double-fired triggers.

You must ensure that your application executes:

SET CONTEXT_INFO 0x80

Everytime it makes a connection to the server. We will use 0x80 (that in binary is 10000000 00000000). In fact, we will only use the first bit from the first byte (8 bits) from CONTEXT_INFO, that is sized 128 bytes, just in case you might need the rest of the variable for other purposes.

If you already use CONTEXT_INFO in your scenario, select another bit position for this purpose and change the following code accordingly.

And now, the other side of the change: You have to include the following code at the very beginning in every NOT FOR REPLICATION trigger deployed to subscribers that might give you problems when fired more than once:

  -- In order to workaround the double triggering issue in both subscribers 
  -- and publisher when the action is executed on the subscriber, we need 
  -- to check here if the trigger has been fired by a user action.
  -- For this reason, client applications MUST execute SET CONTEXT_INFO 0x80
  -- in order to identify themselves. Only first bit of first byte of the 
  -- 128bytes of CONTEXT_INFO is used here. The rest of CONTEXT_INFO could 
  -- still be used for other purposes. We only check that first bit of first
  -- byte is set to 1.
  IF NOT EXISTS(SELECT * FROM master.dbo.sysprocesses
                WHERE spid = @@SPID AND 
                      CONVERT(tinyint, SUBSTRING(context_info, 1, 1)) & 0x80 = 0x80)
       RETURN -- If no context info exists, return
  -- If we reach to this point, the trigger has identified correctly, the user
  -- action and the execution will continue either on the publisher or the 
  -- subscriber, but not on both.

  -- The rest of the trigger follows...