我想做一个触发器,功能是在修改关系SC的成绩值时,要求修改后的成绩一定不能比原来的低,否则拒绝修改。
代码如下:
create trigger sc_update on SC
after update
as
begin
declare @S# char(4)
declare @C# char(4)
declare @SCORE SMALLINT
set @S#=(select S# from inserted)
set @C#=(select C# from inserted)
set @SCORE=(select SCORE from inserted)
WHILE(@SCORE>@SCORE) 这句比较的功能为什么不起作用???
update SC
SET SCORE='@SCORE';
end帮我改改,谢谢
代码如下:
create trigger sc_update on SC
after update
as
begin
declare @S# char(4)
declare @C# char(4)
declare @SCORE SMALLINT
set @S#=(select S# from inserted)
set @C#=(select C# from inserted)
set @SCORE=(select SCORE from inserted)
WHILE(@SCORE>@SCORE) 这句比较的功能为什么不起作用???
update SC
SET SCORE='@SCORE';
end帮我改改,谢谢
after update
as
begin
declare @S# char(4)
declare @C# char(4)
declare @SCORE SMALLINT
set @S#=(select S# from inserted)
set @C#=(select C# from inserted)
set @SCORE=(select SCORE from inserted)
update SC SET SCORE='@SCORE' where SCORE>@SCORE
end
我用
UPDATE SC
SET SCORE=75
WHERE S#=2;
这个语句测试了下,
显示:
将 varchar 值 '@SCORE' 转换为数据类型为 smallint 的列时发生语法错误。
instead of update
as
begin
if exists
( select 1 from inserted i,deleted d where i.PK=D.PK AND I.SCORE<D.SCORE)
BEGIN
PRINT '修改后的值不能小于修改前的值'
end
else
begin
update tb set score=i.score from inserted i where i.pk=tb.pk
end
end
UPDATE SC
SET SCORE=75
WHERE S#=2;
改为
UPDATE SC
SET SCORE=75
WHERE SCORE>某个数;是这个意思吗???
这是唯一符合触发器规则写法的
after update
as
begin
declare @S# char(4)
declare @C# char(4)
declare @SCORE SMALLINT
set @S#=(select S# from inserted)
set @C#=(select C# from inserted)
set @SCORE=(select SCORE from inserted)
update SC SET SCORE=@SCORE where SCORE>@SCORE
end
我用
UPDATE SC
SET SCORE=75
WHERE S#=2;
这个语句测试了下,
显示:
将 varchar 值 '@SCORE' 转换为数据类型为 smallint 的列时发生语法错误。这是为什么???如果修改要怎么改