SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Enterprise
    Managing conflicts

A first conflict resolution example

In this example, conflicts in the Customer table in the two-table example used in the tutorials are reported into a table for later review.

The database 

The two-table database is as follows:

Goals of the conflict resolution 

The conflict resolution will report conflicts on updates to the name column in the Customer table into a separate table named ConflictLog.

The conflict resolution objects 

The conflict resolution tables are defined as follows:

CREATE TABLE OldCustomer(
   cust_key CHAR( 12 ) NOT NULL,
   name CHAR( 40 ) NOT NULL,
   rep_key CHAR( 5 ) NOT NULL,
   PRIMARY KEY ( cust_key )
)
CREATE TABLE RemoteCustomer(
   cust_key CHAR( 12 ) NOT NULL,
   name CHAR( 40 ) NOT NULL,
   rep_key CHAR( 5 ) NOT NULL,
   PRIMARY KEY ( cust_key )
)

Each of these tables has exactly the same columns and data types as the Customer table itself. The only difference in their definition is that they do not have a foreign key to the SalesRep table.

The conflict resolution procedure reports conflicts into a table named ConflictLog, which has the following definition:

CREATE TABLE ConflictLog (
   conflict_key numeric(5, 0) identity not null,
   lost_name char(40) not null ,
   won_name char(40) not null ,
   primary key ( conflict_key )
)

The conflict resolution procedure is as follows:

CREATE PROCEDURE ResolveCustomer
AS
BEGIN
   DECLARE @cust_key CHAR(12)
   DECLARE @lost_name CHAR(40)
   DECLARE @won_name CHAR(40)

 // Get the name that was lost
   // from OldCustomer
   SELECT @lost_name=name,
      @cust_key=cust_key
   FROM OldCustomer

 // Get the name that won
   // from Customer
   SELECT @won_name=name
   FROM Customer
   WHERE cust_key = @cust_key

 INSERT INTO ConflictLog ( lost_name, won_name )
   VALUES ( @lost_name, @won_name )
END

This resolution procedure does not use the RemoteCustomer table.

How the conflict resolution works 

The stored procedure is the key to the conflict resolution. It works as follows:

1. Obtains the @lost_name value from the OldCustomer table, and also obtains a primary key value so that the real table can be accessed.

   The @lost_name value is the value that was overridden by the conflict-causing UPDATE.

2. Obtains the @won_name value from the Customer table itself. This is the value that overrode @lost_name. The stored procedure runs after the update has taken place, which is why the value is present in the Customer table. This behavior is different from SQL Remote for Adaptive Server Anywhere, where conflict resolution is implemented in a BEFORE trigger.

3. Adds a row into the ConflictLog table containing the @lost_name and @won_name values.

4. After the procedure is run, the rows in the OldCustomer and RemoteCustomer tables are deleted by the Message Agent. In this simple example, the RemoteCustomer row was not used.

Testing the example 

To test the example

1. Create the tables and the procedure in the consolidated database, and add them as conflict resolution objects to the Customer table.

2. Insert and commit a change at the consolidated database. For example:

   UPDATE Customer
   SET name = 'Sea Sports'
   WHERE cust_key='cust1'
   go
   COMMIT
   go

3. Insert and commit a different change to the same line at the remote database. For example:

   UPDATE Customer
   SET name = 'C Sports'
   WHERE cust_key='cust1'
   go
   COMMIT
   go

4. Replicate the change from the remote to the consolidated database, by running the Message Agent at the remote database to send the message, and then at the consolidated database to receive and apply the message.

5. At the consolidated database, view the Customer table and the ConflictLog table. The Customer table contains the value from the remote database:

   cust_key	name	rep_key
   cust1	C Sports	rep1

   The ConflictLog table has a single row, showing the conflict:

   conflict_key	lost_name	won_name
   1	Sea Sports	C Sports