我刚才看帮助中说,如果对插入删除多行的情况,触发器编起来就很麻烦,而且使用游标不好,最好用@@rowcount... ,但是,我下面有个例子现在只能插入或删除单条记录才没有问题,怎么应用@@rowcount写代码实现多行插入删除呢??有没有人知道?也帮帮忙!先谢过了!
代码:两个服务器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怎么改?请大家帮忙啊!!!我急,希望高手指点!
代码:两个服务器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怎么改?请大家帮忙啊!!!我急,希望高手指点!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货