实验七实验报告触发器.docx
《实验七实验报告触发器.docx》由会员分享,可在线阅读,更多相关《实验七实验报告触发器.docx(26页珍藏版)》请在冰点文库上搜索。
实验七实验报告触发器
实验七触发器
实验目的
(1)理解触发器的用途
(2)掌握利用T-SQL语句创建和维护触发器的办法
(3)掌握利用SQL创建和维护触发器的办法
实验内容
1.创建AFTER触发器
(1)
ifexists(selectnamefromsysobjectswherename='sc_insert'andtype='tr')
droptriggersc_insert
go
createtriggersc_insert
onSC
afterinsert
as
ifnotexists(select*fromstudent,inserted
wherestudent.sno=inserted.sno)
begin
print'该学号不存在,无法插入!
'
ifnotexists(select*fromcourse,inserted
whereo=o)
print'该课程号不存在!
'
end
go
insert
intosc
values('2011001','001','88'),
('2011009','001','99'),
('2011002','005','55');
插入后的结果如图:
(2).createtriggercourse_del
oncourse
afterdelete
as
begintransaction
declare@cnochar(8)
select@cno=cno
fromdeleted
delete
fromsc
wherecno=@cno
committransaction
go
delete
fromcourse
wherecno='002'
执行之后的course表
(3)altertablecourse
addavg_gradesmallint
updatecourse
setavg_grade=(selectavg(grade)fromSCwhereo=o)
createtriggergrade_modify
onsc
afterupdate
as
ifUPDATE(grade)
begin
updatecourse
setavg_grade=(selectavg(grade)fromsc
whereo=o
groupbycno)
end
updateSC
setgrade='99'
wheresno='20110001'andcno='001'
2.创建INSTEADOF触发器
(1)createviewstudent_view
as
selectstudent.sno,sname,o,cname,grade
fromStudent,course,sc
wherestudent.sno=sc.snoando=o
select*fromstudent_view
ifexists(selectnamefromsysobjectswherename='GRADE_MODIFY'andtype='tr')
droptriggerGRADE_MODIFY
go
createtriggerGRADE_MODIFY
onstudent_view
insteadofinsert
as
IFUPDATE(GRADE)
BEGIN
updateSC
setgrade=(selectgradefrominserted
wheresno=(selectsnofrominserted)
andcno=(selectcnofrominserted))
end
UPDATEstudent_view
setgrade=88
wheresno='20110001'andcno='001'
(2)
altertablestudent
addgetcreditint
select*fromStudent
updatesc
setgetcredit=(selectcreditfromcourse
whereo=o)
wheregrade>=60
updatesc
setgetcredit=0
wheregrade<60
CREATEtriggerins_credit
onsc
insteadofinsert,update
as
begindeclare@snochar(8),@cnochar(8),@new_gradesmallint,@credint
select@sno=sno,@cno=cno,@new_grade=gradefrominserted
select@cred=creditfromCourse
if(@new_grade>=60)
begin
deletefromSCwhere@sno=snoand@cno=cno
insertintoSCvalues(@sno,@cno,@new_grade,@cred)
end
else
begin
deletefromSCwhere@sno=snoand@cno=cno
insertintoSCvalues(@sno,@cno,@new_grade,0)
end
end
insertintoSC(sno,cno,grade)
values('20110001','002',89)
3.使用T-SQL语句管理和维护触发器
(1)sp_helptriggersc
(2)sp_helptextgrade_modify
(3)selecto.id,c.text
fromsysobjectsoinnerjoinsyscommentsc
ono.id=c.id
whereo.type='tr'ando.name='grade_modidy'
(4)sp_dependsgrade_modify
(5)
createtriggersc_insertonsc
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)droptriggersc_insert
4.使用SQL管理触发器
(1)
CREATETRIGGERsc_insertONSC
INSTEADOFINSERTASBEGINifnotexists(select*fromstudent,inserted
wherestudent.sno=inserted.sno)
begin
print'插入信息的学号不在学生表中!
'
ifnotexists(select*fromcourse,insertedwhereo=o)
print'插入信息的课程号不在课程表中!
'
rollback
end
else
begin
ifnotexists(select*fromcourse,insertedwhereo=o)
beginprint'插入信息的课程号不在课程表中!
'
rollback
end
end
END
(2)
(3)