【T-SQL】数据库系统概论【例5.21】

REF:https://blog.csdn.net/weixin_44724691/article/details/105280025

1. 建表

DROP TABLE IF EXISTS SC_U;

create table SC_U
(
Sno char(9) ,
Cno char(9) ,
oldgrade smallint ,
newgrade smallint ,
)

2. 建立触发器 （注意：提前按照教材建立好SC表）

create trigger SC_T
on SC
for update 
as declare @old smallint--声明变量
  declare @new smallint
  declare @Sno char(9)
  declare @Cno char(4)
if(update(Grade))
  begin 
  select @old =Grade from deleted 
  select @new =Grade from inserted
  select @Sno =Sno from deleted------------这里很多同学错写为：SC，结果会错误。 
  select @Cno =Cno from deleted------------同上
  IF(@NEW>=1.1*@OLD)-----------------------更新条件
  insert into SC_U(Sno,Cno,oldgrade,newgrade)
  values(@Sno, @Cno,@old,@new)
end

3. 测试

update SC
set Grade=90
where Sno='201215121' and Cno ='2'

select *from SC
select *from SC_U