Merge Replication: Inventory Conflict Resolution

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Merge Replication: Inventory Conflict Resolution

Author

Topic

otis
Starting Member

2 Posts

Posted - 2005-04-18 : 09:05:14

Hi!

Does anybody have any ideas, solution, advise for this problem:

Say I have an "inventory" table having "quantity" column.
quantity's initial value is 10

At remote site 1 (publisher), orders are made, and quantity is reduced from 10 to 7.
At remote site 2 (subscriber), stocks arrive, and quantity is increased from 10 to 30.

Now when data is merged, quantity would result to 7, right?
But that would be logically wrong, quantity is supposed to be
(10 - 3 + 20) = 27

So, how do you handle this conflict to get the correct quantity?

I have some experience with Sybase and you can use the following to resolve the conflict:

CREATE TRIGGER resolve_quantity
RESOLVE UPDATE OF quantity
ON "DBA".inventory
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

in SQL server,
OLD would be DELETED,
NEW would be INSERTED,
but what about REMOTE?

Any help would be greatly appreciated

uxphreak
Starting Member

38 Posts

Posted - 2005-04-20 : 18:49:29

I'm not sure about your environment, but.....

I have 7 remote sites and one central server. My central server is the publisher and the remote site are the subscribers. Each subscriber is uniquely identifiable, so when a change to product quantity is made, it only affects that remote location.

If I were to manually adjust the quantities of a product, which I sometimes do, I do it before the locations perform any work. This limits the type of conflict you're referring to.

In your scenario, I would be concerned with which conflict would win. If I manually adjust the quantity down, while at the same time a remote location increase the quantity, how am I to know that the quantity I adjust down is in fact correct? If it is correct, then I would adjust it accordingly only after ensuring the remote location does not make any changes during my update. But that's just me.

D

otis
Starting Member

2 Posts

Posted - 2005-04-21 : 01:32:12

I see what you mean, you probably have a public id column indicating which site the row belongs to. I'm also using the same concept for my transaction records, but the inventory records are shared by all sites.

I've thought up an idea, create a trigger for the inventory table which logs all adjustments made to the quantity field.

When merge agent runs and a conflict occurs, I'll use a custom conflict resolver which sums up all the adjustments made by the subscriber then add it to the publisher's inventory.quantity, thereby resulting in the correct quantity.

But I'm still having trouble implementing it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources