Excel服务器中的两个明细表A、B完全相同(表的结构完全相同,初始时均为空),且A、B表中有唯一能区分数据记录的两列K1、K2。拟先用A表的触发器同步B表,可是只有在插入的情况下才能成功,其它情况下无论如何都不成功。后来我又分别为INSERT,UPDATE,DELETE建立了触发器,结果还是不行。错误的提示是有多行错误。其它背景简介:在将Excel中的数据存放到SQL Server中的时候采用的是事务处理,一个事物中可能同时存在批量插入、删除、更新操作。我在SQL Server2005中一次只执行一种操作,结果均成功。那么,怎么应付这种同时存在批量混操作的情形?我建的触发器大致代码如下(分开的触发器就不再举例了),请高手指正:CREATE TRIGGER SYNC_B_BY_A
ON A
AFTER INSERT,UPDATE,DELETE
AS
Declare @RowsD Int
Declare @RowsI Int
Select @RowsD=Count(*) From Deleted
Select @RowsI=Count(*) From Inserted
If @RowsD=0 And @RowsI>0 --插入数据
insert into c select * from inserted
Else If @RowsD>0 And @RowsI=0 --删除
DELETE X FROM B X INNER JOIN DELETED D WHERE X.K1=D.K1 AND X.K2=D.K2
Else If @RowsD>0 And @RowsI>0 --更新
BEGIN
DELETE X FROM B X INNER JOIN DELETED D WHERE X.K1=D.K1 AND X.K2=D.K2
INSERT INTO B SELECT * FROM INSERTED
END
ON A
AFTER INSERT,UPDATE,DELETE
AS
Declare @RowsD Int
Declare @RowsI Int
Select @RowsD=Count(*) From Deleted
Select @RowsI=Count(*) From Inserted
If @RowsD=0 And @RowsI>0 --插入数据
insert into c select * from inserted
Else If @RowsD>0 And @RowsI=0 --删除
DELETE X FROM B X INNER JOIN DELETED D WHERE X.K1=D.K1 AND X.K2=D.K2
Else If @RowsD>0 And @RowsI>0 --更新
BEGIN
DELETE X FROM B X INNER JOIN DELETED D WHERE X.K1=D.K1 AND X.K2=D.K2
INSERT INTO B SELECT * FROM INSERTED
END
给你一个例子表A :ID name SEX表B :ID name sex 要求对一个表A进行了增删改后,表B就能增删改,要求就写一个触发器,不能是多个。----- 作者:wufeng4552 set nocount on
if object_id('ta')is not null drop table ta
go
create table ta(ID int ,[Name] varchar(10))
if object_id('tb')is not null drop table tb
go
create table tb(ID int ,[Name] varchar(10),Sex varchar(2))
go
if object_id('tri_Modi')is not null drop trigger tri_Modi go
create trigger tri_Modi on ta
for delete ,insert ,update
as
if not exists(select 1 from deleted)
insert tb(ID,[name])select * from inserted --insert
else if exists(select 1 from inserted)and exists(select 1 from deleted)
update tb set tb.[name]=inserted.[name] from inserted where tb.id=inserted.id --update
else
delete b from tb b ,deleted d where b.id=d.id --del
go
insert ta select 1,'a'
insert ta select 2,'b'
select * from tb
/*
ID Name Sex
----------- ---------- ----
1 a NULL
2 b NULL
*/
update ta set [name]='c' where id=2
select * from tb
/* ID Name Sex ----------- ---------- ---- 1 a NULL 2 c NULL */
delete ta where id=1
select * from tb
/*
ID Name Sex
----------- ---------- ----
2 c NULL
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/js_szy/archive/2009/07/28/4386332.aspx
create trigger tri_Modi on ta
for delete ,insert ,update
as
if not exists(select 1 from deleted)
insert tb(ID,[name])select * from inserted --insert
else if exists(select 1 from inserted)and exists(select 1 from deleted)
update tb set tb.[name]=inserted.[name] from inserted where tb.id=inserted.id --update
else
delete b from tb b ,deleted d where b.id=d.id --delete
go
在执行update时我之所以先删除后插入,是因为我要同步的表中的列非常多,而且这样的表也非常多,不便于一一列出,需要一个通用的方法,哪怕效率低一点。
“多步 OLE DB 操作产生错误。如果可能,请检查每个 OLE DB 状态值。没有工作被完成。”我在网上找过很多关于此类问题的帖子,都不是(例如字段类型、长度等)