实验11触发器.docx
- 文档编号:4696542
- 上传时间:2022-12-07
- 格式:DOCX
- 页数:26
- 大小:204.75KB
实验11触发器.docx
《实验11触发器.docx》由会员分享,可在线阅读,更多相关《实验11触发器.docx(26页珍藏版)》请在冰豆网上搜索。
实验11触发器
实验触发器
一、实验目的
(1)理解触发器的用途、类型和工作原理
(2)掌握利用T-SQL语句创建和维护触发器的方法
(3)掌握利用企业管理器创建、维护触发器的方法
二、实验内容
1.创建after触发器
(1)创建一个在插入时触发的触发器sc_insert,当向sc表插入数据时,须确保插入的学
号已在Student表中存在,并且还须确保插入的课程号在Course表中存在;若不存在,则给
出相应的提示信息,并取消插入操作,提示信息要求指明插入信息是学号不满足条件还是课程号不满足条件(注:
Student表与sc表的外键约束要先取消)
onsc
begin
begin
print'插入信息的课程号不在课程表中!
rollback
end
end
E=r*a"etriggersc_in3ert
□n
af^erinsert
S3
F]ifexistsselect「fronstudent;,inserted
wherestudent■5nQ=inserted・anoj
F]beginprint,插入信鳥的学号不在学生走中I,
Eifr\dtexistsselect"fromcourserinaertedwhereo=cl
-print的课程号不在课程芙中!
,
rollback
end
else
begin
ifnot戶*:
吕selectfTramcdutse,insertedwhereczcurse.CTia=inserzed-cna)■
b^gin
p呎吐,插的课程号不在谍程表巾!
,
rollback
and
nr
命令已咸功完成Q执行:
①、insertintoSC
values('20110112','001','78')
fi^LQu«r>3.cql£Q_(xqyPCuqyCM))'
tT4X0SC
l-raluea'^011011J\BQai'."73.-
勒A・rtaLJ用L11晤
耳#12=_二・止口■鼻盍“也用-JlLiilwJ耳"#E右lusvi-Jhg
#「一一■
删除外键约束:
altertableSC
dropconstraintFK__SC__Sno__182C9B23
SQLQuery6.sql-ZQ^(zqy-PC\zqy(5Z)厂SQLQueryJ.曰ilnertableSC
LdropconsrraintFKSCSno18SCSB23
SQLQueryS.sql-ZQ.„(zqy-PC\zqyC57))*SQLQ
曰intoSC
L辛辽朋导厂201二兀"J・0兀・J*7即)
鬲消息
豔罄瞬餌孰第a行事劳在触发器中结束。
批处理已中止•
②、insertintoSC
values('20110002'
'001','78')
SQLQuery5.sql-ZQ^(zqy-PC\zqy(54))*曰intoE匚
Lvalues(*2D11OOQ2hr*Q01m
nr
Jj消息
(1行受影响)
③、insertintoSC
'006','78')
values('20110002'
□insertinto5匚
Lval口总弓1:
?
2011000^\・DO箸'土・7邑「}
A消息
插入信息的课程号不在课程表中!
消息2,级别®状态r丄行事势在触发器中结束。
批处理已中止。
(2)为Course表创建一个触发器Course_del,当删除了Course表中的一条课程信息时,同时将表sc表中相应的学生选课记录删除掉。
createtrigger
coursedel
oncourse
afterdelete
asif
exists(
select
where
*
sc
fromsc,deleted
.eno=deleted
.eno)
begin
delete
from
sc
where
sc.eno
in(
selecteno
from
end
deleted)
SQLQuery$.s<]l-Z □createtriggerCQU.rse_del aneourse after? delete existsselectfromszfdtleLtd U'h.eresc.cno=delet-ed.sno begin deletefremaz whereac.enoiz(selecc.enofromdelereden(i a消息 命令已丽 deletefromCourse whereCno='003' £QLQuery3.sql-2Q._(zqy-PC\zqy(54}J 日deletefromGourde whereCno-*003・ li消息 门行受診闭 <1行受影响) select*fromSC 绪杲亠、消月 S™ Cno Grade 1 』==>93■■■■3■■■■■■■h■■■■■■A■■■ 120ii«nit 001 80 2 20110001 002 73 3 20110X12 001 78 4 2D110002 002 GO 5 20110101 Ml 8C 』I £QLQueiy3.sql-2Q_(zqy-PC\select'XrciTiSC (3)在Course表中添加一个平均成绩avg_Grade字段(记录每门课程的平均成绩),创建一个触发器Grade_modify,当SC表中的某学生的成绩发生变化时,则Course表中的平均成绩也能及时相应的发生改变。 altertableCourseaddavg_Gradesmallint 5QLQuery5L£ql-ZQ..Jzqy-PC\zqy □altertateleCctitje addavg^radeJir.illin.T: 命令已成功兗成〜 updateCourse setavg_Grade=(selectAVG(Grade)fromSC whereSC.Cno=Course.Cno) EQLQuery^.s^l-ZQ_(2qy-PC\? qy(52»*huiKlateCourse setavg-_Grade=(s&lecLAVG[GradesEroir.SG Lwhere5C.Cno=Course-Cno} select*fromCourse EQLQuery&fql-ZQ._(zqy-PO^qy(52J)" 3electffromCourse *nr I__ |园鋳和鱼消Jg_ ChoCnameTutgi_pericrQ^ditavgjGrade 1rSl"I高教M679 2»亦厂C语言程厚设计30569 3M4伽曲_B日sic4NULL createtriggerGrade_modify onsc afterupdate as ifupdate(grade) begin updatecourse fromscwherecourse.eno=sc.eno groupbyeno) end £<2LQuery€.sql-ZQu(zqy-PC\zqy(Si})* 日createtriggerGrade_rcodify onsc afterupdate as Ifupdate■grade 0匚号工口 口pdarecourseaecavg^grade--selectav^(QiAde fromhcwheiecouzae.cna=sc.cilqgroufbyeno) end 內消息 命令已成功完砾 updateSC setGrade='90' whereSno='20050001'andCno='001' pSQL^uery€-.sql-ZQ.H(zqy-PC\zqyC52})*| Bupda匸亡3匚 直色匸Srade=*90* LwnereSno=*201100011andCno=*0011 <| 商话息 <3行受影响! (1行受彫响) Sno Cno Grade 20110001 Mi Q2) 20110001 002 7B 20110002 0Q1 7B 20110002 002 60 20110103 00】 ao ALLi mi Cno Cname TotaljMrioc Credit avg_Grade COL 96 6 002 匚语言程序设计 30 J 關 CCF4 yisusl^Bssic 4 NLAL MULL AU2 NUU J^ULL AL41 (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 S QLQueryll.^ql-Z...(2qy-PC\zqy(S2))* =]createiriewSt-^identview 33 selects.SnofSnair-,c.匚norCn&ir.er.GradeffciftSr吕r亡C-J.TS&二$£亡 -叩匕eres.Sno=SC・口口・Cj: q=5C■匚n口 * in 消息 命令已成功完成口 select*fromStudent_view £QLQuerylL£ql-Z...(zqy-PC\M|y(52))* 5-elect*fromStuden七view irr J结果_J消息 Sno Sname Cno CnamE Grade 1 : 2011KM1 1张虹 J01 高数 2 20110001 號虹 M2 G吾害程序设计 7& 3 2011NJ02 林红 001 高数 7& 4 2D110002 林红 M2 G吾言稈序设计 閒 5 20110103 赳青 Ml 高数 createtriggerGrade_moidfy onStudent_view insteadofupdate as ifUPDATE(Grade) begin updateSC setGrade=(selectGradefrominserted)where Sno=(selectSnofrominserted)and Cno=(selectCnofrominserted) end EQLQuery4.£ql-2Q_(zqy-PC\zqy(SS))1fQLQuery弓石ql-ZQ...[z. on5tuiient_view ins匚亡己dofupdate as ifUPDATE(Grade.: ! begin updateSC secGrade-selectGzadeTroic丄naeitedwhere 5no=select5jicificininser匸亡d]and Cnc=selectCncXxcttiinserted} end updateStudent_view setGrade=40 whereSno='20110001'andCno='002' SQLQuery3.sql-(zqy-y(56))*select1fromStudenr_view itr J结杲J消息 Srx? Sname C™ Cnarne Grade 1 i2G110OT1I 张虹 2 叩―! lH-lll一电 20110001 张虹 002 c语寻程序设计①^ 3 2G11CXM2 誌红 001 高数 7S 4 20110002 誌红 002 口吾言程序设计 GO 5 20110103 赵青 001 高数 select *from SC (2)在SC表中插入一个getcredit字段(记录某学生,所选课程所获学分的情况),创建一个 触发器ins_credit,当更改(注: 含插入时)SC表中的学生成绩时,如果新成绩大于等于60分,则该生可获得这门课的学分,且该学分须与Course表中的值一致;如果新成绩小于60 分,则该生未能获得学分,修改值为0。 altertableSC addgetcreditsmallint 农Lqumry3・s>ql-ZQ...Czqy-PC\zqy(5 0altertatle3匚 -getcredrusncallint 自消息 命令已成功克成。 Sno Grio Grade Setcredit 1 \20110001: 001 NULL 2 20110001 0C2 443 NULL 3 20110002 001 7B NULL 4 201102 002 &Q NULL 5 2011C103 D01 ao NULL update SC creditfromCourse whereSC.Cno=Course.Cno) whereGrade >=60 /£QLQ"rym刊-ZQ...(2qy-PCVqy鬥 曰update5G s^tg'etcr^dir-(selectcreditfrom匚ouisewhereSC-Cno^CaiiTse.Cno; --wher-eG-rade>=6t) setgetcredit =(select EQLQueryS.sql-y-FC\zqy(S6}) select_frcuoSC rrr updateSC setgetcredit=0 whereGrade<60 SQLQuery3.&ql-ZQ...(zq日uplite号匚 set.g&tcrsdlt-fl tj消息 <二行受彩响J £QLQuery3.s(|l-ZQ»(sqy-PC\zqy伊selectlfromSC 二1结果.」消息 Sno 5口 Grade getcredit 1 raiiiiidciTl Ml 射 € 2 20110001 40 0 2 ^0110002 Ml 7B £ A 2011000? 驱 60 5 5 20110101 Ml 甜 createtriggerins_creditonSC insteadofupdate,insert asbegin declare@s_nochar(8),@c_nochar(3),@new_gradesmallint,@credsmallint select@s_no=sno,@c_no=cno,@new_grade=gradefrominserted select@cred=credit fromcoursewhereeno=@c_no delete from SCwhere insert into SC values end else begin delete from SC where insert into SC values sno=@s_noandeno=@c_no (@s_no,@c_no,@new_grade,@cred) sno=@s_noandeno=@c_no (@s_no,@c_no,@new_grade,0) end if(@new_grade>=60)begin end insteadofupdate.insert a3 □begin declare@3_nechar8『3c_nechar3,3new_gtadesitallintT@credsmallintselect.&s_no=3nor5c_nc=cnoK? new_grade=gradefrominserted fiornccLirsewherecilq=@cno select? cred=credit,if^rewjrade>=60) begin delete froit 5C insert 二二匸z where3nc=®s_noandcno=@c_n.ovalues(®s_nc,®c_no,@new_gxad&,@csed end elsebegin delete 二二 二二匸z SC whsiem匕七二andena=3c_! ievalues(®a_nc,Sc_nc,3new_gradef0) end 1-end EJcreatetrigger二H9_u工亡d丄ronSC rrr 白消息 命令已成功完成。 insertintoSC(sno,eno,grade)values('20081800','002',85) i^aex-Vim;QSC(suq,GrjoKgxadej1'^QCaiSQQ'j'0Q2r 匕消息 汕行受影响) 口行受影响) (1行受影响) □elec匚Tfrom3C □貉果_』鞘息 Sno Cho Grade getcradit 1 |2M31300 |002 S5 5 2 20110001 001 € 3 2O11D0D1 M2 4唯 D 4 20110002 001 £ 5 2Q110W2 002 5 20110103 001 &0 € (3)测试上述两个触发器。 测试结果在 (1)、 (2)中均已给出 3.使用T-SQL语句管理和维护 (1)用系统存储过程sp_helptrigger查看触发器Grade_modify的相关信息 sp_helptriggerStudent_view y£QLQuery4.sql-工Q..(iqy-PC\? qySQLQuery^.sql-ZQ...t~qy-PC.zqy(55〕.」&QLQl sp_helptrirjffexStnew 也结果险消息 trigger_nametriggerjownerisupdaieisdeleieisin爭eitisafterisinsteadofhiggef-ScheE目 1[Gradejnorify[dbo1D001dbo (2)使用系统存储过程sp_helptext查看触发器Grade_modify中的定义内容。 sp_helptextGrade_moidfy £QLQuery4.s(|l-ZQ_(zqy-FC\zqy(S7))*sphelpcextGrade_ntoidXy 二结杲j消息 Text 尸l*J! III! -*411HIlliH4IIIHlHIia*UBII! S-HIII>Mi|II-"411I一Illi円Illl^-llllll-H! ll>*-VIII^ itrigger 2 3 4 5 6 7 on3udent_viewinsteadofupdarte asrfUPDATEfjBde)begh updateSC setGrade=(seleGtGradefrcrninserted)where 95no-JselectSnofrominserted)and IDQio=(selectCnofiuminserted) 11end ⑶使用select语句查看触发器Grade_modify的定义内容。 selecto.id,c.text fromsysobjectsoinnerjoinsyscommentsc ono.id=c.id whereo.type='TR'ando.name='Grade_modify' E)3electo■id,c.text fremsjsotjeers□innerjoinacona.id=c.id -whereci・type=vTR*andn«nsjne=1Gradie_noidfyr *I ru 结果 缶消息 id text : 1S615&1£70: creaftetriggerGrade_nioidfyanStudenrt_viewnsteadofupdarteasifUPDATEl'Gicidejbeginupd…人nn■■■■■eh■■■■«? (4)用系统存储过程sp_depends查看触发器Grade_modify的相关性。 sp_dependsGrade_modify £QLQuery4.sc|l-2Q—
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验 11 触发器
![提示](https://static.bdocx.com/images/bang_tan.gif)