![]() |
#2
球球2010-06-24 16:24
|
确保用户在插入或更新数据时所提供的WEIGHT值是非负和非零的,
否则给出错误提示并回滚此操作。请测试该触发器。测试方法自定*/
P表为零件表 其中weight列为零件重量不允许为0或者负数

--create trigger P_Ins
alter trigger P_Ins
on p
for insert,update
as
declare @weight int
select @weight=weight from inserted ------------此句如果在SQL2000中如果运行UPDATE P SET WEIGHT=WEIGHT-15(改所有记录此时某些记录为负应该不允许修改)系统允许修改在SQL2008中则不允许修改
--SET @weight=(SELECT MIN(weight)from inserted) ----------------此句是我为了在SQL2000防止出现以上漏洞做的修改
if(@weight<=[color=#800000; font-weight: bold][color=#800000; font-weight: bold]0[/color][/color])
begin
raiserror('插入出错,weight值小于或等于0',[color=#800000; font-weight: bold][color=#800000; font-weight: bold]16[/color][/color],[color=#800000; font-weight: bold][color=#800000; font-weight: bold]1[/color][/color])
ROLLBACK TRANSACTION
end
go
alter trigger P_Ins
on p
for insert,update
as
declare @weight int
select @weight=weight from inserted ------------此句如果在SQL2000中如果运行UPDATE P SET WEIGHT=WEIGHT-15(改所有记录此时某些记录为负应该不允许修改)系统允许修改在SQL2008中则不允许修改
--SET @weight=(SELECT MIN(weight)from inserted) ----------------此句是我为了在SQL2000防止出现以上漏洞做的修改
if(@weight<=[color=#800000; font-weight: bold][color=#800000; font-weight: bold]0[/color][/color])
begin
raiserror('插入出错,weight值小于或等于0',[color=#800000; font-weight: bold][color=#800000; font-weight: bold]16[/color][/color],[color=#800000; font-weight: bold][color=#800000; font-weight: bold]1[/color][/color])
ROLLBACK TRANSACTION
end
go
/*测试实例*/
/*往P表插入一个零件重量小于0的记录*/
insert into p(PNO,PNAME,COLOR,WEIGHT) values('P7','轮','红',-5);
/*将P标所有记录重量更新为-7*/
update p set weight=-7 --where pno='p6'
/*将P表所有记录重量减15*/
UPDATE P SET WEIGHT=WEIGHT-15
我想知道为什么在SQL2000允许修改 我只有5分了 希望好心人回答下谢谢
[ 本帖最后由 tonlin 于 2010-6-2 23:12 编辑 ]