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

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

Side effects: If the double-fired trigger makes any update, this update is first executed at the publisher. When the update is run at the subscriber, symptoms described in KB241511 appear: Server: Msg 20515, Level 16, State 1, Procedure sp_MSreplraiserror, Line 9 Updatable Subscribers: Rows do not match between Publisher and Subscriber. Refresh rows at Subscriber. Server: Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8 Updatable Subscribers: Rolling back transaction. This is because the change tried at the subcriber was already done by the publisher, changing msrepl_tran_version field, thus not matching the version that the subscriber has, and failing the transaction. According KB241511 - PRB: REPL: Msgs. 20515, 21054, or 20512 with Immediate-Updating Subscribers:

These messages appear if a change has been made at the publisher and that change has not appeared on the subscriber yet. These messages prevent you from making a change that would violate transactional consistency.

More information: This behaviour is new to SQL Server 2005. Database solutions including transactional replication that worked perfectly with SQL Server 2000 version, might have problems when migrating to SQL Server 2005 and they will need to make modifications in order to succeed. Acording to SQL Server 2005 Books Online: How to: Control the Behavior of Triggers and Constraints During Synchronization (Replication Transact-SQL Programming):

To prevent triggers from executing during synchronization:

• When creating a new trigger, specify the NOT FOR REPLICATION option of CREATE TRIGGER (Transact-SQL).
• For an existing trigger, specify the NOT FOR REPLICATION option of ALTER TRIGGER (Transact-SQL).

And also in Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION:

For example, if you define an insert trigger with the NOT FOR REPLICATION option set, all user inserts fire the trigger, but inserts from replication agents do not. Consider a trigger that inserts data into a tracking table: when the user inserts the row originally, it is appropriate for the trigger to fire and enter a row into the tracking table, but the trigger should not fire when that data is replicated to the Subscriber, because it would result in an unnecessary row being inserted in the tracking table.

As you can read, this comment is done from the publisher's point of view: what should happen on the subscriber when the insertion is done at the publisher? Since the trigger is marked as NFR, the trigger should not be fired there because it would not be fired by a user, but rather a replication agent. However, what happens when the insertion is done at the subscriber? Well... if you are using transactional replication with immediate updates, the behaviour is not symetric, I mean, the trigger will be fired twice: at the subscriber and also at the publisher (If there are more than one subscriber participating in the replication, when the change is replicated to the rest of them it will not be fired again, since the change is done by replication agent). This asymmetry in behaviour regarding NOT FOR REPLICATION triggers has been the reason for my headaches since we decided to migrate to SQL Server 2005.

Steps to reproduce the problem: Lets go and imagine a simple pair of master/detail tables: TQuotations and TQuotationsDetails. We will create triggers to update the master quotation record with the new sum of items' prices every time an insert/update/delete ocurrs on the details table. The tables's schema will be:

