22,209
社区成员
发帖
与我相关
我的任务
分享
create trigger AddUserPoint
on [OrderStatus]
for insert
as
begin
update u
set [Point] = [Point] + isnull(f,0)
from [user] u ,(
select i.[OrderID],[UserID],sum( [Point]*[Quantity]) as f
from inserted i,[Order] o,[OrderDetail] od,[Product] p
where i.[OrderID] = o.[OrderID] and o.[OrderID] = od.[OrderID] and od.[ProID] = p.[ProID]
group by i.[OrderID],[UserID])g
where u.[UserID] = g.[UserID]
end
create trigger tr_订单状态表_insert
on 订单状态表
for insert
as
update 用户表 set
totalpoint=用户表.totalpoint+t.point
from 用户表 inner join (
select
订单表.用户ID
,sum(产品表.point*订单明细表.产品数量) as point
from inserted i
inner join 订单表
on i.订单ID = 订单表.订单ID
inner join 订单明细表
on 订单明细表.订单明细ID=订单表.订单明细ID
inner join 产品表
on 产品表.产品ID=订单明细表.产品ID
where i.订单状态 = 4
group by 订单表.用户ID
) as t
on 用户表.用户ID = t.用户ID
go
create trigger tr_i
on 订单状态表
for insert
as
begin
update e
set totalpoint = totalpoint + isnull(f,0)
from 用户表 e ,(
select i.订单ID,用户,sum( 产品积分*产品数量) as f
from inserted i,订单表 a,订单明细表 b,产品表 c
where i.订单ID = a.订单ID and a.订单ID = b.订单ID and b.产品ID = c.产品ID
group by i.订单ID,用户)g
where e.用户 = g.用户
end
go