SQLSERVER2012学习记录

最新推荐文章于 2025-05-14 03:32:12 发布

another_liu

最新推荐文章于 2025-05-14 03:32:12 发布

阅读量660

点赞数

CC 4.0 BY-SA版权

分类专栏： SQLServer

本文链接：https://blog.youkuaiyun.com/another_liu/article/details/79766075

SQLServer 专栏收录该内容

1 篇文章

订阅专栏

本文介绍如何在SQL Server 2012中配置dblink并创建触发器实现数据同步。内容包括dblink的创建与删除、触发器的建立与删除等步骤，并提供了具体的SQL脚本示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQLServer2012下载地址

https://msdn.itellyou.cn/

创建dblink

EXEC sp_addlinkedserver 'dblink94','','SQLOLEDB','192.168.1.94,9090'
EXEC sp_addlinkedsrvlogin 'dblink94','false',NULL,'ncitf','ufsoft123!@#'

删除dblink

exec sp_dropserver 'dblink94','droplogins'

新建触发器

CREATE TRIGGER ic_onhandnum_trigger ON ic_onhandnum FOR DELETE, UPDATE,INSERT 

AS DECLARE
	@pk_onhandnum VARCHAR ( 40 ),
	@nonhandastnum DECIMAL,
	@nonhandnum DECIMAL,
	@ts VARCHAR ( 40 ),
	@orgcode  VARCHAR ( 40 ),
	@orgname VARCHAR ( 200 ),
	@warehousecode VARCHAR ( 40 ),
	@warehousename VARCHAR ( 200 ),
	@invcode VARCHAR ( 40 ),
	@invname VARCHAR ( 200 ); 
	
SELECT
	@pk_onhandnum = pk_onhandnum,
	@nonhandastnum = nonhandastnum,
	@nonhandnum = nonhandnum
FROM
	inserted;
	
 SELECT
	@orgcode=gg.CODE ,
	@orgname=gg.NAME ,
	@warehousecode=cc.CODE ,
	@warehousename=cc.NAME ,
	@invcode=ll.CODE ,
	@invname =ll.NAME ,
	@ts=uu.ts 
FROM
	ic_onhandnum uu,
	ic_onhanddim mm,
	org_orgs gg,
	bd_stordoc cc,
	bd_material ll 
WHERE
	uu.pk_onhanddim = mm.pk_onhanddim 
	AND uu.dr = 0 
	AND gg.pk_org = mm.pk_org 
	AND cc.pk_stordoc = mm.cwarehouseid 
	AND ll.pk_material = mm.cmaterialvid and uu.pk_onhandnum=@pk_onhandnum;

INSERT INTO dblink94.ncsyn.dbo.onhand ( orgcode, orgname, warehousecode,warehousename ,invcode,invname,nonhandnum,ts,status)
VALUES
	( @orgcode, @orgname, @warehousecode,@warehousename,@invcode,@invname,ISNULL(@nonhandnum,0),@ts,'0' );
	
go

删除触发器

IF ( object_id ( 'ic_onhandnum_trigger', 'TR' ) IS NOT NULL )
DROP TRIGGER ic_onhandnum_trigger
go