Issues with Triggers for SQL Server Tables using Merge Replication

By: Ranga Babu | Comments | Related: > Replication

Problem

We have SQL Server merge replication created for a database. Most of tables involved in the merge replication have triggers that are configured for not for replication, but on one table we have a trigger that needs to fire for replication. This trigger seems to fire randomly for some inserts and updates on the subscriber. Why doesn't this trigger always work correctly?

Solution

We have a SQL Server publisher on one server and subscriber on a remote SQL Server. The subscriber was configured as a pull subscription and scheduled to run every 10 minutes. We found that a trigger was not firing for some of the inserts and updates coming from the subscriber. To troubleshoot, we ran Profiler and inserted and updated a few records on the subscriber. We found the merge replication procedures which do the inserts and updates on the publisher when the merge agent runs and analyzed the behavior of the trigger. The trigger was created to handle a single insert or update within the transaction, but the merge agent is grouping all the transactions within a 10-minute replication cycle and applying them in batch. Let's walk through a sample below.

SQL Server Not For Replication Overview

When we create a trigger with not for replication, the trigger is fired only when there are user inserts, updates or deletes and is not supposed to fire when the replication merge agent applies inserts, updates, deletes. Triggers that are not marked for "NOT FOR REPLICATION" will fire when the replication merge agent applies changes.

Here are some other options for using Not For Replication:

"NOT FOR REPLICATION" on foreign keys – Foreign key constraints are checked only when user inserts, updates and deletes data on a table. This validation is not checked when the merge agent is applying data changes on subscribers.
"NOT FOR REPLICATION" on identity columns – When a user inserts data into a table which has an identity column and marked for "NOT FOR REPLICATION", when a new record is inserted a new sequence is generated for that insert. When the merge agent applies the inserts on subscribers or publisher it uses the value which was already generated on the other end and inserts the same sequence value.

Merge Replication Configuration

To illustrate, I created a sample table to use.

CREATE TABLE SampleData (id int, name varchar (50))

I created the merge replication and added the SampleData table. When we generate the snapshot, we can see an entry for the table in the sysmergearticles table on the publisher. The two columns insert_proc and update_proc in sysmergearticles gives us the insert and update procedures which are used by the merge agent to apply inserts and updates on the subscribers and publisher.

There are also batch insert/update procedures for the same article as shown below. You can see _batch is appended to the procedure name. These batch procedures are called by the merge agent when applying inserts/updates that occurred on the subscribers and publisher with in a 10-minute replication cycle.

Create History Table and Trigger

Now login to the publisher and create a history table and sample trigger on the replicated table SampleData. Note: This is just a sample trigger to just test the scenario, actual business logic would be more complex.

CREATE TABLE SampleHistory (id int, updatedtime datetime)

CREATE TRIGGER TST_SAMPLE ON SampleData
FOR INSERT, UPDATE
AS
BEGIN
   DECLARE @id int

   SELECT @id = id FROM inserted
   INSERT INTO SampleHistory VALUES (@id, GETDATE())
END

So basically, the trigger inserts data into a history table when there is an insert or update on the main table. The trigger was not created with "NOT FOR REPLICATION". The trigger was created to handle only one insert or update at a time as the developer was confident that the application will only insert or update a single record within a transaction.

Test Replication Setup and Trigger

Now login to the subscriber and in the replicated database insert a few records.

INSERT INTO SampleData (id, name) VALUES (1,'Adam') 	
GO
INSERT INTO SampleData (id, name) VALUES (2,'Anil')
GO
INSERT INTO SampleData (id, name) VALUES (3,'Robert')
GO

After 10 minutes, the merge agent runs and applies the changes to the publisher using MSmerge_ins_sp_96C3862F96C147C8B11685D938D64758_batch procedure in a single batch. The trigger is fired once and the inserted table in the trigger will have 3 entries, but only one id is assigned to variable @id and only one record is inserted into the history table.

To check this, query the tables on the publisher. We can see the result sets and we have all data which is inserted at the subscriber, but there is only one record inserted in the history table, where there should be three.

Even though they are individual insert statements from the application/user the merge agent groups them and applies them in batches. Similarly, this is done for updates and deletes.

We modified the trigger to be replication compatible. Please refer to below updated trigger code. Note: This is just a sample trigger to just test the scenario, actual business logic would be more complex.

ALTER TRIGGER [dbo].[TST_SAMPLE] ON [dbo].[SampleData] 
FOR INSERT, UPDATE
AS
BEGIN
   SELECT * into #Temptable FROM inserted

   INSERT INTO SampleHistory 
   SELECT id, GETDATE() FROM #Temptable
END

After modifying the trigger on publisher login to subscriber and insert few more records.

INSERT INTO SampleData (id, name) VALUES (4,'Chris')
GO
INSERT INTO SampleData (id, name) VALUES (5,'Steve')
GO
INSERT INTO SampleData (id, name) VALUES (6,'Jhon')
GO

Login to publisher and check the data after 10 minutes once the merge agent synchronizes and you should now see these records in the SampleData and SampleHistory tables.

Using Profiler to Find Issues

Profiler is great tool to identify issues in SQL Server. We ran Profiler when the merge agent was syncing data and found the batch insert/update procedure is being called to apply the changes on the publisher. This was very helpful in troubleshooting this problem.

Checking Triggers for Not For Replication Setting

To check if the trigger is not marked for "NOT FOR REPLICATION" or not use the below query.

SELECT name,type, create_date, modify_date, is_ms_shipped, is_not_for_replication 
FROM sys.triggers
WHERE is_ms_shipped=0 and is_not_for_replication=0

Summary

As you can see, having the right tools and knowing where to look to find the problem is key. Also, make sure your trigger code is robust enough to handle issues like this. You never know how things might get used.

Next Steps

Check if the table is part of merge replication or not.
Check if the table has any triggers which were created with "NOT FOR REPLICATION".
Review the trigger code if you find any such triggers.
Review not for replication on foreign keys and identity columns.
Check out these other Replication Tips.
Read this tip to learn how to change not for replication of all triggers.
Read this tip to learn how to change not for replication of identity columns.

About the author

Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips