触发器问题，高分求助

[kingshine]

我写DELETE触发器，但如果删除多条只对第一条起作用，各位帮忙看一下。

CREATE TRIGGER [dbo].[deltb] on [dbo].[AdjustTran]
for delete
AS
BEGIN
declare @trantype int
declare @MchntID char(18)
declare @Amount money
SELECT @MchntID=NtMchntID,@trantype=trantype FROM deleted
If @trantype=0 or @trantype=1 or @trantype=4 or @trantype=6
Begin
if @trantype=0
begin
set @trantype=1
end
select d.NtMchntID,sum(d.FactAmount*d.ComputeRate) as Amount into #Temp from deleted d
group by d.NtMchntID

select @Amount=Amount from #Temp where NtMchntID=@MchntID
update mchntaccount set Type=@trantype,Amount=Amount-@Amount where MchntID=@MchntID
end
END

[luoyoumou]

-- 要用 for each row （行触发器)

--

[luoyoumou]

-- 哦：仔细一看：因为你这里分组了（ group by d.NtMchntID )

-- 所以更新的时候就不能这样啦！

[luoyoumou]

-- 这样的话，你select @Amount=Amount from #Temp where NtMchntID=@MchntID
这个语句，@Amount就只能取到最后一行数据!

[迷途的书童]

看看：

CREATE TRIGGER [dbo].[deltb] on [dbo].[AdjustTran]
  for delete   
AS  
BEGIN
declare @trantype int
declare @MchntID char(18)
declare @Amount money
SELECT @MchntID=NtMchntID,@trantype=trantype FROM deleted
If	@trantype=0 or @trantype=1	or	@trantype=4 or @trantype=6
Begin
if @trantype=0  
begin
set @trantype=1  
end
select d.NtMchntID,sum(d.FactAmount*d.ComputeRate) as Amount into #Temp from deleted d
group by d.NtMchntID  

select @Amount=Amount from #Temp where NtMchntID=@MchntID  
update mchntaccount set Type=@trantype,Amount=Amount-@Amount where MchntID=@MchntID
end
END

[迷途的书童]

应该是每删除的时候就调用触发器， 不管是当删一条还是多条，

SQL 只执行一次 删除。 也就只启动一次触发@

[xman_78tom]

-- 这个意思？？？ 最好将想要的结果描述一下。
CREATE TRIGGER [dbo].[deltb] on [dbo].[AdjustTran]
FOR DELETE
AS
 update m 
 set 
 Type=(select top 1 (case trantype when 0 then 1 else trantype)
   from deleted where NtMchntID=t.NtMchntID and trantype in (0,1,4,6)),
  Amount=Amount-t.Amount
 from
  (select NtMchntID,sum(FactAmount*ComputeRate) as Amount
  from deleted where trantype in (0,1,4,6) group by d.NtMchntID ) t,
  mchntaccount m
 where t.NtMchntID=m.MchntID;
GO

[永生天地]

用游标，实现逐行触发

[永生天地]

CREATE TRIGGER [dbo].[deltb] on [dbo].[AdjustTran]
  for delete   
AS  
BEGIN
	declare @trantype int
	declare @MchntID char(18)
	declare @Amount money
	
	declare cur cursor for SELECT NtMchntID,trantype FROM deleted
	open cur

	fetch next from cur into @MchntID,@trantype
	while @@fetch_status=0
	begin
		If @trantype=0 or @trantype=1 or @trantype=4 or @trantype=6
		Begin
			if @trantype=0  
			begin
				set @trantype=1  
			end
			select d.NtMchntID,sum(d.FactAmount*d.ComputeRate) as Amount 
			into #Temp 
			from deleted d
			group by d.NtMchntID  
			
			select @Amount=Amount from #Temp where NtMchntID=@MchntID  
			update mchntaccount set Type=@trantype,Amount=Amount-@Amount where MchntID=@MchntID
		end
		fetch next from cur into @MchntID,@trantype
	end
END

[中国风]

如果同一個NtMchntID,也只會存在trantype一個刪除時。
可用

CREATE TRIGGER [dbo].[deltb] on [dbo].[AdjustTran]
for delete  
AS 
begin
	UPDATE b
	SET Amount=a.Amount,TYPE=CASE WHEN a.trantype=0 THEN 1 ELSE a.trantype end
	FROM (SELECT NtMchntID AS MchntID,trantype,SUM(d.FactAmount*d.ComputeRate) AS Amount FROM DELETED d 	WHERE trantype IN(0,1,4,6) GROUP BY NtMchntID,trantype)a
		INNER JOIN mchntaccount b ON a.MchntID=b.MchntID
END

[中国风]

[Quote=引用 8 楼 xys_777 的回复:]

SQL code
CREATE TRIGGER [dbo].[deltb] on [dbo].[AdjustTran]
for delete
AS
BEGIN
declare @trantype int
declare @MchntID char(18)
declare @Amount money

declare cur cursor f……
[/Quote]
樓上兄弟，漏了臨時表 #Temp 的刪除操作，游標熱循環時，在into前加上 if object_id('Tempdb..#Temp') is not null drop table #Temp