sql server触发器存入远程数据库.doc

1、SQL code通过触发器实现数据库的即时同步 -即时同步两个表的实例: -测试环境:SQL2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test-创建测试表,不能用标识列做主键,因为不能进行正常更新-在本机上创建测试表,远程主机上也要做同样的建表操作,只是不写触发器if exists (select * from dbo.sysobjects where id = object_id(Ntest) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table testcreate table test(id int not null

2、constraint PK_test primary key,name varchar(10)go-创建同步的触发器create trigger t_test on testfor insert,update,deleteasset 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

3、 start msdtc,no_output-进行分布事务处理,如果表用标识列做主键,用下面的方法BEGIN DISTRIBUTED TRANSACTIONdelete 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 insertedcommit trango-插入数据测试insert into testselect 1,aaunion all se

4、lect 2,bbunion all select 3,cunion all select 4,ddunion all select 5,abunion all select 6,bcunion 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 joinopenrowset(sqloledb,xz;sa;,test.dbo.test) b on a.id=

5、b.idSQLSERVER 本地查询更新操作远程数据库的代码复制代码代码如下:-PKselect * from sys.key_constraints where object_id = OBJECT_ID(TB)-FKselect * from sys.foreign_keys where parent_object_id =OBJECT_ID(TB)-创建链接服务器exec sp_addlinkedserver ITSV , , SQLOLEDB , 远程服务器名或 ip地址 exec sp_addlinkedsrvlogin ITSV , false ,null, 用户名 , 密码 -查

6、询示例select * from ITSV.数据库名.dbo.表名-导入示例select * into 表 from ITSV.数据库名.dbo.表名-以后不再使用时删除链接服务器exec sp_dropserver ITSV , droplogins -连接远程/局域网数据(openrowset/openquery/opendatasource)-1、openrowset-查询示例select * from openrowset( SQLOLEDB , sql 服务器名 ; 用户名 ; 密码 ,数据库名.dbo. 表名)-生成本地表select * into 表 from openrowse

7、t( SQLOLEDB , sql 服务器名 ; 用户名 ; 密码 ,数据库名.dbo.表名)-把本地表导入远程表insert openrowset( SQLOLEDB , sql 服务器名 ; 用户名 ; 密码 ,数据库名.dbo.表名 )select *from 本地表-更新本地表update bset b.列 A=a.列 Afrom openrowset( SQLOLEDB , sql 服务器名 ; 用户名 ; 密码 ,数据库名.dbo.表名 )as a inner join 本地表 bon a.column1=b.column1-openquery 用法需要创建一个连接-首先创建一个连

8、接创建链接服务器exec sp_addlinkedserver ITSV , , SQLOLEDB , 远程服务器名或 ip地址 -查询select *FROM openquery(ITSV, SELECT * FROM 数据库.dbo. 表名 )-把本地表导入远程表insert openquery(ITSV, SELECT * FROM 数据库 .dbo.表名 )select * from 本地表-更新本地表update bset b.列 B=a.列 BFROM openquery(ITSV, SELECT * FROM 数据库.dbo. 表名 ) as ainner join 本地表 b

9、on a.列 A=b.列 A-3、opendatasource/openrowsetSELECT *FROM opendatasource( SQLOLEDB , Data Source=ip/ServerName;User ID=登陆名;Password=密码 ).test.dbo.roy_taSQL 不同服务器数据库之间的数据操作整理(完整版)-1. 创建链接服务器-1.1 创建一个链接名EXEC sp_addlinkedserver LinkName,SQLOLEDB,远程服务器名或 ip 地址 -有自定义实例名还要加上“/实例名“/*例如： EXEC sp_addlinkedserve

10、r TonyLink,SQLOLEDB,192.168.58.208 */ -1.2 创建登录信息 (或叫创建链接服务器登录名映射)(只需选择一种方式)-1.2.1 以 windows 认证的方式登录EXEC sp_addlinkedsrvlogin LinkName -或 EXEC sp_addlinkedsrvlogin LinkName,true/*例如： EXEC sp_addlinkedsrvlogin TonyLink */ -1.2.2 以 SQL 认证的方式登录 EXEC sp_addlinkedsrvlogin LinkName,false,NULL,用户名,密码/*例如：

11、EXEC sp_addlinkedsrvlogin TonyLink,false,null,sa,123 */-2. 链接服务器相关数据操作-2.1 查询示例SELECT * FROM LinkName.数据库名.架构名.表名/*例如： SELECT * FROM TonyLink.Mydb.dbo.tb */-2.2 导入示例SELECT * INTO 表名 FROM LinkName.数据库名.架构名.表名/*例如： SELECT * INTO Newtb FROM TonyLink.Mydb.dbo.tb */-2.3 更新示例UPDATE LinkName.数据库名.架构名.表名 SE

12、T 字段=值 WHERE 字段=条件/*例如： UPDATE TonyLink.Mydb.dbo.tb SET Persons=g WHERE Persons=a */-2.4 删除示例DELETE LinkName.数据库名 .架构名.表名 WHERE 字段名 =条件/*例如： DELETE TonyLink.Mydb.dbo.tb WHERE Persons=g */-3. 通过行集函数(OPENQUERY/OPENROWSET/OPENDATASOURCE)操作方法-3.1 OPENQUERY 方法（需要借助刚创建的链接服务器）：-3.1.1 查询示例SELECT * FROM OPEN

13、QUERY(LinkName,SELECT * FROM 数据库名.架构名.表名)/* 例如：SELECT * FROM OPENQUERY(TonyLink,SELECT * FROM Mydb.dbo.tb)*/-3.1.2 导入示例-3.1.2.1 导入所有列INSERT OPENQUERY(LinkName, SELECT * FROM 数据库名.架构名.表名)SELECT * FROM 本地表/* 例如：INSERT OPENQUERY(TonyLink,SELECT * FROM Mydb.dbo.tb) SELECT * FROM tb */-3.1.2.2 导入指定列INSER

14、T OPENQUERY(LinkName, SELECT * FROM 数据库名.架构名.表名) (列, 列.)SELECT 列 ,列. FROM 本地表/* 例如：INSERT OPENQUERY(TonyLink,SELECT * FROM Mydb.dbo.tb)(RANGE,LEVEL,Persons)SELECT RANGE,LEVEL,Persons FROM tb*/-3.1.3 更新示例UPDATE OPENQUERY(LinkName, SELECT * FROM 数据库名 .架构名.表名) SET 字段=值 WHERE 字段=条件/*例如： UPDATE OPENQUERY

15、(TonyLink, SELECT * FROM Mydb.dbo.tb) SET Persons=g WHERE Persons=a */-3.1.4 删除示例DELETE OPENQUERY(LinkName, SELECT * FROM 数据库名 .架构名.表名) WHERE 字段名=条件/*例如： DELETE OPENQUERY(TonyLink, SELECT * FROM Mydb.dbo.tb)WHERE Persons=g */-3.2 OPENROWSET 方法(不需要用到创建好的链接名。如果连接的实例名不是默认的，需要在“sql 服务器名或 IP 地址“后加上“/实例名“

16、)-3.2.1 查询示例-3.2.1.1 Windows 认证方式查询(以下方法之一即可)SELECT * FROM OPENROWSET(SQLOLEDB, server=sql 服务器名或 IP 地址;Trusted_Connection=yes,数据库名.架构名.表名)SELECT * FROM OPENROWSET(SQLNCLI, server=sql 服务器名或 IP 地址;Trusted_Connection=yes,数据库名.架构名.表名)SELECT * FROM OPENROWSET(SQLOLEDB, server=sql 服务器名或 IP 地址;Trusted_Conn

17、ection=yes,SELECT * FROM 数据库名.架构名.表名)SELECT * FROM OPENROWSET(SQLNCLI, server=sql 服务器名或 IP 地址;Trusted_Connection=yes,SELECT * FROM 数据库名.架构名.表名)/* 例如：SELECT * FROM OPENROWSET(SQLOLEDB,Server=192.168.58.208;Trusted_Connection=yes,Mydb.dbo.tb)或： SELECT * FROM OPENROWSET(SQLNCLI,Server=192.168.58.208;Tr

18、usted_Connection=yes,Mydb.dbo.tb)或：SELECT * FROM OPENROWSET(SQLOLEDB,Server=192.168.58.208;Trusted_Connection=yes,SELECT * FROM Mydb.dbo.tb) 或：SELECT * FROM OPENROWSET(SQLNCLI,Server=192.168.58.208;Trusted_Connection=yes,SELECT * FROM Mydb.dbo.tb)*/-3.2.1.2 SQL 认证方式查询 (以下方法之一即可)SELECT * FROM OPENROW

19、SET(SQLOLEDB, server=sql 服务器名或 IP 地址;uid=用户名;pwd=密码,数据库名.架构名.表名)SELECT * FROM OPENROWSET(SQLNCLI, server=sql 服务器名或 IP 地址;uid=用户名;pwd=密码,数据库名.架构名.表名)SELECT * FROM OPENROWSET(SQLOLEDB, server=sql 服务器名或 IP 地址;uid=用户名;pwd=密码,SELECT * FROM 数据库名.架构名.表名)SELECT * FROM OPENROWSET(SQLNCLI, server=sql 服务器名或 IP

20、 地址;uid=用户名;pwd=密码,SELECT * FROM 数据库名.架构名.表名)SELECT * FROM OPENROWSET(SQLOLEDB, sql 服务器名; 用户名; 密码,数据库名.架构名.表名)SELECT * FROM OPENROWSET(SQLNCLI, sql 服务器名 ;用户名; 密码,数据库名.架构名.表名)SELECT * FROM OPENROWSET(SQLOLEDB, sql 服务器名; 用户名; 密码,SELECT * FROM 数据库名.架构名.表名)SELECT * FROM OPENROWSET(SQLNCLI, sql 服务器名 ;用户名

21、; 密码,SELECT * FROM 数据库名.架构名.表名)/* 例如：SELECT * FROM OPENROWSET(SQLOLEDB,server=192.168.58.208;uid=sa;pwd=123,mydb.dbo.tb) 或：SELECT * FROM OPENROWSET(SQLNCLI,server=192.168.58.208;uid=sa;pwd=123,mydb.dbo.tb) 或：SELECT * FROM OPENROWSET(SQLOLEDB,server=192.168.58.208;uid=sa;pwd=123,SELECT * FROM Mydb.db

22、o.tb) 或：SELECT * FROM OPENROWSET(SQLNCLI,server=192.168.58.208;uid=sa;pwd=123,SELECT * FROM Mydb.dbo.tb)或：SELECT * FROM OPENROWSET(SQLOLEDB,192.168.58.208;sa;123,mydb.dbo.tb) 或：SELECT * FROM OPENROWSET(SQLNCLI,192.168.58.208;sa;123,mydb.dbo.tb) 或：SELECT * FROM OPENROWSET(SQLOLEDB,192.168.58.208;sa;1

23、23,SELECT * FROM Mydb.dbo.tb) 或：SELECT * FROM OPENROWSET(SQLNCLI,192.168.58.208;sa;123,SELECT * FROM Mydb.dbo.tb) */-3.2.2 导入示例-3.2.2.1 导入所有列INSERT OPENROWSET(SQLOLEDB, server=sql 服务器名或 IP 地址;uid=用户名;pwd=密码,数据库名.架构名.表名)SELECT * FROM 本地表/* 例如：INSERT OPENROWSET(SQLOLEDB,server=192.168.58.208;uid=sa;pw

24、d=123,mydb.dbo.tb)SELECT * FROM tb*/-3.2.2.2 导入指定列INSERT OPENROWSET(SQLOLEDB, server=sql 服务器名或 IP 地址;uid=用户名;pwd=密码,数据库名.架构名.表名)(列,列.)SELECT 列,列. FROM 本地表/* 例如：INSERT OPENROWSET(SQLOLEDB,server=192.168.58.208;uid=sa;pwd=123,mydb.dbo.tb)(RANGE,LEVEL,Persons)SELECT RANGE,LEVEL,Persons FROM tb*/-注：更多替代

25、方法参考.2.1 查询示例，只需替换行集函数 (OPENROWSET)内的内容即可。-3.2.3 更新示例UPDATE OPENROWSET(SQLOLEDB, server=sql 服务器名或 IP 地址;uid= 用户名;pwd=密码,数据库名.架构名.表名)SET 字段 =值WHERE 字段=条件/*例如： UPDATE OPENROWSET(SQLOLEDB,server=192.168.58.208;uid=sa;pwd=123,mydb.dbo.tb)SET Persons=gWHERE Persons=a*/-注：更多替代方法参考.2.1 查询示例，只需替换行集函数 (OPENR

26、OWSET)内的内容即可。-3.2.4 删除示例DELETE OPENROWSET(SQLOLEDB, server=sql 服务器名或 IP 地址;uid=用户名;pwd=密码,数据库名.架构名.表名) WHERE 字段名 =条件/*例如： DELETE OPENROWSET(SQLOLEDB,server=192.168.58.208;uid=sa;pwd=123,mydb.dbo.tb) WHERE Persons=g*/-注：更多替代方法参考.2.1 查询示例，只需替换行集函数 (OPENROWSET)内的内容即可。-3.3 OPENDATASOURCE 方法(不需要用到创建好的链接名

27、。如果连接的实例名不是默认的，需要在“sql 服务器名或 IP 地址“后加上“/实例名“)-3.3.1 查询示例-3.3.1.1 Windows 认证方式查询(以下方法之一即可)SELECT * FROM OPENDATASOURCE(SQLOLEDB, server=sql 服务器名或 IP 地址;Trusted_Connection=yes).数据库名.架构名.表名SELECT * FROM OPENDATASOURCE(SQLNCLI, server=sql 服务器名或 IP 地址;Trusted_Connection=yes).数据库名.架构名.表名/* 例如：SELECT * FRO

28、M OPENDATASOURCE(SQLOLEDB,Server=192.168.58.208;Trusted_Connection=yes).Mydb.dbo.tb或： SELECT * FROM OPENDATASOURCE(SQLNCLI,Server=192.168.58.208;Trusted_Connection=yes).Mydb.dbo.tb*/-3.3.1.2 SQL 认证方式查询 (以下方法之一即可)SELECT * FROM OPENDATASOURCE(SQLOLEDB, server=sql 服务器名或 IP 地址;uid=用户名;pwd= 密码).数据库名.架构名.

29、表名SELECT * FROM OPENDATASOURCE(SQLNCLI, server=sql 服务器名或 IP 地址;uid=用户名;pwd= 密码).数据库名.架构名.表名SELECT * FROM OPENDATASOURCE(SQLOLEDB, Data Source=sql 服务器名或 IP地址;uid=用户名;pwd= 密码).数据库名.架构名.表名SELECT * FROM OPENDATASOURCE(SQLNCLI, Data Source=sql 服务器名或 IP 地址;uid=用户名;pwd= 密码). 数据库名.架构名.表名/* 例如：SELECT * FROM OPENDATASOURCE(SQLOLEDB,Server=192.168.58.208;uid=sa;pwd=123).mydb.dbo.tb 或：SELECT * FROM OPENDATASOURCE(SQLNCLI,Server=192.168.58.208;uid=sa;pwd=123).mydb.dbo.tb