实验11触发器汇总

1、实验目的实验触发器(1)(2)(3)理解触发器的用途、类型和工作原理掌握利用T-SQL语句创建和维护触发器的方法 掌握利用企业管理器创建、维护触发器的方法实验内容1.创建after触发器(1)创建一个在插入时触发的触发器sc_insert,当向sc表插入数据时，须确保插入的学号已在Student表中存在，并且还须确保插入的课程号在Course表中存在；若不存在，则给出相应的提示信息，并取消插入操作，提示信息要求指明插入信息是学号不满足条件还是课 程号不满足条件(注：createtriggerStudent表与sc表的外键约束要先取消)。sc inserton scafterin sertasi

2、f notexists(select * fromstudentwhere student. sno,inserted=inserted. sno )beginprint ifcourse . eno'插入信息的学号不在学生表中！exists( select * from=inserted. eno )pri nt '插入信息的课程号不在课程表中!rollbacknotcourse , insertedwhereendelsebeginifcourse . enonot exists( select * from course , inserted=inserted. eno

3、)wherebegi npri nt '插入信息的课程号不在课程表中!rollbackendend日 create trigger ac_ln3ert n 3Cafter insertasnot exists (select * from studlentr insertedwhere student.sno=inseited.sno)beginprint，插入信息的学号不在学生表中！，ifA ifendelsebeginifnot es=3：s Eselect - from course, inserted where c亡usse. cno-inaerusd. eno) Prinr

