Take Data Offline Using Microsoft Synchronization Services for ADO.NET

--
-- 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