|
--
-- Create fresh new server database called "pub"
--
drop database pub
go
create database pub
go
CREATE TABLE pub..orders(order_id int NOT NULL primary key, order_date datetime NULL)
go
CREATE TABLE pub..order_details(order_id int NOT NULL primary key, order_details_id int NOT NULL, product nvarchar(100) NULL, quantity int NULL)
go
--
-- Add sync columns
-- 1. Create update_originator_id column defaulted to 0 to indicate server change
ALTER TABLE pub..orders add update_originator_id int null default 0
ALTER TABLE pub..order_details add update_originator_id int null default 0
go
-- Add last update timestamp column
ALTER TABLE pub..orders add update_timestamp timestamp
ALTER TABLE pub..order_details add update_timestamp timestamp
go
-- Add create timestamp column (use bigint since one one timestamp column type is allowed per table)
ALTER TABLE pub..orders add create_timestamp bigint default @@DBTS+1
ALTER TABLE pub..order_details add create_timestamp bigint default @@DBTS+1
go
--
-- Create tombstone tables to store deletes
--
CREATE TABLE pub..orders_tombstone(order_id int NOT NULL primary key, order_date datetime NULL, update_originator_id int default 0, update_timestamp timestamp, create_timestamp bigint)
CREATE TABLE pub..order_details_tombstone(order_id int NOT NULL primary key, order_details_id int NOT NULL, product nvarchar(100) NULL, quantity int NULL, update_originator_id int default 0, update_timestamp timestamp, create_timestamp bigint)
go
--
-- Create Update and Delete Triggers
-- Since there will be changes on the server outside of sync application
-- we need triggers to fixup update_originator_id back to 0 which designated
-- for server change
-- update triggers
use pub
go
CREATE TRIGGER orders_update_trigger on orders for update
as
update o
set o.update_originator_id = 0
from [orders] o join [inserted] i on o.order_id = i.order_id
where not UPDATE(update_originator_id)
go
CREATE TRIGGER order_details_update_trigger on order_details for update
as
update o
set o.update_originator_id = 0
from [order_details] o join [inserted] i on o.order_id = i.order_id
where not UPDATE(update_originator_id)
go
-- delete triggers
use pub
go
CREATE TRIGGER orders_delete_trigger on orders for delete
as
insert into pub..orders_tombstone (order_id, order_date, create_timestamp, update_originator_id) select order_id, order_date, create_timestamp, 0 from deleted
go
CREATE TRIGGER order_details_delete_trigger on order_details for delete
as
insert into pub..order_details_tombstone (order_id, order_details_id, product, quantity, create_timestamp, update_originator_id) select order_id, order_details_id, product, quantity, create_timestamp, 0 from deleted
go
--
-- Test Sample
--
insert into orders (order_id, order_date) values(1, GetDate())
insert into orders (order_id, order_date) values(2, GetDate())
insert into order_details (order_id, order_details_id, product) values(7, 2 , 'DVD')
insert into order_details (order_id, order_details_id, product) values(3, 3 , 'CD')
insert into order_details (order_id, order_details_id, product) values(4, 2 , 'Floppy Disk')
update order_details set product = 'HDTV' where order_id =2
update order_details set order_details_id = 11 where order_id in (20,21,23)
delete orders where order_id = 2
select @@dbts
select * from orders
select * from orders_tombstone
select * from order_details
select * from order_details_tombstone
select * from msrepl_info
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
Entrepreneur, developer and then some. I started
Raveable Hotel Reviews in 2008 to help travelers find the best hotel for their vacation with no history of
bed bug reports (hint: don't go new york without checking
bed bugs in NYC hotels page), cases of personal property theft, or food poisoning incidents.
Prior to Raveable.com, I was a development lead at Microsoft Corporation. I led the team developing the next generation social networking for Windows Mobile. Before that I played leading role in the design and development of Microsoft Sync Framework, Sync Services for ADO.NET, SQL Server Replication and Windows File Replication.
My Company:
Raveable Hotel Reviews
My Sync Blog:
The Synchronizer
My Sync WebSite:
www.syncguru.com