SQL Remote User's Guide
Principles of SQL Remote Design
How statements are replicated
UPDATE statements are not replicated exactly as the client application enters them. This section describes two ways in which the replicated UPDATE statement may differ from the entered UPDATE statement.
If an UPDATE statement has the effect of removing a row from a given remote user's subscription, it is sent to that user as a DELETE statement. If an UPDATE statement has the effect of adding a row to a given remote user's subscription, it is sent to that user as an INSERT statement.
The figure illustrates a publication, where each subscriber subscribes by their name:
An UPDATE that changes the Rep value of a row from Marc to Ann is replicated to Marc as a DELETE statement, and to Ann as an INSERT statement.
This reassignment of rows among subscribers is sometimes called territory realignment, because it is a common feature of sales force automation applications, where customers are periodically reassigned among representatives.
An UPDATE statement changes the value of one or more rows from some existing value to a new value. The rows altered depend on the WHERE clause of the UPDATE statement.
When SQL Remote replicates an UPDATE statement, it does so as a set of single-row updates. These single-row statements can fail for one of the following reasons:
The row to be updated does not exist Each row is identified by its primary key values, and if a primary key has been altered by some other user, the row to be updated is not found.
In this case, the UPDATE does not update anything.
The row to be updated differs in one or more of its columns If one of the values expected to be present has been changed by some other user, an update conflict occurs.
At remote databases, the update takes place regardless of the values in the row.
At the consolidated database, SQL Remote allows conflict resolution operations to take place. Conflict resolution operations are held in a trigger or stored procedure, and run automatically when a conflict is detected.
In Adaptive Server Anywhere, the conflict resolution trigger runs before the update, and the update proceeds when the trigger is finished. In Adaptive Server Enterprise, the conflict resolution procedure runs after the update has been applied.
A table without a primary key or uniqueness constraint refers to all columns in the WHERE clause of replicated updates When two users update the same row, replicated updates will not update anything and databases will become inconsistent. All replicated tables should have a primary key or uniqueness constraint and the columns in the constraint should never be updated.