触发器影响多行时无效

触发器影响多行时无效 [无满意答案结贴]

zzyyc
西风瘦马
等级:

发表于：2008-05-20 09:31:11 楼主

SQL的触发器是表级触发器，DML影响一行或无数行触发动作只触发一次。
触发器定义：
CREATE TRIGGER Barn_Branch_IU
ON dbo.Barn_Branch
FOR INSERT
AS
begin
begin tran
DECLARE @find integer
DECLARE @rowcount integer
set @rowcount=(select count(*) from inserted)
if @rowcount=1
begin
set @find=(select count(*) from Barn_Branch where rtrim(ltrim(ProBarcode))=(select cast(ProBarcode as varchar(50)) as ProBarcode from inserted) AND ltrim(rtrim(pihao))=(select cast(pihao as varchar(50)) as pihao from inserted) AND ltrim(rtrim(BranchCode))=(select cast(BranchCode as varchar(50)) as BranchCode from inserted))
print @find
if @find>1
begin
delete Barn_Branch where id in (select id from inserted)
UPDATE Barn_Branch SET Sum_Num=Sum_Num+(select Sum_Num from inserted),Pei_Price=(select Pei_Price from inserted) WHERE ProBarcode=(select cast(ProBarcode as varchar(50)) as ProBarcode from inserted) AND rtrim(pihao)=(select cast(pihao as varchar(50)) as pihao from inserted) AND rtrim(BranchCode)=(select cast(BranchCode as varchar(50)) as BranchCode from inserted)
end
commit tran
end
else
commit tran
end
return

作用就是判断是否有此批号的商品，如果有则直接增加其数量，没有则新增一条记录。
问题是现在有一批量插入语句:
INSERT INTO Barn_Branch(......)select ....... from tab
执行时触发器无效。查帮助上面讲：在写触发器代码时需要考虑的一个重要问题就是，引发触发器的语句可以是一个影响多行的单一语句，而不仅是影响一行。这在 UPDATE 和 DELETE 触发器中很常见，因为这些语句经常作用于多行。而这在 INSERT 触发器中就比较少见，因为基本的 INSERT 语句只添加一行。然而，由于 INSERT 触发器可由 INSERT INTO (table_name) SELECT 语句激发，所以插入许多行可能导致单个的触发器调用。
求一解决方案。

问题点数：20 回复次数：9 修改删除举报引用回复

--try: CREATE TRIGGER Barn_Branch_IU ON dbo.Barn_Branch instead of INSERT,update AS begin delete Barn_Branch from Barn_Branch a,deleted b where rtrim(ltrim(a.ProBarcode))=b.ProBarcode AND ltrim(rtrim(a.pihao))=b.piao AND ltrim(rtrim(a.BranchCode))=b.BranchCode insert into Barn_Branch select * from inserted end

--这样？ CREATE TRIGGER Barn_Branch_IU ON dbo.Barn_Branch instead of INSERT,update AS begin if exists(select 1 from Barn_Branch a where exists(select 1 from inserted where rtrim(ltrim(a.ProBarcode))=ProBarcode AND ltrim(rtrim(a.pihao))=piao AND ltrim(rtrim(a.BranchCode))=BranchCode)) begin update Barn_Branch set Sum_Num=Sum_Num+b.Sum_Num,Pei_Price=Pei_Price+b.Pei_Price from Barn_Branch a,inserted b where rtrim(ltrim(a.ProBarcode))=b.ProBarcode AND ltrim(rtrim(a.pihao))=b.piao AND ltrim(rtrim(a.BranchCode))=b.BranchCode end else insert into Barn_Branch select * from inserted end