如果一定要这样做,那就写一个统计程序插入到后面四个表好了. create trigger 统计 on 交易记录表 after insert as begin insert into 日报表(时间,金额) select convert(varchar(10),交易时间,120),sum(商品单价*数量) from 交易记录表 group by convert(varchar(10),交易时间,120) insert into 月报表(时间,金额) select convert(varchar(7),交易时间,120),sum(商品单价*数量) from 交易记录表 group by convert(varchar(7),交易时间,120) insert into 年报表(时间,金额) select convert(varchar(7),交易时间,120),sum(商品单价*数量) from 交易记录表 group by convert(varchar(4),交易时间,120) --季度自己考虑 end
create trigger tr_交易记录表 on 交易记录表 after insert as begin set nocount on
merge 日报表 as o using inserted as s on cast(o.时间 as date)=cast(s.交易时间 as date) when matched then update set o.销售总额=o.销售总额+s.总量 when not matched then insert(时间,销售总额) values(cast(s.交易时间 as date),s.总量);
merge 月报表 as o using inserted as s on left(convert(varchar,o.时间,111),7)=left(convert(varchar,s.交易时间,111),7) when matched then update set o.销售总额=o.销售总额+s.总量 when not matched then insert(时间,销售总额) values(left(convert(varchar,s.交易时间,111),7),s.总量);
merge 年报表 as o using inserted as s on left(convert(varchar,o.时间,111),4)=left(convert(varchar,s.交易时间,111),4) when matched then update set o.销售总额=o.销售总额+s.总量 when not matched then insert(时间,销售总额) values(left(convert(varchar,s.交易时间,111),4),s.总量); merge 季度报表 as o using inserted as s on left(convert(varchar,o.时间,111),5)=left(convert(varchar,s.交易时间,111),5) and substring(o.时间,6,1)=cast((month(s.交易时间)-1)/3+1 as varchar) when matched then update set o.销售总额=o.销售总额+s.总量 when not matched then insert(时间,销售总额) values(left(convert(varchar,s.交易时间,111),5)+cast((month(s.交易时间)-1)/3+1 as varchar),s.总量); end
create trigger 统计
on 交易记录表
after insert
as
begin
insert into 日报表(时间,金额) select convert(varchar(10),交易时间,120),sum(商品单价*数量) from 交易记录表 group by convert(varchar(10),交易时间,120)
insert into 月报表(时间,金额) select convert(varchar(7),交易时间,120),sum(商品单价*数量) from 交易记录表 group by convert(varchar(7),交易时间,120)
insert into 年报表(时间,金额) select convert(varchar(7),交易时间,120),sum(商品单价*数量) from 交易记录表 group by convert(varchar(4),交易时间,120)
--季度自己考虑
end
create trigger tr_交易记录表
on 交易记录表 after insert
as
begin
set nocount on
merge 日报表 as o
using inserted as s
on cast(o.时间 as date)=cast(s.交易时间 as date)
when matched then
update set o.销售总额=o.销售总额+s.总量
when not matched then
insert(时间,销售总额) values(cast(s.交易时间 as date),s.总量);
merge 月报表 as o
using inserted as s
on left(convert(varchar,o.时间,111),7)=left(convert(varchar,s.交易时间,111),7)
when matched then
update set o.销售总额=o.销售总额+s.总量
when not matched then
insert(时间,销售总额) values(left(convert(varchar,s.交易时间,111),7),s.总量);
merge 年报表 as o
using inserted as s
on left(convert(varchar,o.时间,111),4)=left(convert(varchar,s.交易时间,111),4)
when matched then
update set o.销售总额=o.销售总额+s.总量
when not matched then
insert(时间,销售总额) values(left(convert(varchar,s.交易时间,111),4),s.总量); merge 季度报表 as o
using inserted as s
on left(convert(varchar,o.时间,111),5)=left(convert(varchar,s.交易时间,111),5)
and substring(o.时间,6,1)=cast((month(s.交易时间)-1)/3+1 as varchar)
when matched then
update set o.销售总额=o.销售总额+s.总量
when not matched then
insert(时间,销售总额) values(left(convert(varchar,s.交易时间,111),5)+cast((month(s.交易时间)-1)/3+1 as varchar),s.总量);
end