SqlServer2012实例之触发器的创建

一、触发器实验
注意：把studentmanager数据库中的所有表用select  into命令复制一份，然后用复制后新表完成下面的实验，同时，对每个触发器都要进行验证。


SELECT * INTO department2 FROM department


SELECT * INTO class2 FROM class


SELECT * INTO course2 FROM course


SELECT * INTO s_c2 FROM s_c


SELECT * INTO student2 FROM student


SELECT * INTO teacher2 FROM teacher


SELECT * INTO t_c2 FROM t_c


1、使用SQL语句创建DML触发器
（1）创建触发器ts_sex，在录入学生信息时用以约束性别的值只能是“男”或者“女”


create trigger ts_sex
on student2
after insert
as
if exists(select * from student2 where s_sex not in('男','女'))
begin 
raiserror ('学生性别只能为“男”或者“女”',16,1)
rollback transaction
end


insert into student2
values('2016020203','whp','男',NULL,NULL,NULL,NULL,'20160101')


（2）创建触发器ts_name，用来控制不能更新“姓名”列


create trigger ts_name
on student2
for update
as
if(UPDATE(s_name))
begin
print'禁止更新性名列'
rollback transaction
end


UPDATE student2
 set s_name='ghnhj' 


（3）创建触发器ts1，在录入学生信息时同时将此学生的学号及大学英语课程号录入到成绩表中（大学英语是公共必修课），同时显示录入的记录信息。


create trigger ts1
on student2
after insert
as
begin
declare  @sno char(10),@cno char(10)
select @sno=s_id from inserted
select @cno=course_id from course where course_name='大学英语'
insert into s_c values(@sno,@cno,NULL)
select * from s_c where s_id =@sno
end


insert into student2
values('2016020103','whp','男',NULL,NULL,NULL,NULL,'20160101')


（4）创建触发器ts2，在删除一个毕业生的信息的同时删除这个学生的成绩记录。如果没有此学生则给出提示信息。


create trigger ts2
on student2
after delete 
as
begin
if exists(select s_id from deleted)
begin 
delete from s_c 
where s_id=(select s_id from deleted)
select * from student2
end
else 
print '不存在此学生信息'
end


delete from student2 where s_id ='2016020104'


（5）创建触发器ts3，当更新某个学生的学号时更新这个学生的选课信息。


create trigger ts3
on student2 
after update
as
begin
if exists(select s_id from deleted)
begin
update s_c2 set s_id =(select s_id from inserted)
where s_id =(select s_id from deleted)
select s.s_id,s_name,result
from student2 s,s_c2 sc
where s.s_id=sc.s_id and s.s_id=(select s_id from inserted)
select * from  s_c2
end 
else
print '没有这个学生的选课记录'
end


update student set s_id='2016010106' where s_id ='2016010105'


（6）创建触发器ts4，在向学生表插入、更新记录时检测出生日期与注册日期的合法性，即出生日期应早于注册日期，若不合法给出提示，并撤销此操作。


create trigger ts4
on student2 
after insert,update
as
begin
declare @date0 smalldatetime
declare @date1 smalldatetime
set @date0=(select s_borndate from inserted)
set @date1=(select s_enrolldate from inserted)
if @date0 > @date1
print'出生日期大于注册日期，拒绝操作'
rollback transaction
end


update student2 set s_borndate='1983-05-03' where s_id='2016010101'