SQL SERVER通过触发器实现跨服务器会员同步代码

发布时间:2017-04-19作者:周

(注:原创内容,转载请注明出处北京平东信息


需求

两个服务器之间,数据库通过触发器进行会员同步。

具体说明

通过触发器的方法,再通过各种不可描述的过程,实现服务器A向服务器B同步会员。

准备工作

服务器A:创建数据库DB_A,添加表User

USE [DB_A]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Number] [nvarchar](200) NULL,
	[Pwd] [nvarchar](200) NULL,
	[Name] [nvarchar](200) NULL,
	[OpenId] [nvarchar](200) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_User] UNIQUE NONCLUSTERED 
(
	[Number] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


服务器B:创建数据库DB_B,添加表Member

USE [DB_B]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Member](
	[Pkid] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](200) NULL,
	[Password] [nvarchar](200) NULL,
	[RealName] [nvarchar](200) NULL,
	[OpenId] [nvarchar](200) NULL,
	[Enabled] [bit] NOT NULL,
	[CreateTime] [datetime] NULL,
	[ExpireTime] [datetime] NULL,
	[FromMall] [bit] NOT NULL,
	[Number] [nvarchar](200) NULL,
	[IsPass] [bit] NOT NULL,
 CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED 
(
	[Pkid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_Member] UNIQUE NONCLUSTERED 
(
	[UserName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Member] ADD  CONSTRAINT [DF_Member_Enabled]  DEFAULT ((0)) FOR [Enabled]
GO
ALTER TABLE [dbo].[Member] ADD  CONSTRAINT [DF_Member_FromMall]  DEFAULT ((0)) FOR [FromMall]
GO
ALTER TABLE [dbo].[Member] ADD  CONSTRAINT [DF_Member_IsPass]  DEFAULT ((0)) FOR [IsPass]
GO


添加远程服务器

服务器A中添加服务器B的链接:

1.添加服务器B链接

exec sp_addlinkedserver '服务器B的别名', ' ', 'SQLOLEDB ', '

2.添加服务器B登录

exec sp_addlinkedsrvlogin '服务器B的别名', 'false ',null, '服务器B的数据库用户名', '服务器B的数据库密码'

其他方法:

删除服务器连接和登陆

exec sp_dropserver '服务器B的别名','droplogins'


创建插入触发器

服务器A添加插入触发器

测试插入触发器

服务器A添加测试数据

添加重复测试

(出错:违反了 UNIQUE KEY 约束 'IX_User'。不能在对象 'dbo.User' 中插入重复键。)

关联问题

删除User的aoao,向User添加aoao,不会出错。

(存在的问题:新增的aoao和Member的aoao是关联的,但逻辑上他们是无关的;如果需要,可修改插入触发器,当存在记录时,取消FromMall标记)

关联测试

删除Member的aoao,向Member添加aoao;删除User的aoao,向User添加aoao,不会出错。
(两条重名aoao,不相关)


创建删除触发器

测试删除触发器

(没有FromMall标记的记录不会被删除)


创建更新触发器

测试更新触发器


其他触发器方法

暂停触发器

恢复触发器

暂停约束

恢复约束

删除触发器


服务器配置要求

服务器要求开启msdtc服务。

SqlServer2005以上

防火墙相关配置

常见问题

1.msdtc 不可用

需要开启Distributed Transaction Coordinato服务,及其相关依赖。

2.本地测试时(localhost),其他会话正在使用事务的上下文。

sql server 2005不支持在分布式事务处理中存在指向本地的链接服务器(环回链接服务器)。

3.可能出现各种数据库错误提示,此处不再赘述,请自行百度。