实验七触发器.pdf

《实验七触发器.pdf》由会员分享，可在线阅读，更多相关《实验七触发器.pdf（10页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验七、触发器 一、实验目的（1）理解触发器的用途、类型和工作原理。（2）掌握利用 T-SQL 语句创建和维护触发器的方法。（3）掌握利用 SQL Server Management Studio 创建、维护 触发器的方法。二、实验内容、1、创建 AFTER 触发器（1）创建一个在插入时触发的触发器 sc_insert，当向 SC 表插入数 据时，须确保插入的学号已在 Student 表中存在，并且还须确保插入 课程号在 Course 表中存在；若不存在，则给出相应的提示信息，并 取消插入操作，提示信息要求指明插入信息是学号不满足条件还是课 程号不满足条件。（注：Student 表与 SC 表

2、的外键约束要先取消。）if exz_Bt3-.select name from Bysobjects where name=j_r.Bert1 注二d type-TR i drap trigger sc_nser 50 create trigger 3C_inserc an sc for insert S3 declare 0sro varchar 110 Jcno varcriar 10：select sno-sno,cnccno from INSERTED if not exists(select sno frorti student where 3no=snd fcegin p工二口七

3、1 学号不存在！T rollback tran erd|ELSE PRINT OR1 else if no匸 cixz-Bts select eno from course where cno=cno begin rcllb&ck tr&n p工二口七 课程号不存在!1 j end go J消息 命令已成功完戚.(2)为 Course 表创建一个触发器 Course_del,当删除了 Course 表 中的一条课程信息时，同时将表 SC 中相应的学生选课记录也删除。if exists(select name from 3ysobjsees where name-1course_del1 and

4、 typeTR1)drep trigger 匚口口rse_del go create trigger course_del on ccurse far DELETE AS tegin transec匸丄口口 declare ano char 8)select 茵匚口o=cno from DELETED DELETE FROM SC whe工己 cno=cno commit tr&n (3)在 Course 表中添加一个平均成绩 avg_modify，当 SC 表中某学 生的成绩发生变化时，则 Course 表中的平均成绩也能及时相应发生 改变。alter table course add av

5、ggrade int/*alcer 匸able course drop columrL avg_grade*/if exists:select name from sy3objects where nazue=l grade_raodif y1 and type：=lTR.drop 匸rigger grade_nioLfy go create txiggex grade_iMdify an sc for update as decla工皂 avg int.7 cno char 2 select CE口=eno P avg=avg(grade frojn INSERTED group by en

6、o UPDATE course set.avg_grade=电avg where cno=cno(4)测试上述三个触发器 insert into sc values(20110009,*001,90|S消息 聲号 ife息3509,级别1状态 S 第丄行 事务在触发器中结束批处理已中止。insert into sc values/,2O110Q02,rQ01,90 select _ from sc;口结果血消息 Snc Cno Grade j L,E-F,f，1 j2011DD01 001 39 2“面了伍亦D02 7S 3 2D11D001 D03 出 4 2011D001 DM NULL

7、5 20110002 001 NULL 20110002 002 60 7 20110002 003 NULL 6 20110002 004 W delete from Course where cna=1002*;_ HLEO 4LD Sno Cno Grade 卜 003 39 20110001 004 NULL 20110002 003 AUZ 20110002 004 90 20110003 003 37 20110003 004 75 MJ.NULL MULL Cname Total _peror Credit r-二幵士丑口*jRh 3*1丁筑语言程序设计 J：suaJtiaSC

8、斗 英语 64 斗 数据库 羽 3 NULL NULL MJLL Jpda匸亡3C ser grade:S0 where cno=*33;updare sc set grade gradel wkere cno=11J4*;匕消息:1行受彫响1（3行受彫响）警告：聚合或其他ST操作消除了空值”（L行受彫响）3行受影响）1 _ 6。Cname Totaljjerior Credit avg_grade 鹿 JBM语言程序 3 3Q 004 Viiual_Basic 羽 4 90 00 S 英语 S4 斗 At2 006 数据库 43 3 AUZ NULL NLIL NHL AUZ MJLL 2、

9、创建 INSERTED OF 触发器（1）创建一视图 student_view,包含学号、姓名、课程号、课程名、成绩等属性，在 student_view 上创建一个触发器 grade_modify,当 对student_view 中的学生的成绩进行修改时，实际修改的是 SC 中的 相应记录。if exists(select nwae from sysobjects where：name=1 student_view*且nd typ=lV*)DROP VIEW szudenxvlew go create view student view as sei皂匚芯 studen匸 snor sname

10、.sc cnQ,cname.grade f r ain scf 匚口口工于皂*student where sc-ano studentBsno and occurse.eno;if exists i select rma.已 from sysobjects wti皂工e：niame=1 grade_Eiodify11 且nd typ皂=TR)drop trigger giraderaodify go create trigger grade_iRQdify on student view INSTEAD OFUPDATE A5 DECLARE grade int select grade-gr

11、ade froin INSERTED update 3tudent_view set grade=grade;|（2）在 Student 表中插入一个 getcredit 字段（记录学生所获学分 的情况），创建一个触发器 ins_credit，当更改（注：含插入时）SC 表中的学生成绩时，如果新成绩大于等于 60 分，则该生可获得这门 课的学分，如果新成绩小于 60 分，则该生未能获得这门课的学分。（3）测试上述两个触发器。update 5tdent_view set grade-2?:Sno Cna Grade 003 3S 2C110001 004 33 20110002 003 sa 2

12、0110002 004 as 2C110003 003 as 20110003 004 as 米 NULL Am NULL 3、使用 T-SQL 语句管理和维护触发器(1)用系统存储过程 sp_helptrigger 查看触发器 grade_modify 的 相关信息 EXEC sp heLptrigger r stden匸 view1;s G 1菇果&消息 trigger_nam&trigger_07kTFer isupdate isdslets isinserl isafter isinsteadof tngger_3chenna 1 i grade_nnciclify dbo 1 0 0

13、 0 1 dbo (2)使用 sp_helptext 查看触发器 grade_modify 中的定义内容 exec lptext y 型=ZZZ=ZZ 二结果 J消息 Text 1:create trigger grade _modify:2 on stjderrt_view 3 INSTEAD OF UPDATE 4 AS 5 DECLARE grade int E select grade=grade from INSERTED&update studentview 9 set gade=grade；(3)使用 select 语句查看触发器 grade_modify 的定义内容 SELEC

14、T 亠 ETRCH r ByscoraraeDts MEKE type-TR-azid naw gratiE_iiod 1 f yp;M _ u _ 1 _ J 1占朋 det t-fiXttflM language fincWBd ccr*ipfssid LeM A 0 1 0 ftB)05B0050FQ073ffl)RQ053MKF 畑 WK5C05D0020 2 0 a 0 iJPiMia昶呛 lMlQ-9|IMR9|Q-9!lJ-9r)5 D 1 0 血泊瞒XK桥畑0M006300詹畑 DO&KOSDOCEI.2 0 打 0 ilPmsio詔时帕 lMD-9|lQ-?lD-9|0-9i

