open delpoint fetch next from delpoint into @point while @@fetch_status=0 begin
update user_point set youxiao=youxiao-@point,shixiao=shixiao+@point where userid=@uid fetch next from delpoint into @point end close delpoint deallocate delpoint
alter trigger checkNoPoint on oblog_user for update as if update(lastlogintime) begin declare @uid int declare @total int declare @point int select @uid=userid from deleted if(select count([id]) from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2)>0 begin
declare delpoint cursor for select point from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2 open delpoint fetch next from delpoint into @point while @@fetch_status=0 begin update user_point set youxiao=youxiao-@point,shixiao=shixiao+@point where userid=@uid fetch next from delpoint into @point end close delpoint deallocate delpoint
delete from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2 end end
create trigger checkNoPoint on oblog_user for update as if update(lastlogintime) begin declare @uid int declare @total int declare @point int select @uid=userid from deleted if(select count([id]) from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2)>0 begin
select @total=sum(point) from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2 update user_point set youxiao=youxiao-@total,shixiao=shixiao-@total where userid=@uid
delete from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2 end end 我换成这种实现方式,是不是更好一些呢
create trigger checkNoPoint on oblog_user for update as if update(lastlogintime) begin if exists (select 0 from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2)>0) begin declare @uid int,@total int,@point int select @uid=userid from deleted select @total=sum(point) from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2 update user_point set youxiao=youxiao-@total,shixiao=shixiao-@total where userid=@uid delete from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2 end end
fetch next from delpoint @point --增加变量 while @@fetch_status=0 begin set @point=point update user_point set youxiao=youxiao-@point,shixiao=shixiao+@point where userid=@uid fetch next from delpoint @point --增加此句,否则就死循环 end close delpoint deallocate delpoint
应该是这句错了
fetch next from delpoint into @point
fetch next from delpoint into @point
while @@fetch_status=0
begin
update user_point set youxiao=youxiao-@point,shixiao=shixiao+@point where userid=@uid
fetch next from delpoint into @point
end
close delpoint
deallocate delpoint
on
oblog_user
for
update
as
if update(lastlogintime)
begin
declare @uid int
declare @total int
declare @point int
select @uid=userid from deleted
if(select count([id]) from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2)>0
begin
declare delpoint cursor
for
select point from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2 open delpoint
fetch next from delpoint into @point
while @@fetch_status=0
begin
update user_point set youxiao=youxiao-@point,shixiao=shixiao+@point where userid=@uid
fetch next from delpoint into @point
end
close delpoint
deallocate delpoint
delete from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2
end
end
create trigger checkNoPoint
on
oblog_user
for
update
as
if update(lastlogintime)
begin
declare @uid int
declare @total int
declare @point int
select @uid=userid from deleted
if(select count([id]) from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2)>0
begin
select @total=sum(point) from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2 update user_point set youxiao=youxiao-@total,shixiao=shixiao-@total where userid=@uid
delete from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2
end
end
我换成这种实现方式,是不是更好一些呢
if update(lastlogintime)
begin
if exists (select 0 from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2)>0)
begin
declare @uid int,@total int,@point int
select @uid=userid from deleted
select @total=sum(point) from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2
update user_point set youxiao=youxiao-@total,shixiao=shixiao-@total where userid=@uid
delete from user_GetPoint where userid=@uid and datediff(yyyy,addtime,getdate())>2
end
end
while @@fetch_status=0
begin
set @point=point
update user_point set youxiao=youxiao-@point,shixiao=shixiao+@point where userid=@uid
fetch next from delpoint @point --增加此句,否则就死循环 end
close delpoint
deallocate delpoint