SQL Server数据库——触发器的创建

潇小白本学期刚学期数据库，下面分享一下我的成果，欢迎大家来讨论哦！
小编的数据库就是教材附带的数据库哦，也是在此基础上进行操作的。

--第一题，（1）限制学生的年龄在15~45之间。(用两种触发器做，并比较结果，截图)
create trigger trigger_sage on student
after insert,update
as
if exists (select * from inserted where sage not between 15 and 45)
begin
print '插入学生年龄不在15~45岁之间!'
rollback
end

drop trigger trigger_sage

insert into student values(128,'潇小白','男',12,'软件工程系')
--------------
create trigger trigger_sage1 on student
instead of insert,update
as
if not exists (select * from inserted where sage between 15 and 45) 
begin
print '插入学生年龄不在15~45岁之间!'
end
else
insert into student select * from inserted

drop trigger trigger_sage1

insert into student values(128,'小白','女',13,'软件工程系')

--第二题，（2）限制学生所在系的取值范围为{计算机系，软件工程系，物联网系}
create trigger trigger_sdept on student
after insert,update
as
if not exists (select * from inserted where sdept in('计算机系','软件工程系','物联网系'))
begin
print '学生所在系的取值范围不在计算机系，软件工程系，物联网系内!'
rollback
end

drop trigger trigger_sdept

insert into student values(2,'潇小白','男',21,'软件系')

--第三题，（3）限制每个学期开设的课程总学分在20~30范围内。
create trigger trigger_ccredit on course
after insert,update
as
if exists (select * from course where semster in(select semster from inserted) having sum(ccredit) not between 20 and 30)
begin
print '每个学期开设的课程总学分不在20~30范围内!'
rollback
end

drop trigger trigger_ccredit

insert into course values('C100','概率论',3,9)
insert into course values('C101','概率论',20,9)

--第四题，（4）限制每个学生每学期选课门数不能超过6门（设只针对插入操作）。
create trigger trigger_sc on sc
after insert
as
if exists (select count(cno) from sc where sno in(select sno from inserted)  having count(sc.cno)>6)
begin
print '每个学生每学期选课门数不能超过6门!'
rollback
end

drop trigger trigger_sc

insert into sc values('0611102','C101',72)

--第五题，（5）当更改学生表中的学号时，选课表中的学号也作相应修改。
create trigger trigger_student on student
after update
as
begin
declare @newsno char(8)
declare @oldsno char(8)
select @newsno=sno from inserted
select @oldsno=sno from deleted
update sc set sno=@newsno where sno=@oldsno
end

drop trigger trigger_student

update student set sno='0621104' where sno='0621103'
select * from sc

--第六题，（6）当删除学生表中某个学生信息后，选课表中该学生的选课信息也自动删除。
create trigger trigger_studentinfo on student
after delete
as
begin
delete from sc where sno=(select sno from deleted)
end

drop trigger trigger_studentinfo

delete from student where sno='0611101'

--第七题，（7）为防止其他人修改成绩，在grade列上创建触发器，要求不能更新sc表中的grade列。
create trigger trigger_grade on sc
after update
as
if update(grade)
begin
print '不能更新sc表中的grade列!'
rollback
end

drop trigger trigger_grade

update sc set grade=grade*1.1

--第八题，（8）创建一个触发器，不允许删除SC表。
create trigger trigger_del_sc on database
after drop_table
as
begin
print '不允许删除SC表！'
rollback
end

drop trigger trigger_del_sc

drop table sc