34,590
社区成员
发帖
与我相关
我的任务
分享
create trigger trig_insert_tb_sales on tb_sales
for insert as
if exists(select 1 from inserted a,tb_rpDay b where a.sellpos=b.sellpos and a.sellcashier=b.sellcashier and datediff(dd,a.[date],b.[date])=0)
update a set price=isnull(a.price,0)+isnull(b.price,0) from tb_rpDay a,inserted b where a.sellpos=b.sellpos and a.sellcashier=b.sellcashier and datediff(dd,a.[date],b.[date])=0
else
insert tb_rpDay(sellpos,sellcashier,[date],price) select sellpos,sellcashier,[date],price from inserted
create trogger tr_tb_sales
on tb_sales
for insert,delete,update
as
select sellpos,sellcashier,[date]
,sum(price) as price
into #temp
from (
select sellpos,sellcashier,cast(convert(varchar(10,[date],120) as datetime) as [date]
,price
from inserted
union all
select sellpos,sellcashier,cast(convert(varchar(10,[date],120) as datetime) as [date]
,-price
from deleted
) as t
update r set
price = isnull(r.price,0) + isnull(t.price,0)
from tb_rpDay r,#temp t
where r.sellpos= t.sellpos
and r.sellcashier= t.sellcashier
and r.[date]=t.[date]
insert tb_rpDay (sellpos,sellcashier,[date]
,price
)
select sellpos,sellcashier,[date]
,price
from #temp t
where not exists (
select 1
from tb_rpDay r
where r.sellpos= t.sellpos
and r.sellcashier= t.sellcashier
and r.[date]=t.[date]
)
go