文档介绍：数据库
在pay表中增加pay_score int,默认值为0,允许为空。存放积分抵现金的积分数。目前没用到。
在pay表增加触发器pay_score,当订单从不是已配送的状态变成已配送,则将本单的金额数增加到积分中,分别在user_score和total_score加上积分。
Create trigger [pay_score]
on [dbo].[pay] for update
as
if update(pay_zk)
begin
update user_a
set user_score=user_score+,total_score=total_score+
from user_a U join deleted D on = join inserted I
on =
where (<>'已配送' and <>'已签收')
and (='已配送' or ='已签收')
end
Pay_shop无修改。
Shop表中增加LP_score字段int。默认值为0。将表中现有的全清为0
User_a表中增加user_score int,total_score int。默认值都是1000。User_VIP默认值为铁牌会员。
增加触发器 total_score_add,解决当total_score发生变化时,total_score会自动会员升级。
Create trigger [total_score_add]
on [dbo].[user_a] for update
as
if Update(total_score)
begin
update user_a
set =(case
when (<1000
and in ('否','铁牌会员','铜牌会员','银牌会员','金牌会员'))
then '否'
when (>=1000 and <100000
and in ('否','铁牌会员','铜牌会员','银牌会员','金牌会员'))
then '铁牌会员'
when (>=100000 and <200000
and in ('否','铁牌会员','铜牌会员','银牌会员','金牌会员'))
then '铜牌会员'
when (>=200000 and <300000
and in ('否','铁牌会员','铜牌会员','银牌会员','金牌会员'))
then '银牌会员'
when (