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.
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:
The original remote UPDATE statement changed the value from 28 to 23.
The warehouse's entry is replicated to the remote database, but fails as the old value is not what was expected.
The changes made by the RESOLVE UPDATE trigger are replicated to the remote database.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|