通过触发器实现的即时同步

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

--即时同步两个表的实例:

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

--创建测试表,不能用标识列做主键,因为不能进行正常更新
--在本机上创建测试表,远程主机上也要做同样的建表操作,只是不写触发器
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[test]')andOBJECTPROPERTY(id,N'IsUserTable')=1)
droptable[test]

createtabletest(idintnotnullconstraintPK_testprimarykey
 ,namevarchar(10))
go

--创建同步的触发器
createtriggert_testontest
forinsert,update,delete
as
set XACT_ABORTon
--启动远程服务器的MSDTC服务
execmaster..xp_cmdshell'isql/S"xz"/U"sa"/P""/q"execmaster..xp_cmdshell''netstartmsdtc'',no_output"',no_output

--启动本机的MSDTC服务
execmaster..xp_cmdshell'netstartmsdtc',no_output

--进行分布事务处理,如果表用标识列做主键,用下面的方法
BEGINDISTRIBUTEDTRANSACTION
deletefromopenrowset('sqloledb','xz';'sa';'',test.dbo.test)
 whereidin(selectidfromdeleted)
insertintoopenrowset('sqloledb','xz';'sa';'',test.dbo.test)
 select*frominserted
committran
go

--插入数据测试
insertintotest
select1,'aa'
unionallselect2,'bb'
unionallselect3,'c'
unionallselect4,'dd'
unionallselect5,'ab'
unionallselect6,'bc'
unionallselect7,'ddd'

--删除数据测试
deletefromtestwhereidin(1,4,6)

--更新数据测试
updatetestsetname=name+'_123'whereidin(3,5)

--显示测试的结果
select*fromtestafulljoin
openrowset('sqloledb','xz';'sa';'',test.dbo.test)bona.id=b.id


 

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>