--第一种情况是：针对分布于不同服务器上的数据库而言的
--客户端和服务器端表分别为testClient，testServer，表结构一样，如下：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testClient]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[testClient]
GO
CREATE TABLE [dbo].[testClient] (
[id] [int] NOT NULL ,
[email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--测试远程链接服务器
EXEC sp_addlinkedserver
'clover',
'',
'MSDASQL',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=clover;UID=sa;PWD=sa;'
go
EXEC sp_addlinkedsrvlogin @rmtsrvname='clover',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='sa'
--测试分布式事务
--客户端和服务器端同时开启分布式事务处理服务，开启方法为：
--我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动
--go
set XACT_ABORT on
set ANSI_NULL_DFLT_ON on
set ANSI_WARNINGS on
BEGIN DISTRIBUTED TRANSACTION
select * from xiaowang.pubs.dbo.testServer
commit tran
--用触发器测试同步流程
--1。客户端上触发器
--新增同步
create trigger tr_insert_testClient on testClient
for insert
as
set xact_abort on
insert xiaowang.pubs.dbo.testServer(id,email)
select id,email from inserted
go
--测试插入触发器
insert into testClient(id,email) values('21','dfdsf')
--查看触发器状态
EXEC sp_helptrigger testClient
--删除同步
create trigger tr_delete_testClient on testClient
for delete
as
set xact_abort on
delete b
from xiaowang.pubs.dbo.testServer b,deleted d
where b.id=d.id
go
--修改同步
create trigger tr_update_testClient on testClient
for update
as
set xact_abort on
update a set id=b.id,email=b.email
from xiaowang.pubs.dbo.testServer a,inserted b
where a.id=b.id
go
--上面可以实现从客户端到服务器的同步，测试全部正常;
--以下情况是针对同一服务器上的不同数据库
--建立测试表1
CREATE TABLE T1(ID INT,NAMES VARCHAR(10))
INSERT T1 SELECT 1,'A'
UNION ALL SELECT 2,'B'
---建立测试表2
CREATE TABLE T2(ID INT,NAMES VARCHAR(10))
INSERT T2 SELECT 1,'A'
UNION ALL SELECT 2,'B'
---建立触发器1，来同步t2
CREATE TRIGGER TRI_T1
ON T1
FOR UPDATE
AS
UPDATE T2
SET ID=C.ID,NAMES=C.NAMES
FROM T2 A ,DELETED B,INSERTED C
WHERE A.ID=B.ID
------建立触发器2，来同步t1
CREATE TRIGGER TRI_T2
ON T2
FOR UPDATE
AS
UPDATE T1
SET ID=C.ID,NAMES=C.NAMES
FROM T1 A ,DELETED B,INSERTED C
WHERE A.ID=B.ID
--
go
--test1
UPDATE T1
SET ID=5,NAMES='CCC'
WHERE ID=1
--RESULTS
SELECT * FROM T2
--TEST2
UPDATE T2
SET ID=6,NAMES='EEE'
WHERE ID=2
--RESULTS
SELECT * FROM T1
--另一种方法用互斥触发器
--做互斥触发器
Create Trigger Tri_Users
On Users
For Insert
As
Alter Table Users1 DISABLE TRIGGER Tri_Users1 --为防止循环，这里先将对方触发器禁用
Insert Users1 Select * From Inserted
Alter Table Users1 Enable TRIGGER Tri_Users1--插入完毕后启用对方触发器
--*********************
Go
Create Trigger Tri_Users1
On Users1
For Insert
As
Alter Table Users DISABLE TRIGGER Tri_Users
Insert Users Select * From Inserted
Alter Table Users Enable TRIGGER Tri_Users
