Cross-Library synchronization through SQL SERVER triggers

Two servers with two SQL Server A, B

A,B table Structure A,b the same

A,b will do some insert,update,delete operation, require the data of AB to keep consistent

(That is, a new data, request B also add, a change a piece of data, B also change the corresponding)

Is it possible to use triggers to implement

Routines

/*--synchronization of two databases sample

Have data

SRV1. Library name ... Author has fields: Id,name,phone,

Srv2. Library name ... Author have fields: id,name,telphone,adress

Requirements:

SRV1. Library name ... Author added record is SRV1. Library name ... Author record increase

SRV1. Library name ... Author Phone field update, SRV1. Library name ... Author corresponding field Telphone update

--*/

--The approximate processing steps

--1. Create a connection server on the SRV1 to manipulate srv2 in SRV1 to achieve synchronization

exec sp_addlinkedserver ' srv2 ', ', ' SQLOLEDB ', ' srv2 ' SQL instance name or IP '

exec sp_addlinkedsrvlogin ' srv2 ', ' false ', NULL, ' username ', ' password '

Go

--2. On both the SRV1 and SRV2 computers, start MSDTC (Distributed transaction Services) and set to start automatically

My Computer--control Panel--management tools--services--right-distributed Transaction coordinator--Properties-Start-and set the startup type to start automatically

Go

--3. Implementing synchronous processing

--a. In SRV1. Create triggers in author to achieve instant data synchronization

--New Sync

Create Trigger Tr_insert_author on author

For insert

As

Set XACT_ABORT on

Insert Srv2. Library name. Dbo.author (Id,name,telphone)

Select Id,name,telphone from inserted

Go

--Modifying synchronization

Create Trigger Tr_update_author on author

For update

As

Set XACT_ABORT on

Update B Set Name=i.name,telphone=i.telphone

From SRV2. Library name. Dbo.author b,inserted I

where b.id=i.id

Go

--Delete Sync

Create Trigger Tr_delete_author on author

For delete

As

Set XACT_ABORT on

Delete b

From SRV2. Library name. Dbo.author b,deleted D

where b.id=d.id

Go

--3. method to achieve synchronous processing 2, timed synchronization

--Create the following synchronization stored procedure in SRV1

Create proc P_process

As

--updating data that has been modified

Update B Set Name=i.name,telphone=i.telphone

From SRV2. Library name. Dbo.author B,author I

where B.id=i.id and

(B.name<>i.name or B.telphone<>i.telphone)

--Insert new data

Insert Srv2. Library name. Dbo.author (Id,name,telphone)

Select Id,name,telphone from author I

Where NOT EXISTS (

SELECT * from SRV2. Library name. Dbo.author where Id=i.id)

--delete data that has been deleted (if required)

Delete b

From SRV2. Library name. Dbo.author b

Where NOT EXISTS (

SELECT * from author where id=b.id)

Go