一个跨表触发器（等整理）

alter trigger tri_getXBDel on 成型台账1
after delete
as
declare @mothsum int,@DEL_ID int,@DEL_date char(20)
select @DEL_ID=ID ,@DEL_date=日期 from deleted
begin
begin
update 修补台账1 set
[1]=(select sum(成型台账1.[1]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[2]=(select sum(成型台账1.[2]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[3]=(select sum(成型台账1.[3]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[4]=(select sum(成型台账1.[4]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[5]=(select sum(成型台账1.[5]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[6]=(select sum(成型台账1.[6]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[7]=(select sum(成型台账1.[7]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[8]=(select sum(成型台账1.[8]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[9]=(select sum(成型台账1.[9]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[10]=(select sum(成型台账1.[10]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[11]=(select sum(成型台账1.[11]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[12]=(select sum(成型台账1.[12]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[13]=(select sum(成型台账1.[13]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[14]=(select sum(成型台账1.[14]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[15]=(select sum(成型台账1.[15]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[16]=(select sum(成型台账1.[16]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[17]=(select sum(成型台账1.[17]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[18]=(select sum(成型台账1.[18]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[19]=(select sum(成型台账1.[19]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[20]=(select sum(成型台账1.[20]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[21]=(select sum(成型台账1.[21]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[22]=(select sum(成型台账1.[22]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[23]=(select sum(成型台账1.[23]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[24]=(select sum(成型台账1.[24]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[25]=(select sum(成型台账1.[25]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[26]=(select sum(成型台账1.[26]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[27]=(select sum(成型台账1.[27]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[28]=(select sum(成型台账1.[28]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[29]=(select sum(成型台账1.[29]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[30]=(select sum(成型台账1.[30]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[31]=(select sum(成型台账1.[31]) from 成型台账1,deleted where 成型台账1.ID=deleted.ID and 成型台账1.日期=deleted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' ))
where 修补台账1.ID=@DEL_ID and 修补台账1.日期=@DEL_date
end
begin
select @mothsum=修补台账1.当月累计 from 修补台账1,deleted where 修补台账1.ID=deleted.ID and 修补台账1.日期=deleted.日期

if(@mothsum is null)
begin

delete from 修补台账1 where 修补台账1.ID=@DEL_ID and 修补台账1.日期=@DEL_date

end
end
end

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER trigger tri_getXB on 成型台账1
for insert,update
as

declare @DEL_ID int,@DEL_date char(20)
select @DEL_ID=ID ,@DEL_date=日期 from inserted
if exists(select * from 修补台账1,inserted where 修补台账1.ID=inserted.ID and 修补台账1.统计方式='收货数'
and 修补台账1.日期=inserted.日期)
begin
update 修补台账1 set
[1]=(select sum(成型台账1.[1]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[2]=(select sum(成型台账1.[2]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[3]=(select sum(成型台账1.[3]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[4]=(select sum(成型台账1.[4]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[5]=(select sum(成型台账1.[5]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[6]=(select sum(成型台账1.[6]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[7]=(select sum(成型台账1.[7]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[8]=(select sum(成型台账1.[8]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[9]=(select sum(成型台账1.[9]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[10]=(select sum(成型台账1.[10]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[11]=(select sum(成型台账1.[11]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[12]=(select sum(成型台账1.[12]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[13]=(select sum(成型台账1.[13]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[14]=(select sum(成型台账1.[14]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[15]=(select sum(成型台账1.[15]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[16]=(select sum(成型台账1.[16]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[17]=(select sum(成型台账1.[17]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[18]=(select sum(成型台账1.[18]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[19]=(select sum(成型台账1.[19]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[20]=(select sum(成型台账1.[20]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[21]=(select sum(成型台账1.[21]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[22]=(select sum(成型台账1.[22]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[23]=(select sum(成型台账1.[23]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[24]=(select sum(成型台账1.[24]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[25]=(select sum(成型台账1.[25]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[26]=(select sum(成型台账1.[26]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[27]=(select sum(成型台账1.[27]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[28]=(select sum(成型台账1.[28]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[29]=(select sum(成型台账1.[29]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[30]=(select sum(成型台账1.[30]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' )),
[31]=(select sum(成型台账1.[31]) from 成型台账1,inserted where 成型台账1.ID=inserted.ID and 成型台账1.日期=inserted.日期
and (成型台账1.统计方式='良品' or 成型台账1.统计方式='二等品' ))
where 修补台账1.ID=@DEL_ID and 修补台账1.日期=@DEL_date
end
else
begin
insert into 修补台账1(ID,日期,统计方式,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],
[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
select ID,日期,'收货数',[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],
[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]
from inserted

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO