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 typically depletes the quantity in stock or, if a new shipment is brought in, adds 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 her database. Meanwhile, before this update is replicated to the consolidated database, another sales representative receives 40 returned tee shirts. This sales representative enters the returns into his remote database and replicates the changes to the consolidated database at the warehouse, adding 40 to the Quantity column to make it 68.

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

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

In this example, the conflict should be resolved by summing the changes to the inventory column to produce the 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".Products
 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 statement was executed (28) to the new value being sent, before the UPDATE statement is implemented. So, new_name.Quantity becomes 63 (= 23 + 68 - 28), and this value is entered into the Quantity column.

Consistency is maintained on the remote database as follows: