Chapter 8. SQL Remote Design forAdaptive Ser verEnter prise
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
Thisresolution procedure does not use the RemoteCustomer table.
Howthe conflict
resolutionwor ks Thestored procedure is the key to the conflict resolution. Itworks as
follows:
1. Obtains the @lost_name value from the OldCustomer table, and also
obtainsa primary key value so that the real table can be accessed.
The@lost_name value is the value that was overridden by the
conflict-causingUPDATE.
2. Obtains the @won_name value from the Customer table itself. Thisis
thevalue that overrode @lost_name. Thestored procedure runs
after
the
updatehas taken place, which is why the value is present in the Customer
table. This behavior is different from SQL Remote for AdaptiveServer
Anywhere,where conflict resolution is implemented in a BEFORE
trigger.
3. Adds a row into the ConflictLog table containing the @lost_name and
@won_namevalues.
4. After the procedure is run, the rows in the OldCustomer and
RemoteCustomertables are deleted by the Message Agent. Inthis
simpleexample, the RemoteCustomer row was not used.
Testingthe example
169