4、 *插入信息的课程号不在课程表中！， lallbacJcnot exiszs(select " from course,inserted where o-inaerwftd-eno)beginp工int插入信息的课程号不在课程表中！IQ11匕aukendend* 1rrr(i消息命令已成功完成。执行：、insertinto SCvalues('20110112' , '001','78')SQL(iu*73 Hll lQ-(«V-PCiqy («)",=1 inaezT： inizo SC-valuer (

5、2OL1O1L2' H nCJl J '75- Jm1)I消息百IKSETiT 诣句与 rOPSIGH KEY 约束"7¥_SC_SrtS_10JC5B2S"冲突：该;对牛干数据库"Studefte._Lrtfa",表-dbo . Studtni". iSlunin ' SflO ' 语句已终止*一 T"删除外键约束:alter table SCdropcon stra intFK SC Sno 182C9B23SQLQuerye.ql - ZQ«(zqy-PCzqy (52)* |

6、 SQLQugry3 alter table SCI- drop constraint FK SC Sno l?£C93z'3曲消息命令已成功完成。5QLQuery9.£ql - ZQ.(zqy-PCzqy CS7)i)i* SQLQ 曰 insert into SCL values r 2011012 ''001' , *75* I也消息插入信息的学号不在学生表消息309,级别1巧状态1,第，行 事劳在触发器中结束。批处理已中止.、in sert into SCvalues ('20110002', '001'

7、;,78')” “ SQLQuery3,£ql - ZQ«,iqy-PCiqy (54)* 日 insert into scLvalues '20110002', 'OOl*, 7Sl行受影响)、in sert into SCvalues ('20110002', '006', '78')曰 Insert mt口 5CLvalues('2 0110002 , "006', 73" t插扁消息3609,级别血状态打第1行 事务在触岌器中结束.批处理已中止.(2

8、)为Course表创建一个触发器 Course_del，当删除了 Course表中的一条课程信息时, 同时将表sc表中相应的学生选课记录删除掉。create trigger course_del on course after delete asexists(select*from sc , deletedwheresc.eno =deleted . eno )begindeletefromscwheresc . enoin(selecteno fromendifdeleted )/ SQLQucrye.£ql - ZQ_(iqy-PCVqy (52)*EJ create -trig

9、ger coiir3e_delon courseafter deleteasA if exists (select ” frare 3C, deletedwhere sc . cnQ=cl套Iwt昙d. cn口 Jbegindelete froici scwhere 5C.eno in eselect eno from deleted) endHI曲消息命令已成功葱妇delete from Course where Cn o='003'SQI-Query3.sql - ZQ.(zqy-PCzqy (S4) delete ftoil CourseI-where Cno='

10、003'BL(1行受影响(1行受影响）select* from SCSQLQuerySsql - 2q_.Czqy-PCselect - from 5CI-结果消息ISnoCnoGrde1i, 1 tffllJ80220110001002TB320110002001TB420110002002«0<52011010300130（3 ）在Course表中添加一个平均成绩 avg_Grade字段（记录每门课程的平均成绩），创建 一个触发器Grade_modify，当SC表中的某学生的成绩发生变化时， 则Course表中的平均成绩 也能及时相应的发生改变。alter tabl

11、e Courseadd avg_Gradesmalli ntSQLQueryS.scfl - ZQ+(«iy-PWqy alter table Course L add avg Grade srrallin匸禹消息up dateCourseset avg_Grade =( select命令已成功圭亦AVG( Grade ) from SCwhere SC. Cno=Course . Cno)5QLQuery6.sql - ZQd纠yP0qy| update Courseset avg_Gracie= select AVG Grade) frorc SCLwhere SC.Cno=Co

12、urs£.CnojTrr冷行受影响卩select * from CourseSQLQuery6.£ql - ZQ«.(aqy-PC2qy (52)*select - from Ccuiselit題结果涓息CnoCnameTotal_periorCreditavg_GradeS'1 !001 !高数%G792002c语言程序i殳计305693004Visual_Ba5ic羽4NULLcreatetriggerGrade_modifyon scafterup dateasifup date ( grade )beginup datecourseset avg_

13、grade =( selectfrom scavg ( grade )where course . cno =sc . cnogrou pbyeno )end5QLQuery6.£ql - Zq.(zqy-PCqy (52)* | 曰 create trigger Grade_rr.od.f y on scafter Lipdateasupdate (grade 11beginupdate courseset avg_grade= i(select avg grade)from 3G where course *cno=o group by cno)rrrup dateSCset G

14、rade ='90'where Sno ='20050001'and Cno ='001'SQLQueryfi.sql - 2Q_.(zqy-PCzqy (52)* lipdate 5 Jset Grade- * 90' *-Where 5no-'2011C'001' r.d Cno=*001'fTi门行受影响SnoCnoGrade20110001001201100010027S20110002Ml7S201100020026020110103(MlSO峯AUZ磁/001高数966ao2C语言程序设计805

15、eg004Vt5ual_Basic484AOZ磁£NULLNULLNULLNULLCnoCnameTotal penorCreditflvy Grade中均给出（4 ）测试上述三个触发器。 测试过程在（1）、（ 2 ）、（ 3）2.创建 in stead of（1 ）创建一视图Student_view, 包含学号、Student_view 上创建一个触发器 Grade_moidfy，当对Student_view 中的学生的成绩进行修改 时，实际修改的是sc中的相应记录。create触发器view Student viewasselectfroms . Sno , Sname , c.

16、 Cno , Cname, Grade Students , Course c, SCwheres. Sno =SC. Sno and c. Cno =SC. Cno姓名、课程号、课程名、成绩等属性，在SQLQueryll.sql -乙.(zqy-PCzqy (53)产曰 ereAte view Student viewselect 3. Sno 卞 Snarre, c. Cno, Cname, Grade from SLudenc 3,Course c, 5C-where 5.3no=SC.Sno 鼻:id c,Cno=SC.Cnonr暑消息命令已成功完成Oselect * fromStud

17、ent viewiQLQuerylLql - J (zqy-PCzqy <52)r select * from StLident._viewinSnaSnameCnoCt ameGrade1i 2011100011张虹001高数丸2张虹002c语言程序设计7332O1HXM2'郴 1閃1高数7342011t»02'郴丄002C语言程序设计6052t)l 10103001高数8D结果乙消息createtriggerGrade_moidfyon Student viewin steadofup dateas if UPDATE( Grade ) beginup da

18、teSCset Grade =( selectSno =(Grade fromin sertedwhereselectSno fromin serted)andCno =(selectCno fromin sertedendSQLquery4.sql - Zq(zqy-PCy (5S)* create trigger Gxads_moldf7 on StudentWie關r instead of updateSQLQueryS.sql - ZQ.,.(zasif UPDATE(Gradedbeginupdate 5Cset Grades(select GradeSnQ=(selectCna=

19、I, selectinserted) wherefrom5nc from in吕e工二edH andCno from inserted.#end由消息I命令已成功就up dateStude nt_viewset Grade =40whereSn o ='20110001'and Cn o='002'SQLQueryS.sql - ZQ(zqy*PCzqy (St)*13 Lip date Stiident._vxewset Grade=40-where 3：!=*20110001' ar.d CnG='002 'rrr行受影响）<1

20、行受崑响）select* from Student viewSQLQueryS.sql - ZQ«.Czqy-PCzqy (56)产select - from Student viev；nrSnoShameCnoChameGrade1i 20110001 11张虹001220110001张虹002c语言程设计320110002林红001高数7S420110002林红002c语言程睜设计6052011 QI 03赵青001高数SOselect* fromSC/ £QLQuery3*iql - ZQ,«Ciqy-PCMq)select . from S匚SooCnoG

21、rade120110001j 001902201100010023201100020017乐42011000200360520110103001SOgetcredit字段（记录某学生，所选课程所获学分的情况），创建一个6060（2 ）在SC表中插入一个触发器ins_credit ，当更改（注：含插入时）SC表中的学生成绩时，如果新成绩大于等于 分，则该生可获得这门课的学分，且该学分须与Course表中的值一致；如果新成绩小于分，则该生未能获得学分，修改值为 0。alter table SCadd getcreditsmalli ntSQLQueryS.sql - ZQ-(zqy-PCqy (S

22、0 alter table SCLadd ger credit srrallint由消息命令已成功詫成。select * from SCSQLQueryS.sq - ZQ.<zqy-PC?qy CS6) select * from SC園结果I自消息SCup dateset getcreditcredit from Coursewhere Grade=(selectwhere SC. Cno =Course . Cno)>=60SnoCnoGradegetcredit1J ；201100011r! mi1 XWNULL22011()00100240NULL3201100020017

23、SNULL420110002002eoNULL520110103001aoNULLSQLQueryS.sql - ZQ-(zqy-PCiqy (56)*曰update SC3et getcredit tf select credit froir. Course where SC.Cno=Cour3e.Cno) =where Grade；60PTIup dateSCset getcredit =0 where Grade <60,£QLQuery3,sql - ZQ十倨q 日 update SC3eL geccrsdlL=0 -where Grade<60a消息（I行受影响

24、）SQLQuerya.iql - ZQCzqy*PCiqy (5fiselect r frorr 5C结果in steadofup date 'insertasbegindeclares_no char (8), c_no char (3), new_grade smallint, credsmalli ntends_no=sno , c_no=cno , new_grade =grade from inserted cred =creditif ( new_gradebegindeleteselectselectin sertendelsebegindeletein sertend&

25、gt;=60 )fromintofromintofrom course where eno =c_noSCSCSCSCwherevalueswherevaluessno =s_no and eno =c_no(s_no , c_no , new_grade , cred )sno =s_no and eno =c_no(s_no , c_no , new_grade , 0)SnoCnoGradegdcredit1I 201100011j m1622011000140032(3110002m7E642(3110002002BO552(3110103001SO6createtriggerins_

26、crediton SC曰create trigger ins crediton SCinstead of update,insertas*1 begindeclare 3_no char a8i3c_no chdx(3i rnew_grade smallint,cred smallint select $3_no=3no, 3c_:iQ=cno, tr:ew_(ji；ad.e=grade f rair inserted select cred-credinif (: ne w_g r ade >=60)begindeleteinflertfrom course where cno-c n

27、of IQitintoscscwliere sno 三电 s_no and eno 三® c_nQvalues (3_nOr c_jiq, 0new_grftde, iS'credJend else begindeleteiiisercfrominsSCscwhere 3nQ=3_no a：id. cno=c_no values(3 ne3c 0new grade,0>endjrrw»-Insert into 5C n, ano, eno grade it values t '2O0'SlS0O', "002', S5

28、)t* r1Si消息<0行受影响：u行受影响：壮行受影响】in sertinto SC(sno , eno , grade ) values ( '20081800', '002', 85)*PXselect *1pfrom SC *结果消息1SnoChoGradegetcredit11 20081ISW1 002855 220110001001Ml&3tM2401042)r000200178&5201100020020520110103001EO&(3)测试上述两个触发器。测试结果在(1)、(2)中均已给出3.使用T-SQL语句管

29、理和维护(1 )用系统存储过程sp_helptrigger查看触发器Grade_modify的相关信息sp_help triggerStude nt_view£QLQuery4.sql - ZQ».Ciqy-PCMqy (57)* SQLQuery3,sql - ZQ.Jzqy-PCXzqy (55)八 SQLQi 3p help匸riggervie硏结果I lb消息triggerjiame trigger_owner isupdate isddete isinsert isafter lisinsteadotf tnggerscherria Gr&de_rTioi

30、dl7 I dbo10001dba(2 )使用系统存储过程 sp_he Ip text查看触发器Grade_modify中的定义内容。 sp_help text Grade_moidfy豊QLQiiEry4$ql - ZQiGqy-PCM够(57)尸3p_helpteKt Grade_rEc>dfv色结果lb消息2345e7891011Textcreate tri 理r Gradejnoion StLJdent_viewinstsad of updateasif UPDATE(Gfe)beginupde SCset Grade (select Grade f toe inserted)

31、whereSno=(select Snofrom inserted) andCno=(select Cnofrom inserted)end(3)使用select语句查看触发器 select o. id , c. text from sysobjects o inner on o. id =c. idGrade_modify的定义内容。join syscomme nts cwhere o. type ='TR' and o.n ame ='Grade_modify' select o.idfC.textfroma 丄左咒ei: 2cincn ”igc.idI-

32、where o. type=' TR and . najEe=' Grade moidf y * Isidt戲 I 1S615Sll'670i 1 cTMte trigger Gradejnoidy on SLKlent_view instead oF update as if UP DATEGrade) begin upd I II II m 111 11(4)用系统存储过程sp_depends查看触发器Grade_modify的相关性。 sp_depends Grade_modifySQLQuery4.sql - ZQ(zqy-PCzqy (57)* SQLQuer

33、y.sql -ap depends Grade moidfynametypeupdated鸵融已dcolumn1! dbo.ECI,I user tableJnoyesSno2dbo scuser tablenoyesCno3dbo SCuser tableyesnoGrade4dbo.audentview viewnoyesGrade(5)将 sc_insert触发器改为in stead of触发器，实现的Idroptriggersc_insertSyiyueryi-sql -cirop trigger sc insertn消息命令已成功完成。createtriggersc_inserton

34、 scin steadof in sertasif notexists(select* from student,insertedwhere student. sno=inserted. sno )beginprint ifcourse . eno'插入信息的学号不在学生表中！exists( select * from=inserted. eno )pri nt '插入信息的课程号不在课程表中!rollbacknotcourse , insertedwhereendelsebeginifcourse . enonot exists( select * from course ,

35、 inserted=inserted. eno )wherebeginpri nt '插入信息的课程号不在课程表中!rollbackendendSQLQueryS.sql - ZQ.(zqy-PCzqy (52)* £QLQuery4,sql - ZQ(aqy-PC?qy (57)* SQLQuery呛ql - ZQ,.(zqy-PC; r cteacenc_in3erton j-sInjcead of Insertaanot eKisTLS iiselect ” frarr. student insertedwhere student,3nG=in3erted,ano)A

36、ifbeginP-n. 插入信息的学号不在学生表中！if ncE exists (select " from cciu工ssrinat工ted where cqu工se cnQ=inse工ted. nno print，插入信息的课程号不在课程表41!-rollbackA5TBendelsebeginif n2beginexists(select * from course,Inaerted where o=inserteil,3nQpt丄匸插入信息的课程号不在课程表中！，rollbackendend!Er匕満S命令已成功完成4in sertinto SCvalues ('20

37、110005', '001',78', '6')£QLQuery5.£ql - ZQ»*(iqy-PCzqy52)叮 SQLQu已 insert into SCLvalues C20110005' , "001丁S' , *6* pEj消息插入信息的学号不在学生表中！ 消息级别血状态1,第1行 事务在触发器中结束。批蛙理已中止.(6)将触发器sc_insert删除。drop triggersc_in sertSQLQueryS.sql - ZQ.M(zqy-PCzqy (52) drop t

38、rigger sc inseruLj消鳥命令已成功完成。4.使用 SQL Server Management Studio 管理存储过程(1 )在SQL Server Management Studio中重新创建刚删除的触发器sc_insert选中SC表，展开7右击“触发器”7新建触发器I龙#這瘵苣世器I副戲Ia 口E (J StudentjirfoI0 ra谀岸羔至圉0 L3'回国至如3 dbo.Coure3 dbo.SCmr*'mXffl国列Sf口口口 Q 口規LQueryg.fiql -drop匸fl灯口亡J消息I命令已成功完成*HSO Pownr&hsllCH)

39、n dbo Stud db&SUidiijUJ用獅F田Lu昭 l同空伺出现如下界面:- Author:<Author,-Create date: <Create Date,- Description: <I>esciiptiaii, >日 CREATE TRIGGER <5c匕eisa 耳ar.己” sysnaic&r ScEeDi£_HaifEA.<Trigge£_NaiLF Bysname, T工iggej H趣亡> I ON < 3 ckeiai_Naiiie, syanarr.e, 5ei:eii

40、Li_Nsiae>. <T able_Naite,air.e, Tafcle_Naine;-n AKTER <Data_Mfldi£icflT：iDn_ScflceiQffnc3j , INSERTDELETEUPDATEASBESItl-SET NOCOUHT ON added to pi-event extra result sets frojn-interfering with SELECT statementa,SET NOCODNT ON:-VW-Insert stateir.enta for trigger hreL£HDGO编写余下的SQL语

41、句:CREATE TRIGGER sc insertON SCINSTEADOF INSERTASBEGINif notexists( select* fromstudent, insertedwherestudent. sno =inserted . sno )beginprint ifnotcourse . eno'插入信息的学号不在学生表中!exists( select* from course , insertedwhere=inserted. eno )pri nt'插入信息的课程号不在课程表中!rollbackend elsebeginif not exists( select* from course , insertedwherecourse . eno =inserted . eno )begi npri nt rollback'插入信息的课程号不在课程表中!endendEND曰 CREATE TRIGGER sc.inse工tOW 5CINSTEAD OF INSERTA5