求一个存储过程“订单金额大于500，下订单会员自动升级VIP会员”！

[coldermice]

数据库有一个订单表，字段sumprice是金额，当金额大于500元时，下订单日期15日后就升级会员为VIP。字段username为会员。请问这样的存储过程要怎么写？怎么去触发它呢？
能给出具体代码的给全部分。

[simonhehe]

--建立用户表
--username是主键,金额,和成为会员的日期
CREATE TABLE tbOrder(username Varchar(100) PRIMARY KEY,sumprice Money,IsLeaguer Datetime)
go


--建立触发器
--名称:tr_tbOrder
--功能:当有用户的金额>=500时,自动确定他何时会成为会员,存放到IsLeaguer中
CREATE TRIGGER tr_tbOrder
ON tbOrder
FOR INSERT, UPDATE
AS
If UPDATE(sumprice)
BEGIN
declare @sStr Nvarchar(100),@dDate datetime
select @dDate = dateadd(day,15,getdate())
select @sStr = username from inserted

exec('update tbOrder set IsLeaguer = ''' + @dDate + ''' where username = ''' + @sStr + ''' and sumprice >= 500')
END
go

--放入测试数据
insert into tbOrder(username,sumprice) select 'aa',500
insert into tbOrder(username,sumprice) select 'bb',600
insert into tbOrder(username,sumprice) select 'cc',400
go

--演示,IsLeaguer字段的日期<=当前时间的就是已经成为会员的
select * from tbOrder where IsLeaguer <= getdate()
go


--drop table tbOrder
--drop trigger tr_tbOrder

[simonhehe]

--建立用户表
--username是主键,金额,和成为会员的日期
CREATE TABLE tbOrder(username Varchar(100) PRIMARY KEY,sumprice Money,IsLeaguer Datetime)
go


--建立触发器
--名称:tr_tbOrder
--功能:当有用户的金额>=500时,自动确定他何时会成为会员,存放到IsLeaguer中
CREATE TRIGGER tr_tbOrder
ON tbOrder
FOR INSERT, UPDATE
AS
If UPDATE(sumprice)
BEGIN
declare @sStr Nvarchar(100),@dDate datetime
select @dDate = dateadd(day,15,getdate())
select @sStr = username from inserted
--更新:通过匹配用户名,金额和是否已经成为会员来确定更新
exec('update tbOrder set IsLeaguer = ''' + @dDate + ''' where username = ''' + @sStr + ''' and sumprice >= 500 and IsLeaguer is NULL')
END
go

--放入测试数据
insert into tbOrder(username,sumprice) select 'aa',500
insert into tbOrder(username,sumprice) select 'bb',600
insert into tbOrder(username,sumprice) select 'cc',400
go

--演示,IsLeaguer字段的日期<=当前时间的就是已经成为会员的
select * from tbOrder where IsLeaguer <= getdate()
go


--drop table tbOrder
--drop trigger tr_tbOrder

[hellowork]

楼上:
触发器只有在插入或更新的时候才会检查下订单超过15天的用户,如果半年之内都没有对表进行插入或更新,那么之前的那些用户就无法变为VIP,而这些用户下订单的天数已远远超出15天了.
正确的方法应该是建立作业,只有作业才是自动的或主动的,而触发器是被动的.

[simonhehe]

:-)
楼上:
在插入或更新一条记录的时候,如果他符合成为VIP的条件,就立即确立了其VIP的地位(即:从何时起该记录成为VIP!),
所以不存在你说的情况.
--注,IsLeaguer 存放的是一个日期值

[hellowork]

仔细看了楼上设计的触发器,思路很好,在插入的时候就确定了成为VIP的日期.只是不能在vip日期到来那天自动更新VIP字段值为1,需要查询来获得VIP用户.
总的说来,象楼主这种需求还是使用JOB比较好,因为JOB相当于实时的主动的去检测.
：）

[simonhehe]

:-)
开始也想到JOB,不过考虑后觉得触发器更精确,JOB存在执行计划问题,也许一个用一分钟后就是VIP了,而这种问题JOB不应该成为秒级执行的,但查询语句就可以随时获得

[coldermice]

非常感觉大家的意见，是2个表的。一个订单表 一个会员表，当下的订单>=500时，15日后会员表自动升级为VIP。也就是订单表的sumprice>=500，datetime<=getdate()-15时，会员表的ISVIP='1'.
我只需要每天执行一次就可以了。所以用定时自动执行会比较好。觉得JOB比较好。
请问JOB是存储过程吗？应该在哪里建立？怎么去设定定时运行呢？
请大家指点。。

[simonhehe]

打开一个sql实例->管理->代理->作业
一看就知道怎么做了

[coldermice]

如果条件符合的话，需要在m_record表记录下会员名，升级的时间等信息，应该怎么写呢？

[babay2008]

mark

[hellowork]

UPDATE a SET isVIP = 1,isvip_date=getdate(),isvip_date_s=getdate()+12*30
FROM custom as a INNER JOIN orderlist as b
on a.userid = b.userid
WHERE b.state=9 and
(select sum(sumprice) from orderlist where userid = b.userid and
replace(订单号,'-c','') = replace(b.订单号,'-c','')) >= 500
and b.datetime <=getdate()-15
and a.isVIP = 0

[being21]

呵呵，把我的帐号变成VIP吧！！！

[sp4]

用作业

[zdzdzdconan]

首先它升级vip会员需要买东西吧？那么下订单的时候把所花金额放在用户表"总消费"字段。比如user表，cost_all字段。然后下订单日期存入buy_time字段。

不管几天用户变成VIP那总是要他自己登陆才能发现对吧？

那么在用户登陆的时候加个判断：
if rs_user(总消费)>500 & rs_user(最后购买时间)-now()>15天 then
你变成VIP了，yeah!
else
到你写的普通用户的判断
end if

当然条件也能改一下，比如你还有<%rs_user(最后购买时间)-now()%>天变成vip

[zdzdzdconan]

对了，楼主，以前我们老板也提过类似这么一个非礼的问题。苦想了四天才解决……

反正当时我就是这么用的。剩下的就你自己发挥吧。

[simonhehe]

强烈建议楼主，学习、学习、再学习

[songyunxing]

学习

[coldermice]

我已经知道怎么建JOB了。现在还有一个问题，就是一个订单500元，可能因为某些商品无货而被拆分成2个订单，新拆的订单会在原订单号后面加个－C。这样等于就分成2个不到500元的订单了。这时就需要汇总2个订单的金额，大于500元的就处理升级为VIP。现在就是不知道应该怎么写sql语句去汇总金额。订单号字段是ordernum、
下面是我写的没有汇中拆分订单金额的SQL语句，大家看看能不能做到汇总。
UPDATE a SET isVIP = 1,isvip_date=getdate(),isvip_date_s=getdate()+12*30
FROM custom as a INNER JOIN orderlist as b
on a.userid = b.userid
WHERE b.state=9 and b.sumprice >= 500 and b.datetime <=getdate()-15
and a.isVIP = 0

[laowang2]

作业调度

[coldermice]

真是太感谢各位了，特别是一两清风兄。对我帮助很大。现在结贴。