Interesting scenario in replication | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Interesting scenario in replication

Hello DBA’s,
I have a very strange scenario happening. We have transactional replication implemented in our environment and there are some tables involved in replication. The subscribing table has a trigger on those tables while the tables on the publisher side donot have triggers. Now comes the problem, the trigger is an update trigger and now the question is will the trigger fire if the update operation is being performed at the publishing side , will the trigger on subscribing side fire? The answer I thought first was obviously yes because it is the same transactions that are being replicated, but that is not happening, the trigger is not firing when the update operation is performed at the publishing side. Can anyone give me a clear idea of how the replication concept works here. Thanks
Satya
Are those trigger created with NOT FOR REPLICATION option? If it is using NOR FOR REPLICATION option which means trigger should not fire when replication process modifies the table…
Mohammed U.
True, if the trigger is defined with NOT FOR REPLICATION option then the behaviour shown in SQL 2000: if a trigger was marked as NOT FOR REPLICATION and copied along as part of the article, it will not fired when the triggering action was a result of a replication (either publisher to subscriber or subscriber to publisher). From one of the technet article:
Transactional replication with updatable subscriptions and queued updating system as a failover in case of connectivity issues. As shown inhttp://msdn2.microsoft.com/en-US/library/ms151718.aspx, when talking about Switching Between Update Modes, an application can call to sp_setreplfailovermode in case it detects a network problem so that users can still make updates on subscribers and their changes be queued (for later replication to publisher when network problems are solved). Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Here is a thought. If the article’s table has a unique constraint and you update that column, the update will replicate as a delete/insert pair of operations rather than an update. If your subscriber’s trigger is based on UPDATE actions it will not run on the delete/insert pair of operations. Take a look at this article:
http://support.microsoft.com/kb/238254
]]>