Merge replication scenario - how to have inventory working properly

Hi,
I'm using merge replication to replicate the Customers, Orders,
OrderDetails and Stock tables from server A to server B.
Everythings works as expected except the stockage level for a product.
Think about this scenario:
1) Initially the stockage level of product 1 is 20 units.
2) Server A creates a new order with 5 units of product 1.
Stock table in server A now has 15 units for product 1.
3) Server B creates another order with 3 units of product 1.
Stock table in server B now has 17 units for product 1.
4) Synchronization takes place, and there is an update conflict in the
stock table for product 1. Server A wants to save 15 and server B wants
to save 17.
Either value is incorrect because the stockage level shoud be 12.
Is there any way to have this working as expected? I have thought of
creating a custom resolver, but I think there isn't a way to get the
stockage level after the previous synchronization in the conflict
handler, substract that value from the current stockage level, do the
same with the data from the other server and combine the values to get
the proper result.
Thanks a lot!
Manu,
you could have a table which shows initial stock (20). After that the
remaining stock is a view which is initial stock - sum of orders and in this
case there won't be any conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Inventory is defined as the number of units in stock - the number of units
sold. The application maintains inventory in server a.
When server a and server b sync orders will have to move up from server b to
server a. A trigger off the orderdetails table can fire and update the
inventory table on server a and keep it in sync, this trigger can be
designed to only fire on actions originating from server b.
Then the problem becomes keeping the inventory table in sync in both
locations. This can be done as a download only article, but it will be
updated with the next sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Manu" <manunews@.gmail.com> wrote in message
news:1169553887.302039.76620@.m58g2000cwm.googlegro ups.com...
> Hi,
> I'm using merge replication to replicate the Customers, Orders,
> OrderDetails and Stock tables from server A to server B.
> Everythings works as expected except the stockage level for a product.
> Think about this scenario:
> 1) Initially the stockage level of product 1 is 20 units.
> 2) Server A creates a new order with 5 units of product 1.
> Stock table in server A now has 15 units for product 1.
> 3) Server B creates another order with 3 units of product 1.
> Stock table in server B now has 17 units for product 1.
> 4) Synchronization takes place, and there is an update conflict in the
> stock table for product 1. Server A wants to save 15 and server B wants
> to save 17.
> Either value is incorrect because the stockage level shoud be 12.
> Is there any way to have this working as expected? I have thought of
> creating a custom resolver, but I think there isn't a way to get the
> stockage level after the previous synchronization in the conflict
> handler, substract that value from the current stockage level, do the
> same with the data from the other server and combine the values to get
> the proper result.
> Thanks a lot!
>
|||Thanks for the help.
On Jan 23, 2:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> Inventory is defined as the number of units in stock - the number of units
> sold. The application maintains inventory in server a.
> When server a and server b sync orders will have to move up from server b to
> server a. A trigger off the orderdetails table can fire and update the
> inventory table on server a and keep it in sync, this trigger can be
> designed to only fire on actions originating from server b.
> Then the problem becomes keeping the inventory table in sync in both
> locations. This can be done as a download only article, but it will be
> updated with the next sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Manu" <manun...@.gmail.com> wrote in messagenews:1169553887.302039.76620@.m58g2000cwm.go oglegroups.com...
>
>
>
>
>
>