Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » SQL Remote » Creating SQL Remote systems » Update conflicts » Custom conflict resolution using triggers


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 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 first update adds 40 to the Quantity column.

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.

Incorrect conflict resolution: the second update incorrectly overwrites the first update.

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.

Correct conflict resolution: the second update modifies the first update.
 Implementing the solution