触发器，数据同步

同步两个数据库的示例 
  
  有数据 
  srv1.库名..author有字段:id,name,phone, 
  srv2.库名..author有字段:id,name,telphone,adress 
  
  要求： 
  srv1.库名..author增加记录则srv1.库名..author记录增加 
  srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新 
  --*/ 
  
  --大致的处理步骤 
  --1.在   srv1   上创建连接服务器,以便在   srv1   中操作   srv2,实现同步 
  exec   sp_addlinkedserver     'srv2','','SQLOLEDB','srv2的sql实例名或ip' 
  exec   sp_addlinkedsrvlogin   'srv2','false',null,'用户名','密码' 
  go 
  
  --2.在   srv1   和   srv2   这两台电脑中,启动   msdtc(分布式事务处理服务),并且设置为自动启动 
  我的电脑--控制面板--管理工具--服务--右键   Distributed   Transaction   Coordinator--属性--启动--并将启动类型设置为自动启动 
  go 
  
  
  --3.实现同步处理 
  
  --a.在srv1..author中创建触发器,实现数据即时同步 
  --新增同步 
  create   trigger   tr_insert_author   on   author 
  for   insert 
  as 
  set   xact_abort   on 
  insert   srv2.库名.dbo.author(id,name,telphone) 
  select   id,name,telphone   from   inserted 
  go 
  
  --修改同步 
  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.库名.dbo.author   b,inserted   i 
  where   b.id=i.id 
  go 
  
  --删除同步 
  create   trigger   tr_delete_author   on   author 
  for   delete 
  as 
  set   xact_abort   on 
  delete   b   
  from   srv2.库名.dbo.author   b,deleted   d 
  where   b.id=d.id 
  go 
  
  
  
  --3.实现同步处理的方法2,定时同步 
  
  --在srv1中创建如下的同步处理存储过程 
  create   proc   p_process   
  as 
  --更新修改过的数据 
  update   b   set   name=i.name,telphone=i.telphone 
  from   srv2.库名.dbo.author   b,author   i 
  where   b.id=i.id   and 
  (b.name<>i.name   or   b.telphone<>i.telphone) 
  
  --插入新增的数据 
  insert   srv2.库名.dbo.author(id,name,telphone) 
  select   id,name,telphone   from   author   i 
  where   not   exists( 
  select   *   from   srv2.库名.dbo.author   where   id=i.id) 
  
  --删除已经删除的数据(如果需要的话) 
  delete   b   
  from   srv2.库名.dbo.author   b 
  where   not   exists( 
  select   *   from   author   where   id=b.id) 
  go 
  
  
  
  --然后创建一个作业定时调用上面的同步处理存储过程就行了 
  
  企业管理器 
  --管理 
  --SQL   Server代理 
  --右键作业 
  --新建作业 
  --"常规"项中输入作业名称 
  --"步骤"项 
  --新建 
  --"步骤名"中输入步骤名 
  --"类型"中选择"Transact-SQL   脚本(TSQL)" 
  --"数据库"选择执行命令的数据库 
  --"命令"中输入要执行的语句:   exec   p_process   
  --确定 
  --"调度"项 
  --新建调度 
  --"名称"中输入调度名称 
  --"调度类型"中选择你的作业执行安排 
  --如果选择"反复出现" 
  --点"更改"来设置你的时间安排 
  
  
  然后将SQL   Agent服务启动,并设置为自动启动,否则你的作业不会被执行 
  
  设置方法: 
  我的电脑--控制面板--管理工具--服务--右键   SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定. 
  
  
  
  上面写了两种同步方法,实际使用时,只需要其中任意一种就行了 
  
  如果数据库在同一实例中,则只需要同步处理的部分,并且将处理语句中涉及到的服务器名去掉,即只要: 
  
  库名.dbo.表名  

 

 

 

 

如果只是简单的数据同步,可以用触发器来实现.下面是例子: 
  
  --测试环境: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 
  
  --创建同步的触发器 
  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

 

 

 

 

触发器+链接服务器。

SQL code

--*********************************************************************************

--                 链接服务器，不同服务器数据库之间的数据操作

--*********************************************************************************
1、--创建链接服务器 
exec sp_addlinkedserver   '链接服务器名', ' ', 'SQLOLEDB', '远程服务器名或ip地址 ' 
exec sp_addlinkedsrvlogin  '链接服务器名', 'false ',null, '用户名', '密码' 


2、启动两台服务器的MSDTC服务 
MSDTC服务提供分布式事务服务，如果要在数据库中使用分布式事务，必须在参与的双方服务器启动MSDTC（Distributed Transaction Coordinator）服务。

3、打开双方的135端口
MSDTC服务依赖于RPC（Remote Procedure Call (RPC)）服务,RPC使用135端口，保证RPC服务启动，如果服务器有防火墙，保证135端口不被防火墙挡住。
使用“telnet IP 135”命令测试对方端口是否对外开放。也可用端口扫描软件（比如Advanced Port Scanner）扫描端口以判断端口是否开放


4、--如要创建触发器   
create   trigger   t_test   on   test   
for   insert,update,delete   
as 
--加上下面两句，否则会提示新事务不能登记到指定事务处理器
set    xact_abort   on  
begin  distributed   tran 
    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

--查询示例 
select * from 链接服务器名.数据库名.dbo.表名 

--导入示例 
select * into 表 from 链接服务器名.数据库名.dbo.表名 

--以后不再使用时删除链接服务器 
exec sp_dropserver  '链接服务器名 ', 'droplogins '