SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Anywhere
    Managing conflicts
      Conflict resolution examples

Resolving inventory conflicts

Consider a warehouse system for a manufacturer of sporting goods. There is a table of product information, with a quantity column holding the number of each product left in stock. An update to this column will typically deplete the quantity in stock or, if a new shipment is brought in, add to it.

A sales representative at a remote database enters an order, depleting the stock of small tank top tee shirts by five, from 28 to 23, and enters this in on her database. Meanwhile, before this update is replicated to the consolidated database, a new shipment of tee shirts comes in, and the warehouse enters the shipment, adding 40 to the quantity column to make it 68.

The warehouse entry gets added to the database: the quantity column now shows there are 68 small tank-top tee shirts in stock. When the update from the sales representative arrives, it causes a conflict-Adaptive Server Anywhere detects that the update is from 28 to 23, but that the current value of the column is 68.

By default, the most recent UPDATE succeeds, and the inventory level is set to the incorrect value of 23.

In this case the conflict should be resolved by summing the changes to the inventory column to produce the final result, so that a final value of 63 is placed into the database.

Implementing the solution 

A suitable RESOLVE UPDATE trigger for this situation would add the increments from the two updates. For example,

CREATE TRIGGER resolve_quantity
RESOLVE UPDATE OF quantity
ON "DBA".product
REFERENCING OLD AS old_name
NEW AS new_name
REMOTE AS remote_name
FOR EACH ROW
BEGIN
   SET new_name.quantity =   new_name.quantity
                        + old_name.quantity
                        - remote_name.quantity
END

This trigger adds the difference between the old value in the consolidated database (68) and the old value in the remote database when the original UPDATE was executed (28) to the new value being sent, before the UPDATE is implemented. Thus, new_val.quantity becomes 63 (= 23 + 68 - 28), and this value is entered into the quantity column.

Consistency is maintained at the remote database as follows:

1. The original remote UPDATE changed the value from 28 to 23.

2. The warehouse's entry is replicated to the remote database, but fails as the old value is not what was expected.

3. The changes made by the RESOLVE UPDATE trigger are replicated to the remote database.