CREATE TABLE [dbo].[TQuotations](
  [Id] [int] NOT NULL,
  [Description] [varchar](50) NOT NULL,
  [Price] [money] NOT NULL CONSTRAINT [DF_TQuotations_Price]  DEFAULT ((0)),
  CONSTRAINT [PK_TQuotations] PRIMARY KEY CLUSTERED ([Id] ASC)
    WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TQuotationsDetails](
  [QuotationId] [int] NOT NULL,
  [Id] [int] NOT NULL,
  [Description] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
  [Quantity] [int] NOT NULL CONSTRAINT [DF_TQuotationsDetails_Quantity]  DEFAULT ((1)),
  [Price] [money] NOT NULL CONSTRAINT [DF_TQuotationsDetails_Price]  DEFAULT ((0)),
  CONSTRAINT [PK_TQuotationsDetails] PRIMARY KEY CLUSTERED ([QuotationId] ASC, [Id] ASC)
    WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And the triggers, defined at TQuotationsDetails will be:

CREATE TRIGGER [dbo].[trg_Check_ins_upd_TQuotationsDetails] ON [dbo].[TQuotationsDetails]
FOR INSERT, UPDATE
NOT FOR REPLICATION
AS BEGIN
 SET NOCOUNT ON

 IF UPDATE(Quantity) OR UPDATE(Price) BEGIN
  UPDATE dbo.TQuotations
  SET Price = ROUND(AUX.Total, 2)
  FROM dbo.TQuotations INNER JOIN (
   SELECT dbo.TQuotations.Id AS QuotationId,
    SUM(ROUND(dbo.TQuotationsDetails.Quantity * dbo.TQuotationsDetails.Price, 2)) AS Total
   FROM dbo.TQuotations INNER JOIN
    dbo.TQuotationsDetails ON dbo.TQuotations.Id = dbo.TQuotationsDetails.QuotationId INNER JOIN
    (SELECT DISTINCT QuotationId FROM inserted) AS i ON dbo.TQuotations.Id = i.QuotationId
   GROUP BY dbo.TQuotations.Id) AUX ON AUX.QuotationId = dbo.TQuotations.Id
  WHERE dbo.TQuotations.Price <> ROUND(AUX.Total, 2)
 END
END

CREATE TRIGGER [dbo].[trg_Check_del_TQuotationsDetails] ON [dbo].[TQuotationsDetails]
FOR DELETE
NOT FOR REPLICATION
AS BEGIN
 SET NOCOUNT ON

 UPDATE dbo.TQuotations
 SET Price = ROUND(AUX.Total, 2)
 FROM dbo.TQuotations INNER JOIN (
  SELECT dbo.TQuotations.Id AS QuotationId,
   ISNULL(SUM(ROUND(dbo.TQuotationsDetails.Quantity * dbo.TQuotationsDetails.Price, 2)), 0) 
    AS Total
  FROM dbo.TQuotations INNER JOIN
   (SELECT DISTINCT QuotationId FROM deleted) AS d ON dbo.TQuotations.Id = d.QuotationId
   LEFT OUTER JOIN dbo.TQuotationsDetails
    ON dbo.TQuotations.Id = dbo.TQuotationsDetails.QuotationId
  GROUP BY dbo.TQuotations.Id) AUX ON AUX.QuotationId = dbo.TQuotations.Id
 WHERE dbo.TQuotations.Price <> ROUND(AUX.Total, 2)
END

You can now set up a publication including these two tables. Remember to set the option to deploy triggers to subscribers also (this is disabled by default). Configure a subscriber for your new publication and wait until the publication is ready at the subscriber. We will try to do some inserts first at the publisher in order to test that the triggers behave as expected:

INSERT INTO TQuotations (Id, Description) VALUES (1, 'the test1')
SELECT * FROM TQuotations
GO
--Id Description Price msrepl_tran_version
--1 the test1 0,00 75ACD2B8-7A0F-4F15-9F8F-0FA1D8640378
--
INSERT INTO TQuotationsDetails (QuotationId, Id, Description, Quantity, Price)
  VALUES (1, 1, 'Item 1', 1, 20)
INSERT INTO TQuotationsDetails (QuotationId, Id, Description, Quantity, Price)
  VALUES (1, 2, 'Item 2', 10, 5)
SELECT * FROM TQuotations
SELECT * FROM TQuotationsDetails
GO
--Id Description Price msrepl_tran_version
--1 the test1 70,00 80D3E3CB-30D5-42BF-A02A-1F395A94836D
--
--QuotationId Id Description Quantity Price msrepl_tran_version
--1 1 Item 1 1 20,00 CC20554D-E9DE-434D-866C-1A65C6F582A3
--1 2 Item 2 10 5,00 AB690758-ACA9-4D2C-A037-BFCE633A1BEA

As you can see the triggers run fine (the price at the master quotation Id 1 is updated) and the commands are replicated to subscriber without any problem. You can try to run updates and deteles on TQuotationsDetails and check the triggers are doing their job nicely. Now the problems: Let's connect to the subscriber and try to insert a new quotation (id=2) with some details:

INSERT INTO TQuotations (Id, Description) VALUES (2, 'the test2')
SELECT * FROM TQuotations
GO
--Id Description Price msrepl_tran_version
--1 the test1 70,00 80D3E3CB-30D5-42BF-A02A-1F395A94836D
--2 the test2 0,00 7AACDD7F-4A7A-4D0C-93BA-C6C37093FDE6
--
INSERT INTO TQuotationsDetails (QuotationId, Id, Description, Quantity, Price)
  VALUES (2, 1, 'Item 1', 1, 20)
INSERT INTO TQuotationsDetails (QuotationId, Id, Description, Quantity, Price)
  VALUES (2, 2, 'Item 2', 10, 5)
SELECT * FROM TQuotations
SELECT * FROM TQuotationsDetails
GO
--Server: Msg 20515, Level 16, State 1, Procedure sp_MSreplraiserror, Line 9
--Updatable Subscribers: Rows do not match between Publisher and Subscriber.
--Refresh rows at Subscriber.
--Server: Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8
--Updatable Subscribers: Rolling back transaction.

My feedback to Microsoft on this issue: NOT FOR REPLICATION clause in triggers that are replicated to subscribers are fired twice in both publisher and subscriber This issue was resolved as 'By Design'. Please have a look at it. You can read Microsoft's point of view on this problem. This is an 'open' issue for me. I have not found any solution to this problem yet. As a workaround (suggested by Microsoft) I am forced to use transactional replication with queued updates which has its own drawbacks also (I have enough on this for another article).

Keywords: SQL Server 2005, transactional replication with immediate updates, Microsoft, NOT FOR REPLICATION, NFR, trigger, subscriber, fired twice, side-effects, KB241511, Rows do not match between Publisher and Subscriber, Updatable Subscribers: Rolling back transaction, bug, error, workaround, resolution

2006-10-02 Update: See the workaround in SQL Server transactional replication issues regarding NOT FOR REPLICATION triggers. Part II