SQL Server多实例之间触发器同步数据

SQL Server多实例环境搭建：参考链接

在本地完成多实例环境搭建后，数据库中有两个实例，这里成为local实例和Cake实例。

在local实例的[MyTestDB]数据库里的表[test_mytable]上创建触发器：

USE [MyTestDB]
GO
/****** Object:  Trigger [tr_insert]    Script Date: 11/28/2022 16:44:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [tr_insert]
on [test_mytable]
after insert --插入触发
as
 begin
   --定义变量
   declare @name varchar(50)
   declare @age int
   declare @class varchar(50)
   --在inserted表中查询已经插入记录信息
   select @name=name, @age=age, @class=class from inserted
   insert into openrowset('SQLOLEDB', '127.0.0.1\Cake,1450'; 'sa'; 'asas', [MyCakeTestDB].[test_mytable]) (name, age, class) values (@name, @age, @class)
   print '插入成功!'
 
   -- 本来想用下面这种方式建立连接和插入数据的。但是这样写会报错：在sys.servers中找不到服务器'Cake'。在SSMS里新建查询，执行下面前两句，能正常执行后再执行最后一句insert，能正常执行成功。但如果连接登录删除这三句一并执行，则同样会报错：在sys.servers中找不到服务器'Cake'。
   --exec sp_addlinkedserver 'Cake', '', 'SQLOLEDB', '127.0.0.1\Cake,1450' 
   --exec sp_addlinkedsrvlogin 'Cake', 'false', null, 'sa', 'asas'
   --insert into Cake.MyCakeTestDB.test_mytable (name, age, class) values (@name, @age, @class)
 
   -- 从SQL Server本地实例上的已知远程服务器和链接服务器列表中删除服务器
   -- exec sp_dropserver 'Cake', 'droplogins'
 end

这时在local实例的[MyTestDB]数据库里的表[test_mytable]插入数据时，会同时触发触发器，自动往Cake实例的[MyCakeTestDB]数据库里的表[test_mytable]注入数据。

也可以在cmd命令行执行：

C:\Users\Administrator>sqlcmd
1> insert into [MyTestDB].[test_mytable] values ('1', 1, '1')
2> go

如果执行成功会显示：

 
(1 行受影响)
插入成功!
 
(1 行受影响)

---------------------------------------------------------------------------------------

如果插入数据时报错：服务器***上的 MSDTC 不可用，则在cmd命令行里执行net start msdtc，开启服务，或者在windows控制面版-->管理工具-->服务-->Distributed Transaction Coordinator-->右键选择启动。如果Distributed Transaction Coordinator 无法启动，则是因为丢失了日志文件,重新创建日志文件,再启动就行了。重新创建 MSDTC 日志,并重新启动服务的步骤如下：
(1) 单击"开始",单击"运行",输入 cmd 后按"确定"。
(2) 输入:msdtc -resetlog (注意运行此命令时,不要执行挂起的事务)
(3) 最后输入:net start msdtc 回车

参考链接：链接

---------------------------------------------------------------------------------------

如果出现错误：SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset的访问，则需要开启Ad Hoc Distributed Queries组件：

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

 参考链接：链接

---------------------------------------------------------------------------------------

如果两个实例不在同一台电脑上而出现DTC或相关配置的问题，可参考该文章：链接