15、a-9n S fl i 0 Ch2m5SMM细 ElMWiTMKTKieKKffia&KHW 如 2 0 a 0 i Entrance _d aLe p|Brthll 7 百 i D M3D0 7200650(胞 100”00650021&140皿5004風)041005開.2 0 a 0 errfe DEFMJLT Grade as U D i D k4352im5fflM 畑540045002000；106戏便50(771200.2 D a 匚RETE VWN siLArs2 ES selecl cno,cnHmeBcredtE.打 1 D Ds4352Oi5DG41CO54 004500

16、2 W56QM3CG150M7KI2CK.2 0 0 匚REATE VIEWstuv 1 as sded Sno.Snamt Sec,.疋 0 1 RS3M721M1MZfl0KaKL2WD7fflM9aK5OT77DO2M 2 0 5ts wsfM stuvewl 35 sd1 SC Sna D.Grade f 11 fl n D EkSXKITOli 瞬 1Q?74IX5Cia2W7m9S0(l77CW 2 Q fl-0 cnjte vwi shJv-eMiisns.wianitjSidii M sded a 12 1 1 0 2 0 护 Q CREATE JWK*加 stu_pr 9 A

17、*1*4*亠!J 1 U 1 1 0 卯 QQ650aQTO0720afM3Q065D 2 Q Q CWtmK4du 5hJ_p*UCl 刍:；:；V.(4)用系统存储过程 sp_depends 查看触发器 grade_modify 的相关 性(即该触发器涉及哪些基本表)。USE Stdent_info-指定数据库 EXEC sp depends 0objname=rade ncdLfy1;両结果區消息 name type updated selected column!i in 1 dbo.5tudent_view view 仰 no grade RhaiaivmiBiBarainiaiam

18、HiaiMiainraiaiBiiaif(5)将 sc_in sert 触发器改为 in stead of 触发器，实现的功能不变。if existsiselect name from sysabjects wnere nane=T sc_3ert ard typ已=，工罠，drop trigger sc_inserr go on SG INSTEAD OF INSERT as declare Sane varchar(10 r cno varchar(10)select.sno ano.3cno eno frem INSERTED if net exis匸巴(select ano frein

19、 stndent wnere snQ=snoi begin print 学号不存在|rollback tran end|(6)将触发器 sc_insert 删除。DROP TRIGGER 3c_in3ert;|4、使用 SQL Server Management Studio 管理触发器(1)在 SQL Server Management Studio 中重新创建刚删除的触发 器 sc_insert。CREATE TRIGGER sc_inseru ON sc INSTEAD OF INSERT AS BEGIN declare varchai(10 5 varchar10 selecc sn

20、o=o=cno from INSERTED if i:匸 ts i select ano from et;udenr wr:er&3no_sno begin prz.口匸 学号不存在 rollback tran end.EHD GO _J 消息 命令已咸功完成.(2)查看触发器 sc_insert 的内容 75E 5tudent_info GO/*对跟：Trigger dbo sc_ln3ert 脚本日期:SET AN名工_NULLS ON GO SET QUOTED_IDENTI FIER ON GO Author:Create date:-Descript1on;ALTER TRIGGER dbo(5C_inser匸 OM|dlDO).SC INSTEAD OF INSERT AS BEGIN declare 3no varchaz 10ifcno varchar 10：|select 5BO=SBQ eno=GHO 11/19/2013 19:02;37*from INSERTED xf not exists sno from student where sno=3noi begin print(学号不存在I)rollback trail end END(3)删除触发器 sc_insert 要 W 除的对象(Q)对線类型 姑有者状态 DU 11 B