User's Manual

Chapter 8. SQL Remote Design for Adaptive Server Enterprise
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
169