图5-3
21
2.6实验六 触发器和存储过程
一、实验目的
1.通过实验使学生加深对数据完整性的理解，学会创建和使用触发器。 2.熟练创建存储过程，体会存储过程的作用。
二、实验内容
1．触发器
①在学生表Student中定义一个触发器tri1，保证新添加的学生的年龄（Sage）在16～25岁之间。 ②在成绩表SC中定义一个触发器tri2，当修改一个记录时，确保此记录的成绩(Grade)在0～100分之间。 ③在学生表Student中创建一个触发器tri2，当删除学生表Student中学生时，自动删除成绩表SC中相应学生的选课记录。
④在学生表Student中创建一个触发器tri4，当修改学生表Student中学生的学号时，自动修改成绩表SC中的相应学号值。并验证该触发器是否可以正常工作。
⑤在学生表Student中创建一个触发器tri5，当删除学生表Student中学生时，自动删除成绩表SC中相应学生的选课记录；当修改学生表Student中学生的学号时，自动修改成绩表SC中的相应学号值。并验证该触发器是否可以正常工作。 2．存储过程
①利用存储过程查找Student表中所有人的记录 ②利用存储过程查找Student表中某学号的记录
③利用存储过程查找Student表中某学号、某门课程的记录 ④计算出某学生的平均分
⑤创建一个带返回值的存储过程，返回某人的平均成绩
⑥创建一个存储过程pro6将课程中某门课程的学分修改为指定的值，其中某门课程的课程号、指定的值通过参数传递。并多次调用执行该存储过程来修改不同的课程的学分值。
三、具体实现
1．触发器
①在学生表Student中定义一个触发器tri1，保证新添加的学生的年龄（Sage）在16～25岁之间。 【创建触发器】
create trigger tri1 on student for insert as
if(select Sage from inserted)>25 or (select Sage from inserted)<16 begin
print ‘年龄在16-25岁之间’ rollback end
【调用触发器】insert into Student values('200231001','王涛','男',30,'IS')
②在成绩表SC中定义一个触发器tri2，当修改一个记录时，确保此记录的成绩(Grade)在0～100分之间。 create trigger tri2 on SC
22
for update as
if not exists(select * from inserted where Grade between 0 and 100) print ‘成绩应在0-100分之间’ rollback
注：就是插入语句后，如果inserted表（插入或更新影响行构成的表）内的值不在0-100之间，就撤销插入。 【调用触发器】 update SC set Grade=120 where Sno='200215122'
③在学生表Student中创建一个触发器tri3，当删除学生表Student中学生时，自动删除成绩表SC中相应学生的选课记录。 【创建触发器】
create trigger tri3 on student for delete as
delete SC where Sno in(select Sno from deleted) delete Student where Sno in(select Sno from deleted) 【调用触发器】
delete Student where Sno=’200215122’
【注意】实现级联删除时，因为Student表和SC表的主键、外键关系，应先允许“级联删除相关记录”。
23
2．存储过程
①利用存储过程查找Student表中所有人的记录 【创建存储过程】
create procedure pro1 as
select * from Student
【调用存储过程】exec pro1
②利用存储过程查找Student表中某学号的记录 【创建存储过程】 create procedure pro2 @xh char(9) as
select * from student where Sno=@xh 【调用存储过程】exec pro2 200215121
③利用存储过程查找Student表中某学号、某门课程的记录 【创建存储过程】 create procedure pro3 @xh char(9),@kch char(4) as
select * from SC
where Sno=@xh and Cno=@kch 【调用存储过程】 exec pro3 200215121,1
④创建一个存储过程pro4，将课程中某门课程的学分修改为指定的值，其中某门课程的课程号、指定的值通过参数传递。并多次调用执行该存储过程来修改不同的课程的学分值。 【创建存储过程】 create procedure pro4 @kch char(4),@xf int as
update course set Ccredit=@xf where Cno=@kch 【调用存储过程】 exec pro4 1,10 exec pro4 2,20
⑤创建一个带返回值的存储过程，返回某人的平均成绩 【创建存储过程】 create procedure pro5 @xh char(9)
24
as
declare @pjf int select @pjf=avg(grade) from SC where Sno=@xh return @pjf 【调用存储过程】 declare @pjcj int exec @pjcj=pro5 200215121
print ‘pjcj=’+cast(@pjcj as char(10))
⑥计算出某学生的平均分 【创建存储过程】 create procedure pro6 @xh char(9),@pjf int output as
select @pjf=avg(grade) from SC where Sno=@xh 【调用存储过程】 declare @picj int
exec pro6 200215121,@pjcj output select @pjcj
25
四、在企业管理器中创建触发器
（1）在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库（本例为学生选课数据库），打开表文件夹，在表窗口中用鼠标右击触发器所属的表（本例为选课表）。
（2）在弹出菜单上选择“所有任务”“管理触发器”项，则弹出如图所示的触发器属性对话框。
图6-1 触发器属性对话框
如果要新建触发器，在文本[T]的文本框中输入创建触发器的SQL语句（见实验内容），单击“检查语法”按钮进行语法的检查，检查无误后，单击确定按钮。
（3）如果要修改触发器，则在名字弹出项中选择要修改的触发器名（本例为SC_inserted）,然后在文本[T]的文本框中对已有的内容进行修改，检查无误后单击“确定”按钮。
（4）如果要删除触发器，则在名字弹出项中选择要删除的触发器名（本例为SC_inserted），并检查文本[T]的文本框中的内容，看是否选得正确，最后单击“删除”按钮。
三.实验感想
26