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

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