1、實(shí)驗(yàn)七觸發(fā)器一、實(shí)驗(yàn)?zāi)康睦斫庥|發(fā)器的用途、類型和工作原理掌握利用T-SQL語(yǔ)句創(chuàng)建和維護(hù)觸發(fā)器的方法掌握利用企業(yè)管理器創(chuàng)建、維護(hù)觸發(fā)器的方法二、實(shí)驗(yàn)內(nèi)容1.創(chuàng)建after觸發(fā)器創(chuàng)建一個(gè)在插入時(shí)觸發(fā)的觸發(fā)器sc_insert,當(dāng)向sc表插入數(shù)據(jù)時(shí)，須確保插入的學(xué)號(hào)已在Student表中存在，并且還須確保插入的課程號(hào)在Course表中存在；若不存在，則給出相應(yīng)的提示信息，并取消插入操作，提示信息要求指明插入信息是學(xué)號(hào)不滿足條件還是課程號(hào)不滿足條件(注：Student表與sc表的外鍵約束要先取消)。createtriggersc_insertonscafterinsertasifnotexists(

2、select*fromstudent,insertedwherestudent.sno=inserted.sno)beginprint插入信息的學(xué)號(hào)不在學(xué)生表中！ifnotexists(select*fromcourse,o=o)print插入信息的課程號(hào)不在課程表中！rollbackendelsebeginifnotexists(select*fromcourse,o=o)beginprint插入信息的課程號(hào)不在課程表中！rollbackendendcreatetriggersc_insertonscafterinsertasFifnotexists(select*fromstudentfi

3、nsertedwher皂studentsno=insertedanoFbegin中“t，插入信息的學(xué)號(hào)不在學(xué)生表中！，F(xiàn)ifnotexists(select：frfroincourserinsertedwherecourseunci=:Lii呂己工匸Ed.unci尸“匸，插入信息的課程號(hào)不在課程表中！，rollbackendelsenjsbeginifnotexists(selectfrcmncoursefinsertedwherecourseunio=:iiis已工匸已duncibegin-插入信息的課程號(hào)不在課程表中！，rollbackendend（丨忻LS消息命令已成功完成。執(zhí)行：、in

4、sertintoSCvalues(20110112,001,78)/LQuery3.sql-7zQ_(zqy-PCzqy(54)日insertintoSCLvalues(201101121,001,78)4|IPIT消息消息汽,級(jí)別氐狀態(tài)。，第i行、INSERT語(yǔ)與FOREIGNKEY約束FK_SC_Sno_182C9B23.：.該沖字巖生于婁対居庫(kù),rStudent_info,p,rdho_Studentcolumn1Sno語(yǔ)句已終止。一刪除外鍵約束：altertableSCdropconstraintFKSCSno182C9B23/QLQuerySsql-ZQ_.(zqyPCzqy(52)

5、*SQLQu&ry3.日altertaileSC1-drcpccnstrdintFK_SC_Snc_1S2C9E23A消息命令已成功完成QLQuery9.sql-ZQ_.(zqy-PCzqy(57)*SQLQ日insert,intcSC1-values(120LL0LL2fPOOLP_J消息插入信息的學(xué)號(hào)不在學(xué)生表中！消息沁,圾別氐狀態(tài)第丄行事務(wù)在觸發(fā)器中結(jié)束。批處理已中止。、insertintoSCvalues(20110002,001,78)/QLQuery3.sql-7zQ.(zqy-PCqy(54)*日insertintcSCLvalues(OLLOOOS,r00LrfRz1)frrr

6、T消息門行受影響)、insertintoSCvalues(20110002,006,78)insertintcSC1-values(12OLLOOO2,1006c)j消息插入信息的課程號(hào)不在課程表中！消息沁,級(jí)別1狀態(tài)一第1行事務(wù)在觸發(fā)器中結(jié)束。批處理已中止。為Course表創(chuàng)建一個(gè)觸發(fā)器Course_del，當(dāng)刪除了Course表中的一條課程信息時(shí),同時(shí)將表sc表中相應(yīng)的學(xué)生選課記錄刪除掉。createtriggercourse_deloncourseafterdeleteasifexists(select*fromsc,o=o)oin(selectcnofromdeleted)endZSQ

7、LQuery&.sql-ZQ_.(zqy-PCzqy(52)*日createtriggercourse_de：lcnccjrseafterdeleteasFifexists(c=c)FbeginFcin(selectcncfromdeleted)end*rir二亍消息命令已成功完成。deletefromCoursewhereCno=003/&QLQiiery3.sql-ZQ.(zqy-PCzqy(54)日deletefromCcjrseLwhereCnc=003fI巴Z7消息：1行受黒響）：1行受影響）select*fromSCEQLQueryS.sqlZQ_.(zqy-PC也能及時(shí)相應(yīng)的發(fā)生

8、改變。altertableCourseaddavg_GradesmallintSQLQueryS.sql-ZQ_.(zqy-PCzqy日altertableCcirseL己delavgGradesmall!nt_J消息命令已成功完成。updateCoursesetavg_Grade=(selectAVG(Grade)fromSCwhereSC.Cno=Course.Cno)/iQLQu&ryS.sql-ZQ_.(zqy-PCzqy(52)*曰updateCcarsesetavg_Grade=(selectAVG(Grade)fromSCwhereSC.Cnc=Ccarse.CnctrrrT消息

9、：3行受影響）select*fromCourse/SQLQu&ryS.sql-ZQ.(zqy-PCzqy(52)*select*fromCcjtss*Lrrr結(jié)果消息CnoCnameTotal_periorCreditavg_GradeDC1高數(shù)966792DD2匚語(yǔ)言程序設(shè)計(jì)3D56930-D4Visual_Basic4S4NULLcreatetriggerGrade_modifyonscafterupdateasifupdate(grade)beginupdatecoursesetavg_grade=(selectavg(grade)o=ogroupbycno)end/QLQuery6.s

10、ql-ZQ_.(zqyPCzqy(52)*日createtriggerGrade_madifycnscafterupdate己5ATEifjpdate(grade!beginupdateccjrsesetavg_grade=(selectavc(c=cgrcjpbycnc)1-endfrrrT消息命令已成功完成。updateSCsetGrade=90whereSno=20050001andCno=001/SQLQueryS.sqlZQ.(zqyPCzqy(52)*日updateSCsetGrade=,901-where5nc=12OLLOOOL1已ndCnc=pOOLrrrT消息：3行受影響）

11、:1行受影響）CnoCnameTotalperiorCreditavgGrade001高數(shù)966002匚語(yǔ)言程序設(shè)計(jì)80569004Visual_Basic484NLH.LNULLNLH.L（4）測(cè)試上述三個(gè)觸發(fā)器。測(cè)試過程在（1）、（2）、（3）中均給出2.創(chuàng)建insteadof觸發(fā)器（1）創(chuàng)建一視圖Student_view，包含學(xué)號(hào)、姓名、課程號(hào)、課程名、成績(jī)等屬性，在Student_view上創(chuàng)建一個(gè)觸發(fā)器Grade_moidfy,當(dāng)對(duì)Student_view中的學(xué)生的成績(jī)進(jìn)行修改時(shí)，實(shí)際修改的是sc中的相應(yīng)記錄。createviewStudent_viewasselects.Sno,S

12、name,c.Cno,Cname,GradefromStudents,Coursec,SCwheres.Sno=SC.Snoandc.Cno=SC.Cnoselect*fromStudent_view/c:LQueryll.sql-Z_(zqy-PCzqy(52)*3createviewStudentviewdSselects.Snc,Sname,c.Cnc,CnamefGradefromStudentsfCcirsecfSC-wneres.5nc=5C.Sncandc.Cnc=5C.CncPIPR消息命令已成功完成。SQLQueryllsql-Z_(zqy-PCzqy(52)*select*

13、fromStudent：viewrrr二結(jié)果消息SnoSnameCnoCnameGrade12011WHJ1味虹D01高數(shù)9D220110001味虹D02C語(yǔ)言程序設(shè)計(jì)7S320110M2林紅D01高數(shù)7S420110M2林紅D02匚語(yǔ)言程序設(shè)計(jì)60520110103趙青DD1高數(shù)BDcreatetriggerGrade_moidfyonStudent_viewinsteadofupdateasifUPDATE(Grade)beginupdateSCsetGrade=(selectGradefrominserted)whereSno=(selectSnofrominserted)andCno=

14、(selectCnofrominserted)endQLQuery4.sql-ZQ_.(zqy-PCqySQLQuetyS.sql-ZQ.zcreatetriggerGrade_mcidfycnStudent_viewinsteadcfupdate己呂ifUPDATE:GradeibeginupdateSCsetGrade=(selectGradefrominsertediwhereSnc=(selectSuefreminserted)andCnc=(selectCncfreminserted)endupdateStudent_viewsetGrade=40whereSno=20110001a

15、ndCno=002ZSQLQueryS.sql7zQ.(zqy-PCzqy(59)*曰update51ident_viewsetGrade=40-whereSnc=R20LL000LR已ndCnc=R002/rrr3消息門行受影響)le5CL己ddgetcreditsmallint_J1消息命令已成功完成。select*fromSC/SQLQuery3.sql-Q.(zqy-PCzqy(56)select*fromSCrrr二結(jié)果消息SnaCnoGradegetcredit12DmDD1DD19DNULL220110001DD240NULL320110M2DD173NULL420110M2DD

16、260NULL520110103DD1SDNULLupdateSCsetgetcredit=(selectcreditfromCoursewhereSC.Cno=Course.Cno)whereGrade=60/QLQuery3.sql-ZQ-.(zqy-PCqy(56)*日updateSCsetgetcredit=(selectcreditfromCcjrsewhereSC.Cnc=CcjrBe.Cno)-wnereGrade=60updateSCsetgetcredit=0whereGrade60SQLQueryS.sql-ZQ.(zq日updateSCsetgetcredit=0-whe

17、reGrade60R消息門行受影響）=60)begindeletefromSCwheresno=s_noandcno=c_noinsertintoSCvalues(s_no,cno,new_grade,cred)endelsebegindeletefromSCwheresno=s_noandcno=c_noinsertintoSCvalues(s_no,cno,new_grade,0)endend日createtriggerins_creditcnSCinsteadcfupdatefinsertasFbegindeclares_ncchar(8)c_ncchar(3f3new_grade5ma

18、llint,rcredsmallintselects_nc=snc,nc=cnc,:newgrade=gradefrominsertedselectcred=creditifnew_grade=60)begindeleteinsertfromcoursewherec:no=cnofromintcSCSCwheresnc=s_ncandcnc=c_ncvalues(3stlc,nc,newgradecred)ElendelsefcegindeleteinsertfromintcSCSCwheresnQ=s_noandcno=c_nQvalues(3snc,rncfnewgradeQ)end1-e

19、ndrrr_J消息命令已成功完成。insertintoSC(sno,cno,grade)values(20081800,002,85)111,ir-v-vinsertintcSC(sncfcnc,grade)values(1200zLz00rfr00235)A消息：o行受影響）門行受影響）：1行受影響）select*from5C二結(jié)果消息SnoCnoGradegetcredit12WS1S0DDD2B552卻11麗加DD19D63卻11麗加DD240D4卻11麗證DD17S65卻11麗證DD260562011D1D3DD1SD6（3）測(cè)試上述兩個(gè)觸發(fā)器。測(cè)試結(jié)果在（1）、（2）中均已給出3.使

20、用T-SQL語(yǔ)句管理和維護(hù)用系統(tǒng)存儲(chǔ)過程sp_helptrigger查看觸發(fā)器Grade_modify的相關(guān)信息sp_helptriggerStudent_viewZQLQu&ry4.5ql-ZQ.(zqy-PCzqy(57)*SQLQuery3.sql-ZQ.(zqy-PCzqy(55)/&QLQlsp_?ieLptrigger51udent_viewJIIII結(jié)果歯消息trigger_nametrigger_awnerisupdateisdeleteisinsertisafterisinsteadaftrigger_schema1iGrade_moidlyfdbo1DDD1dbo使用系統(tǒng)存

21、儲(chǔ)過程sp_helptext查看觸發(fā)器Grade_modify中的定義內(nèi)容。sp_helptextGrade_moidfyQLQuery4.sqlZQ_.(zqyPCzqy(57)*隨sprieLptexllGrademaidfy丄1結(jié)果L消息Text1createtriggerGrade_maidly2onStudent_view3insteadofupdate4as5ifUPDATEfGrade)begin7updateSCBsetGradeSelectGradefrominserted)where9Sno=electSnofrominserted)and10Cno=electCnofro

22、minserted)11end使用select語(yǔ)句查看觸發(fā)器Grade_modify的定義內(nèi)容。selecto.id,c.textfromsysobjectsoinnerjoinsyscommentscono.id=c.idwhereo.type=TR=Grade_modifyselectc.idc.textfromsyscbjectscinnerjcinsysccirir.entsccnc.id=c.id-wherec.type=,TE=*Grademoidfy14Irrf二結(jié)果J)消息idtad1|1SC15S1G70createtriggerGrad

23、e_moidlyonStudent_viewinsteadofupdateasifUPDATEfGradebeginupd.用系統(tǒng)存儲(chǔ)過程sp_depends查看觸發(fā)器Grade_modify的相關(guān)性。sp_dependsGrade_modifySQLQu&ry4.sql-ZQ.Uqy-PCVqy(57)*強(qiáng)LQuwry了sql-Z3pdependsGrademciidfy二結(jié)果消息nametypeupdatedselectedcolumn1|dbo.SC:usertablenoyesEno2dbo.SCusertablenoyesCno3dbo.SCusertableyesnoGrade4d

24、bo.Student_viewviewnoyesGrade將sc_insert觸發(fā)器改為insteadof觸發(fā)器，實(shí)現(xiàn)的功能不變。droptriggersc_insertcreatetriggersc_insertSLQueryi.sql-Zy.Izqy-PCzqy34drcptriggerscinsert消息命令已成功完成。onscinsteadofinsertasifnotexists(select*fromstudent,insertedwherestudent.sno=inserted.sno)beginprint插入信息的學(xué)號(hào)不在學(xué)生表中！ifnotexists(select*fro

25、mcourse,o=o)print插入信息的課程號(hào)不在課程表中！rollbackendelsebeginifnotexists(select*fromcourse,o=o)beginprint插入信息的課程號(hào)不在課程表中！rollbackendendSQLQuery5.sql-ZQ.(zqy-PQzqy52)*SQLQue.sql-ZQ.(zqyPCzqy(57)*SQLQuery3.sql-ZQ.(zqy-PCverea匸已triggersc_i:nser匸onscinsteadofinsertifnotexists(selectfromstudentfinsertedwherestuden

26、tsno=i:nsertedsnobegin-插入信息的學(xué)號(hào)不在學(xué)生表中！，F(xiàn)ifnotexists(select芒fromcoursefinsertedwherecourseuiici=j_iisE工匸已dcnciPrint，插入信息的課程號(hào)不在課程表中！，rollbackendelsebeginFifnotexists(select缺froracoursefinsertedwherecourseciici=jjQ5已工匸EdunioFbegin中匸，插入信息的課程號(hào)不在課程表中！，rollbackendend*LII!丄消息命令已成功完成。insertintoSCvalues(201100

27、05,001,78,6)ZSQLQueryS.sql-ZQ.(zqy-PCzqy(52)*SQLQuei日insertintcSCLVdlues(R20LL0005R02fRzR6R)占消息插入信息的學(xué)號(hào)不在學(xué)生表中！消息曲,級(jí)別氐狀態(tài)一第1行事務(wù)在觸發(fā)器中結(jié)束。批處理已中止。將觸發(fā)器sc_insert刪除。droptriggersc_insertSQLQuery5.sql-ZQ.(zqy-PCzny(52)dreptriggerscinsert為消息命令已成功完成。4.使用SQLServerManagementStudiO管理存儲(chǔ)過程(1)在SQLServerManagementStud中重

28、新創(chuàng)建剛刪除的觸發(fā)器sc_insert選中SC表，展開右擊“觸發(fā)器”新建觸發(fā)器SQLQueryS.sql-droptriggeS數(shù)揚(yáng)庫(kù)快照曰|Studentjnfo口數(shù)誓庫(kù)關(guān)票圖丄表C3累統(tǒng)表口dbo.CouirsEdbo.SC口列縫約束為消息命令已成功完成:田0S口索引統(tǒng)i+新融炭器閭”.啟動(dòng)PowerShell(H)_3dbo.StudJdbo.Stud+口視囹報(bào)表(P)刷新舊圧I鬥同女伺出現(xiàn)如下界面：Author:Createdate:Description:GREATETRIGGERW芒h皂m且聖&血皂*sysnauner5cheiD.a_Naine.ONfTiaibJ_E_Naiii

29、nErBysnamefTable_ETaireAFTERASBEGIN-SETNOCOUNTONaddedtopreventextraresultsetsfrom-interferingwithSELECTstatementsSETNOCOUNTON;InsertstatementsfortriggerhereLENDGO.編寫余下的SQL語(yǔ)句:CREATETRIGGERsc_insertONSCINSTEADOFINSERTASBEGINifnotexists(select*fromstudent,insertedwherestudent.sno=inserted.sno)beginprint插入信息的學(xué)號(hào)不在學(xué)生表中！ifnotexists(select*fromcourse,o=o)print插入信息的課程號(hào)不在課程表中！rollbackendelsebeginifnotexists(select*fromcourse,o=o)beginprint插入信息的課程號(hào)不在課程表中！rollbackendendEND日CREATETRIGGERsc_i:n5ertONSCINSTEADOFINSERTASABEGINFifnotexists(select*fromstudent,insertedwherestudent