Article ID: 302341 - View products that this article applies to.
This article was previously published under Q302341
If you update any column that is part of a unique or clustered index, SQL Server implements the update as a deferred update. A deferred update means that the UPDATE is sent to the Subscriber as a pair of DELETE and INSERT operations. A deferred update is described in more detail in the following Microsoft Knowledge Base article:
238254Organizations may find that sending updates to subscribers as DELETE and INSERT operations does not meet their business needs. For example, business rules might require update triggers to fire at the Subscriber. To accommodate these situations a new trace flag, 8207, is introduced in SQL Server 2000 Service Pack 1, which enables singleton updates for Transactional Replication. An update to a unique column that affects only one row (a singleton update) is replicated as an UPDATE and not as a DELETE or INSERT pair. If the update affects multiple rows, the update is still replicated as a DELETE or INSERT pair.
(https://support.microsoft.com/kb/238254/EN-US/ )INF: UPDATE Statements May be Replicated as DELETE/INSERT Pairs
You can enable trace flag 8207 on the publishing server by using either of the following methods:
Important: Typically, you use trace flag 8207 with read-only transactional replication. Do not use trace flag 8207 with updatable subscriptions if:
REFERENCESFor more information, refer to the "Trace Flags" and the "Using Startup Options" topics in SQL Server Books Online.
Article ID: 302341 - Last Review: February 22, 2007 - Revision: 3.2