7实验七触发器.docx
《7实验七触发器.docx》由会员分享，可在线阅读，更多相关《7实验七触发器.docx（22页珍藏版）》请在冰点文库上搜索。
7实验七触发器
实验七触发器
一、实验目的
（1）理解触发器的用途、类型和工作原理
（2）掌握利用T-SQL语句创建和维护触发器的方法
（3）掌握利用企业管理器创建、维护触发器的方法
二、实验内容
1.创建after触发器
（1）创建一个在插入时触发的触发器sc_insert,当向sc表插入数据时，须确保插入的学号已在Student表中存在，并且还须确保插入的课程号在Course表中存在；若不存在，则给出相应的提示信息，并取消插入操作，提示信息要求指明插入信息是学号不满足条件还是课程号不满足条件（注：
Student表与sc表的外键约束要先取消）。
createtriggersc_insert
onsc
afterinsert
as
ifnotexists（select*fromstudent,inserted
wherestudent.sno=inserted.sno）
begin
print'插入信息的学号不在学生表中！
'
ifnotexists（select*fromcourse,insertedwhereo=o）
print'插入信息的课程号不在课程表中！
'
rollback
end
else
begin
ifnotexists（select*fromcourse,insertedwhereo=o）
begin
print'插入信息的课程号不在课程表中！
'
rollback
end
end
执行：
、insertintoSC
values（'20110112','001','78'）
删除外键约束：
altertableSC
dropconstraintFK__SC__Sno__182C9B23
、insertintoSC
values（'20110002','001','78'）
、insertintoSC
values（'20110002','006','78'）
（2）为Course表创建一个触发器Course_del，当删除了Course表中的一条课程信息时，同时将表sc表中相应的学生选课记录删除掉。
createtriggercourse_del
oncourse
afterdelete
as
ifexists（select*fromsc,deleted
whereo=o）
begin
deletefromsc
whereoin（selectcnofromdeleted）
end
deletefromCourse
whereCno='003'
select*fromSC
（3）在Course表中添加一个平均成绩avg_Grade字段（记录每门课程的平均成绩），创建一个触发器Grade_modify，当SC表中的某学生的成绩发生变化时，则Course表中的平均成绩也能及时相应的发生改变。
altertableCourse
addavg_Gradesmallint
updateCourse
setavg_Grade=（selectAVG（Grade）fromSC
whereSC.Cno=Course.Cno）
select*fromCourse
createtriggerGrade_modify
onsc
afterupdate
as
ifupdate（grade）
begin
updatecourse
setavg_grade=（selectavg（grade）
fromscwhereo=o
groupbycno）
end
updateSC
setGrade='90'
whereSno='20050001'andCno='001'
（4）测试上述三个触发器。
测试过程在
（1）、
（2）、（3）中均给出
2.创建insteadof触发器
（1）创建一视图Student_view,包含学号、姓名、课程号、课程名、成绩等属性，在Student_view上创建一个触发器Grade_moidfy，当对Student_view中的学生的成绩进行修改时，实际修改的是sc中的相应记录。
createviewStudent_view
as
selects.Sno,Sname,c.Cno,Cname,Grade
fromStudents,Coursec,SC
wheres.Sno=SC.Snoandc.Cno=SC.Cno
select*fromStudent_view
createtriggerGrade_moidfy
onStudent_view
insteadofupdate
as
ifUPDATE（Grade）
begin
updateSC
setGrade=（selectGradefrominserted）where
Sno=（selectSnofrominserted）and
Cno=（selectCnofrominserted）
end
updateStudent_view
setGrade=40
whereSno='20110001'andCno='002'
select*fromStudent_view
select*fromSC
（2）在SC表中插入一个getcredit字段（记录某学生，所选课程所获学分的情况），创建一个触发器ins_credit，当更改（注：
含插入时）SC表中的学生成绩时，如果新成绩大于等于60分，则该生可获得这门课的学分，且该学分须与Course表中的值一致；如果新成绩小于60分，则该生未能获得学分，修改值为0。
altertableSC
addgetcreditsmallint
select*fromSC
updateSC
setgetcredit=（selectcreditfromCourse
whereSC.Cno=Course.Cno）
whereGrade>=60
updateSC
setgetcredit=0
whereGrade<60
createtriggerins_credit
onSC
insteadofupdate,insert
as
begin
declare@s_nochar（8）,@c_nochar（3）,@new_gradesmallint,@credsmallint
select@s_no=sno,@c_no=cno,@new_grade=gradefrominserted
select@cred=creditfromcoursewherecno=@c_no
if（@new_grade>=60）
begin
deletefromSCwheresno=@s_noandcno=@c_no
insertintoSCvalues（@s_no,@c_no,@new_grade,@cred）
end
else
begin
deletefromSCwheresno=@s_noandcno=@c_no
insertintoSCvalues（@s_no,@c_no,@new_grade,0）
end
end
insertintoSC（sno,cno,grade）values（'20081800','002',85）
（3）测试上述两个触发器。
测试结果在
（1）、
（2）中均已给出
3.使用T-SQL语句管理和维护
（1）用系统存储过程sp_helptrigger查看触发器Grade_modify的相关信息
sp_helptriggerStudent_view
（2）使用系统存储过程sp_helptext查看触发器Grade_modify中的定义内容。
sp_helptextGrade_moidfy
（3）使用select语句查看触发器Grade_modify的定义内容。
selecto.id,c.text
fromsysobjectsoinnerjoinsyscommentsc
ono.id=c.id
whereo.type='TR'ando.name='Grade_modify'
（4）用系统存储过程sp_depends查看触发器Grade_modify的相关性。
sp_dependsGrade_modify
（5）将sc_insert触发器改为insteadof触发器，实现的功能不变。
droptriggersc_insert
createtriggersc_insert
onsc
insteadofinsert
as
ifnotexists（select*fromstudent,inserted
wherestudent.sno=inserted.sno）
begin
print'插入信息的学号不在学生表中！
'
ifnotexists（select*fromcourse,insertedwhereo=o）
print'插入信息的课程号不在课程表中！
'
rollback
end
else
begin
ifnotexists（select*fromcourse,insertedwhereo=o）
begin
print'插入信息的课程号不在课程表中！
'
rollback
end
end
insertintoSC
values（'20110005','001','78','6'）
（6）将触发器sc_insert删除。
droptriggersc_insert
4.使用SQLServerManagementStudio管理存储过程
（1）在SQLServerManagementStudio中重新创建刚删除的触发器sc_insert
选中SC表，展开→右击“触发器”→新建触发器
出现如下界面：
编写余下的SQL语句：
CREATETRIGGERsc_insert
ONSC
INSTEADOFINSERT
AS
BEGIN
ifnotexists（select*fromstudent,inserted
wherestudent.sno=inserted.sno）
begin
print'插入信息的学号不在学生表中！
'
ifnotexists（select*fromcourse,insertedwhereo=o）
print'插入信息的课程号不在课程表中！
'
rollback
end
else
begin
ifnotexists（select*fromcourse,insertedwhereo=o）
begin
print'插入信息的课程号不在课程表中！
'
rollback
end
end
END
（2）查看触发器sc_insert的内容。
展开表SC→触发器→选择sc_insert触发器→查看依赖关系
出现如下界面：
（3）删除触发器sc_insert
选中sc_insert触发器，右击→删除
升级会员 