INSERT, UPDATE and DELETE custom stored procedures to apply changes at the subscribers.
INSERT statements made at the Publisher are applied to subscribers through an INSERT stored procedure call. Similarly, a DELETE statement is applied through a DELETE stored procedure call.
However, when an UPDATE statement is executed as a "deferred update", the logreader agent places a pair of DELETE/INSERT stored procedure calls in the distribution database to be applied to the Subscribers rather than an update stored procedure call. For example, suppose you have a publishing table, named TABLE1, with these three columns:
- col1 int
- col2 int
- col3 varchar(30).
When you execute this code:
UPDATE TABLE1 set col1 = 3 where col2 = 'Dallas'
If you prefer to use single logic and you want all your UPDATE commands replicated as DELETE/INSERT pairs, you can enable a trace flag as described in this Microsoft Knowledge Base article:
In the preceding example, assume that you also have a horizontal filter defined on TABLE1: where col2 = 'Dallas'. If you execute this code:
UPDATE table1 set col2 = 'New York' where col1 = 3
Now, if you execute this code:
UPDATE table1 set col2 = 'Dallas' where col1 = 3
Although an UPDATE operation was performed at the Publisher, only the appropriate commands are applied at the Subscriber.
Article ID: 238254 - Last Review: Jul 10, 2008 - Revision: 1