SQL Server用触发器实时同步两个表

关键词
SQL Server用触发器实时同步两个表

本文介绍如何SQL Server用触发器实时同步两个表，直接上代码吧。

–数据较少时,可以手工同步,更方便控制

–用触发器即时同步两个表的实例:

–测试环境:SQL2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test

–创建测试表,不能用标识列做主键,因为不能进行正常更新
–在远程主机上建表
if exists (select * from dbo.sysobjects where id = object_id(N'[test]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [test]

create table test(id int not null constraint PK_test primary key
?,name varchar(10))
go

–以下操作在本机进行
–在本机上建表及做同步处理的工作
if exists (select * from dbo.sysobjects where id = object_id(N'[test]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [test]

create table test(id int identity(1,1) primary key
?,name varchar(10))
go

–创建同步的触发器
create trigger t_test on test
for insert,update,delete
as
set? XACT_ABORT on
–启动远程服务器的MSDTC服务
exec master..xp_cmdshell ‘isql /S”xz” /U”sa” /P”” /q”exec master..xp_cmdshell ”net start msdtc”,no_output”‘,no_output

–启动本机的MSDTC服务
exec master..xp_cmdshell ‘net start msdtc’,no_output

–进行分布事务处理,如果表用标识列做主键,用下面的方法
BEGIN DISTRIBUTED TRANSACTION
delete from openrowset(‘sqloledb’,’xz’;’sa’;”,test.dbo.test)
?where id in(select id from deleted)
insert into openrowset(‘sqloledb’,’xz’;’sa’;”,test.dbo.test)
?select * from inserted
commit tran
go

–插入数据测试
insert into test
select 1,’aa’
union all select 2,’bb’
union all select 3,’c’
union all select 4,’dd’
union all select 5,’ab’
union all select 6,’bc’
union all select 7,’ddd’

–删除数据测试
delete from test where id in(1,4,6)

–更新数据测试
update test set name=name+’_123′ where id in(3,5)

–显示测试的结果
select * from test a full join
openrowset(‘sqloledb’,’xz’;’sa’;”,test.dbo.test) b on a.id=b.id

喜欢 (0)赏

[搞代码]

分享 (0)