下面是具体过程 假设两个服务器s1,s2,库表分别为db1,db2,t1,t2.表结构相同(例id(int)主键,name(char)) 1、将s2设为S1 的链接服务器,别名设为zyc1 2、在s2上写存储过程 //插入过程 CREATE PROCEDURE insert_t2 @id_1 int, @name_2 char(10) AS INSERT INTO db2.dbo.t2 ( id, name) VALUES ( @id_1, @name_2) GO //删除过程 CREATE PROCEDURE delete_t2 @id_1 int, AS DELETE db2.dbo.t2 WHERE id = @id_1 GO //更新过程 CREATE PROCEDURE update_t2 @id_1 int, @id_3 int, @name_4 char(10) AS UPDATE db2.dbo.t2 SET id = @id_3,name = @name_4 WHERE id= @id_1 GO 3、在s1的T1写触发器 //添加触发器 CREATE TRIGGER tr1 ON t1 after INSERT AS set xact_abort on declare @id1 int, @name1 char(10) select @id1=id,@name1=name from inserted EXECUTE zyc1.db2.dbo.insert_t2 @id1,@name1//删除触发器 CREATE TRIGGER tr2 on t1 after delete AS set xact_abort on declare @id1 int select @id1=id from deleted EXECUTE zyc1.db2.dbo.delete_t2 @id1//更新触发器 CREATE TRIGGER tr3 ON t1 after update AS set xact_abort on declare @id1 int, @id2 int, @name2 char(10) select @id1=id,@name1=name from deleted select @id2=id,@name2=name from inserted EXECUTE zyc1.db2.dbo.delete_t2 @id1 EXECUTE zyc1.db2.dbo.insert_t2 @id2,@name2这样就可以了!!!不过,我也还有 一个问题
上面这个例子我是试过的,开始时,我没有加set xact_abort on, 结果我往s1中添加数据时,sql server就报错,说xact_abort值为off,就不能执行。我搞不懂为什么,是不是我哪里没有配置好? 还有一个问题,就是,inserted和deleted表中一次有几条记录?如果写一个程序往S1中一次添加n条记录,s2也会同步吗?我上面的代码还起作用吗?希望高手解答!!清白,你可以帮我吗?
现在问题解决了吗?如没解决请再发贴子,我来解决!
假设两个服务器s1,s2,库表分别为db1,db2,t1,t2.表结构相同(例id(int)主键,name(char))
1、将s2设为S1 的链接服务器,别名设为zyc1
2、在s2上写存储过程
//插入过程
CREATE PROCEDURE insert_t2
@id_1 int,
@name_2 char(10) AS INSERT INTO db2.dbo.t2
( id,
name)
VALUES
( @id_1,
@name_2)
GO
//删除过程
CREATE PROCEDURE delete_t2
@id_1 int,
AS DELETE db2.dbo.t2
WHERE
id = @id_1
GO
//更新过程
CREATE PROCEDURE update_t2
@id_1 int,
@id_3 int,
@name_4 char(10)
AS
UPDATE db2.dbo.t2
SET id = @id_3,name = @name_4
WHERE
id= @id_1
GO
3、在s1的T1写触发器
//添加触发器
CREATE TRIGGER tr1 ON t1
after INSERT
AS
set xact_abort on
declare @id1 int, @name1 char(10)
select @id1=id,@name1=name from inserted
EXECUTE zyc1.db2.dbo.insert_t2 @id1,@name1//删除触发器
CREATE TRIGGER tr2 on t1
after delete
AS
set xact_abort on
declare @id1 int
select @id1=id from deleted
EXECUTE zyc1.db2.dbo.delete_t2 @id1//更新触发器
CREATE TRIGGER tr3 ON t1
after update
AS
set xact_abort on
declare @id1 int, @id2 int, @name2 char(10)
select @id1=id,@name1=name from deleted
select @id2=id,@name2=name from inserted
EXECUTE zyc1.db2.dbo.delete_t2 @id1
EXECUTE zyc1.db2.dbo.insert_t2 @id2,@name2这样就可以了!!!不过,我也还有 一个问题
还有一个问题,就是,inserted和deleted表中一次有几条记录?如果写一个程序往S1中一次添加n条记录,s2也会同步吗?我上面的代码还起作用吗?希望高手解答!!清白,你可以帮我吗?