存储过程、触发器和用户自定义函数实验2、触发器

（1）为study表创建一个UPDATE触发器，当更新成绩时，要求更新后的成绩不能低于原来的成绩。

CREATE TRIGGER UPDATE_SCORE ON study
instead of update
as
declare @sno2 char(5),@cno2 char(4),@score1 smallint,@score2 smallint
select @sno2=sno,@cno2=cno,@score2=score
from inserted
select @score1=score
from deleted
if(@score2>=@score1)
update study set score=@score2
where study.cno=@cno2 and study.sno=@sno2
go

（2）为study表创建一个DELETE触发器，要求一次只能从study表中删除一条记录。

CREATE TRIGGER DEL_STUDY ON study
instead of DELETE
AS
begin
declare @num int,@sno char(5),@cno char(4)
select @num=COUNT(*)from deleted
if @num=1
begin
select @sno=sno,@cno=cno from deleted
delete from study where @sno=study.sno and @cno=study.cno
end
else print '一次不能删除多条记录'
end

（3）为course表创建一个INSERT触发器，要求插入的课程记录中任课教师不能为空。

 CREATE TRIGGER INSERT_COR ON course
instead of insert
AS
declare @cno char(4),@cname char(20),@teacher char(8)
select @cno=cno,@cname=cname,@teacher=teacher from inserted if(@teacher is null)
print '注意：任课教师不能为空！'
else
insert course values(@cno,@cname,@teacher)