为什么检索deleted的值是前一次的值呢?急切求助啊!!!
有如下触发器!其目的是有中英2个版本的数据库,中文版将某用户的cecheck置为1或者checkvip置为1时,就将这条数据插入到英文版数据库里面去,SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER [插入更改中文版用户] ON [dbo].[userinfo]
FOR update
AS
declare @Auditing bit;
declare @LoginName varchar(20)
select @Auditing=cocheck,@LoginName=uname from deleted
if update(cocheck)
begin
print '@Auditing='+cast(@Auditing as varchar);
--情况1.当中文版审核认证某用户之后,我们需要将他插入到英文版中
if @Auditing=1 AND NOT EXISTS(select * from enn.dbo.userinfo where loginname=@LoginName)
begin
insert into enn.dbo.UserInfo
( [LoginName],
[Password],
[L_Name],
[L_Sex],
[L_Position],
[L_Mobile],
[Email],
[Phone],
[Zipcode],
[Country],
[Fax],
[Address],
[VipBeginDate],
[VipEndDate],
[Company],
[Comment],
[Products],
[Materials],
[Website],
[Logo],
[RegDate],
[LastLogin],
[LoginCount],
[RegLocation],
[IsVip],
[Interpret],
[BegUpgrade],
[GlassCnID]
)
select uname,
upass,
contact,
sex,
enuseroffice,
mobile,
email,
phone,
zipcode,
'china',
fax,
address,
vipbegindate,
vipenddate,
coname,
memo,
prodsell,
eMaterials,
url,
logo,
urtime,
lastonline,
loginnum,
0,
checkvip,
1,
0,
[id]
from userinfo where uname=@LoginName
print '插入了:'+@LoginName
end
--情况2.当中文版将某用户从审核状态置为非审核状态,那么我们需要将英文版里面的该用户删除掉
else if @Auditing=0 AND EXISTS(select * from enn.dbo.userinfo where loginname=@LoginName)
begin
DELETE enn.dbo.userinfo where loginname=@LoginName
end
end
--情况3.当中文版将更改某个用户的类型
if update(checkvip)
begin
declare @IsVip bit;
select @IsVip=checkvip from deleted
print '@IsVip='+cast(@IsVip as varchar);
--如果中文版某个用户被升级成为VIP但是却没有在英文版中找到他,那么我们要将他他插入英文版,并且还要是置为VIP
if @IsVip=1 AND NOT EXISTS(select * from enn.dbo.userinfo where loginname=@LoginName)
begin
insert into enn.dbo.UserInfo
( [LoginName],
[Password],
[L_Name],
[L_Sex],
[L_Position],
[L_Mobile],
[Email],
[Phone],
[Zipcode],
[Country],
[Fax],
[Address],
[VipBeginDate],
[VipEndDate],
[Company],
[Comment],
[Products],
[Materials],
[Website],
[Logo],
[RegDate],
[LastLogin],
[LoginCount],
[RegLocation],
[IsVip],
[Interpret],
[BegUpgrade],
[GlassCnID]
)
select uname,
upass,
contact,
sex,
enuseroffice,
mobile,
email,
phone,
zipcode,
'china',
fax,
address,
vipbegindate,
vipenddate,
coname,
memo,
prodsell,
eMaterials,
url,
logo,
urtime,
lastonline,
loginnum,
0,
1,
1,
0,
[id]
from userinfo where uname=@LoginName
end
--否则我们只需把英文版里面的vip状态改为和中文版一样就可以了
else
begin
UPDATE enn.dbo.userinfo set IsVip=@IsVip WHERE LoginName=@LoginName
end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
问题:
update userinfo set checkvip=1
WHERE (ID = 7897)
当我执行如上语句的时候却显示提示信息如下
(所影响的行数为 1 行)
@IsVip=0----------------------------[为什么是0呢?]
....
按照我的想象上面的@IsVip=0应该是@IsVip=1啊,为什么当我再次执行这个update语句的时候才显示的@IsVip=1这样的错误直接就导致了我程序错误
!!急啊