This section describes how to use RESOLVE UPDATE, or conflict resolution triggers.

UPDATE statements with a VERIFY clause

Conflict resolution triggers are fired by the failure of values in the VERIFY clause of an UPDATE statement to match the values in the database before the update. An UPDATE statement with a VERIFY clause takes the following form:

UPDATE table-list 
SET column-name = expression, ... 
[ VERIFY (column-name, ...)
   VALUES ( expression, ...) ] 
[ WHERE search-condition ]

The VERIFY clause can be used only if table-list consists of a single table. It compares the values of specified columns to a set of expected values, which are the values that were present in the publisher database when the UPDATE statement was applied there. When the VERIFY clause is specified, only one table can be updated at a time.

The VERIFY clause is useful only for single-row updates. However, multi-row update statements entered at a database are replicated as a set of single-row updates by the Message Agent, so this imposes no constraints on client applications.

Conflict resolution trigger syntax

The syntax for a RESOLVE UPDATE trigger is as follows:

CREATE TRIGGER trigger-name
RESOLVE UPDATE
OF column-name ON table-name 
[ REFERENCING [ OLD AS old-val ]
   [ NEW AS new-val ]
     [ REMOTE AS remote-val ] ] 
FOR EACH ROW 
BEGIN
   ... 
END

RESOLVE UPDATE triggers fire before each row is updated. The REFERENCING clause allows access to the values in the row of the table to be updated (OLD), to the values the row is to be updated to (NEW) and to the rows that should be present according to the VERIFY clause (REMOTE). Only columns present in the VERIFY clause can be referenced in the REMOTE AS clause; other columns produce a "column not found" error.

Using the VERIFY_ALL_COLUMNS option

The database option verify_all_columns is Off by default. If it is set to On, all columns are verified on replicated updates, and a RESOLVE UPDATE trigger is fired whenever any column is different. If it is set to Off, only those columns that are updated are checked.

Setting this option to On makes messages bigger, because more information is sent for each UPDATE.

If this option is set at the consolidated database before remote databases are extracted, it will be set at the remote databases also.

You can set the verify_all_columns option either for the PUBLIC group or just for the user contained in the Message Agent connection string. See verify_all_columns option [SQL Remote].

Using the CURRENT REMOTE USER special constant

The CURRENT REMOTE USER special constant holds the user ID of the remote user sending the message. This can be used in RESOLVE UPDATE triggers that place reports of conflicts into a table, to identify the user producing a conflict.