sql server触发器存入远程数据库.docx
- 文档编号:4379625
- 上传时间:2022-12-01
- 格式:DOCX
- 页数:26
- 大小:30.25KB
sql server触发器存入远程数据库.docx
《sql server触发器存入远程数据库.docx》由会员分享,可在线阅读,更多相关《sql server触发器存入远程数据库.docx(26页珍藏版)》请在冰豆网上搜索。
sqlserver触发器存入远程数据库
SQLcode
通过触发器实现数据库的即时同步
---即时同步两个表的实例:
--测试环境:
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
setXACT_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
SQLSERVER本地查询更新操作远程数据库的代码
复制代码代码如下:
--PK
select*fromsys.key_constraintswhereobject_id=OBJECT_ID('TB')
--FK
select*fromsys.foreign_keyswhereparent_object_id=OBJECT_ID('TB')
--创建链接服务器
execsp_addlinkedserver'ITSV','','SQLOLEDB','远程服务器名或ip地址'
execsp_addlinkedsrvlogin'ITSV','false',null,'用户名','密码'
--查询示例
select*fromITSV.数据库名.dbo.表名
--导入示例
select*into表fromITSV.数据库名.dbo.表名
--以后不再使用时删除链接服务器
execsp_dropserver'ITSV','droplogins'
--连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset
--查询示例
select*fromopenrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)
--生成本地表
select*into表fromopenrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)
--把本地表导入远程表
insertopenrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)
select*from本地表
--更新本地表
updateb
setb.列A=a.列A
fromopenrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)asainnerjoin本地表b
ona.column1=b.column1
--openquery用法需要创建一个连接
--首先创建一个连接创建链接服务器
execsp_addlinkedserver'ITSV','','SQLOLEDB','远程服务器名或ip地址'
--查询
select*
FROMopenquery(ITSV,'SELECT*FROM数据库.dbo.表名')
--把本地表导入远程表
insertopenquery(ITSV,'SELECT*FROM数据库.dbo.表名')
select*from本地表
--更新本地表
updateb
setb.列B=a.列B
FROMopenquery(ITSV,'SELECT*FROM数据库.dbo.表名')asa
innerjoin本地表bona.列A=b.列A
--3、opendatasource/openrowset
SELECT*
FROMopendatasource('SQLOLEDB','DataSource=ip/ServerName;UserID=登陆名;Password=密码').test.dbo.roy_ta
SQL不同服务器数据库之间的数据操作整理(完整版)
--1. 创建链接服务器
--1.1 创建一个链接名
EXEC sp_addlinkedserver 'LinkName','','SQLOLEDB','远程服务器名或ip地址' --有自定义实例名还要加上"/实例名"
/*例如:
EXECsp_addlinkedserver'TonyLink','','SQLOLEDB','192.168.58.208'*/
--1.2 创建登录信息(或叫创建链接服务器登录名映射)(只需选择一种方式)
--1.2.1 以windows认证的方式登录
EXEC sp_addlinkedsrvlogin 'LinkName' --或EXECsp_addlinkedsrvlogin'LinkName','true'
/*例如:
EXECsp_addlinkedsrvlogin'TonyLink' */
--1.2.2 以SQL认证的方式登录
EXEC sp_addlinkedsrvlogin 'LinkName','false',NULL,'用户名','密码'
/*例如:
EXECsp_addlinkedsrvlogin'TonyLink','false',null,'sa','123'*/
--2. 链接服务器相关数据操作
--2.1 查询示例
SELECT * FROM LinkName.数据库名.架构名.表名
/*例如:
SELECT*FROMTonyLink.Mydb.dbo.tb*/
--2.2 导入示例
SELECT * INTO 表名 FROM LinkName.数据库名.架构名.表名
/*例如:
SELECT*INTONewtbFROMTonyLink.Mydb.dbo.tb*/
--2.3 更新示例
UPDATE LinkName.数据库名.架构名.表名 SET 字段='值' WHERE 字段='条件'
/*例如:
UPDATETonyLink.Mydb.dbo.tbSETPersons='g'WHEREPersons='a'*/
--2.4 删除示例
DELETE LinkName.数据库名.架构名.表名 WHERE 字段名='条件'
/*例如:
DELETETonyLink.Mydb.dbo.tbWHEREPersons='g'*/
--3. 通过行集函数(OPENQUERY/OPENROWSET/OPENDATASOURCE)操作方法
--3.1OPENQUERY 方法(需要借助刚创建的链接服务器):
--3.1.1 查询示例
SELECT * FROM OPENQUERY(LinkName,'SELECT*FROM 数据库名.架构名.表名')
/* 例如:
SELECT*FROMOPENQUERY(TonyLink,'SELECT*FROMMydb.dbo.tb') */
--3.1.2 导入示例
--3.1.2.1 导入所有列
INSERT OPENQUERY(LinkName, 'SELECT* FROM 数据库名.架构名.表名') SELECT * FROM 本地表
/* 例如:
INSERTOPENQUERY(TonyLink,'SELECT* FROMMydb.dbo.tb')SELECT*FROMtb*/
--3.1.2.2 导入指定列
INSERT OPENQUERY(LinkName, 'SELECT* FROM 数据库名.架构名.表名') (列,列...)
SELECT 列,列... FROM 本地表
/* 例如:
INSERTOPENQUERY(TonyLink,'SELECT* FROMMydb.dbo.tb')(RANGE,LEVEL,Persons)
SELECTRANGE,LEVEL,PersonsFROMtb
*/
--3.1.3 更新示例
UPDATE OPENQUERY(LinkName, 'SELECT*FROM 数据库名.架构名.表名') SET 字段='值' WHERE 字段='条件'
/*例如:
UPDATEOPENQUERY(TonyLink,'SELECT*FROMMydb.dbo.tb') SETPersons='g'WHEREPersons='a'*/
--3.1.4 删除示例
DELETE OPENQUERY(LinkName, 'SELECT*FROM 数据库名.架构名.表名') WHERE 字段名='条件'
/*例如:
DELETEOPENQUERY(TonyLink,'SELECT*FROMMydb.dbo.tb') WHEREPersons='g'*/
--3.2OPENROWSET方法(不需要用到创建好的链接名。
如果连接的实例名不是默认的,需要在"sql服务器名或IP地址"后加上"/实例名")
--3.2.1 查询示例
--3.2.1.1Windows认证方式查询(以下方法之一即可)
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_Connection=yes','SELECT*FROM 数据库名.架构名.表名')
SELECT * FROM OPENROWSET('SQLNCLI', 'server=sql服务器名或IP地址;Trusted_Connection=yes','SELECT*FROM 数据库名.架构名.表名')
/* 例如:
SELECT*FROMOPENROWSET('SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes',Mydb.dbo.tb)
或:
SELECT*FROMOPENROWSET('SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes',Mydb.dbo.tb)
或:
SELECT*FROMOPENROWSET('SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes','SELECT*FROMMydb.dbo.tb')
或:
SELECT*FROMOPENROWSET('SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes','SELECT*FROMMydb.dbo.tb')
*/
--3.2.1.2SQL认证方式查询(以下方法之一即可)
SELECT * FROM OPENROWSET('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地址;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服务器名';'用户名'; '密码','SELECT*FROM 数据库名.架构名.表名')
/* 例如:
SELECT*FROMOPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb)
或:
SELECT*FROMOPENROWSET('SQLNCLI','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb)
或:
SELECT*FROMOPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123','SELECT*FROMMydb.dbo.tb')
或:
SELECT*FROMOPENROWSET('SQLNCLI','server=192.168.58.208;uid=sa;pwd=123','SELECT*FROMMydb.dbo.tb')
或:
SELECT*FROMOPENROWSET('SQLOLEDB','192.168.58.208';'sa';'123',mydb.dbo.tb)
或:
SELECT*FROMOPENROWSET('SQLNCLI','192.168.58.208';'sa';'123',mydb.dbo.tb)
或:
SELECT*FROMOPENROWSET('SQLOLEDB','192.168.58.208';'sa';'123','SELECT*FROMMydb.dbo.tb')
或:
SELECT*FROMOPENROWSET('SQLNCLI','192.168.58.208';'sa';'123','SELECT*FROMMydb.dbo.tb')
*/
--3.2.2 导入示例
--3.2.2.1 导入所有列
INSERT OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)
SELECT * FROM 本地表
/* 例如:
INSERTOPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb)
SELECT*FROMtb
*/
--3.2.2.2 导入指定列
INSERT OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)(列,列...)
SELECT 列,列... FROM 本地表
/* 例如:
INSERTOPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb)(RANGE,LEVEL,Persons)
SELECTRANGE,LEVEL,PersonsFROMtb
*/
--注:
更多替代方法参考.2.1查询示例,只需替换行集函数(OPENROWSET)内的内容即可。
--3.2.3 更新示例
UPDATE OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)
SET 字段='值'
WHERE 字段='条件'
/*例如:
UPDATEOPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb)
SETPersons='g'
WHEREPersons='a'
*/
--注:
更多替代方法参考.2.1查询示例,只需替换行集函数(OPENROWSET)内的内容即可。
--3.2.4 删除示例
DELETE OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)
WHERE 字段名='条件'
/*例如:
DELETEOPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb)
WHEREPersons='g'
*/
--注:
更多替代方法参考.2.1查询示例,只需替换行集函数(OPENROWSET)内的内容即可。
--3.3OPENDATASOURCE方法(不需要用到创建好的链接名。
如果连接的实例名不是默认的,需要在"sql服务器名或IP地址"后加上"/实例名")
--3.3.1 查询示例
--3.3.1.1Windows认证方式查询(以下方法之一即可)
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;Trusted_Connection=yes').数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'server=sql服务器名或IP地址;Trusted_Connection=yes').数据库名.架构名.表名
/* 例如:
SELECT*FROMOPENDATASOURCE('SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes').Mydb.dbo.tb
或:
SELECT*FROMOPENDATASOURCE('SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes').Mydb.dbo.tb
*/
--3.3.1.2SQL认证方式查询(以下方法之一即可)
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'DataSource=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql server触发器存入远程数据库 server 触发器 存入 远程 数据库