《数据库实验6触发器》由会员分享，可在线阅读，更多相关《数据库实验6触发器（5页珍藏版）》请在金锄头文库上搜索。
1、实验 6 触发器一、实验目的1了解触发器的基本概念和功能。2.掌握触发器的创建和使用方法。二、相关知识创建触发器的语法为：CREATE TRIGGER 触发器名 ON 表名WITH ENCRYPTIONFOR DELETE，INSERT，UPDATEWITH APPENDNOT FOR REPLICATIONAS SQL 语句组其中：1)WITH ENCRYPTION 为加密选项。2) DELETE 选项为创建 DELETE 触发器。 DELETE 触发器的作用是当对表执行 DELETE 操作时触发器被激活，并从指定表中删除元组，同时将删除的元组放入一个特殊的 逻辑表(delete表)中。触发
2、器的动作可以检查delete表中的数据，以确定下一步该如何处 理。3) INSERT选项为创建INSERT触发器。INSERT触发器在对指定表中执行插入数据操 作时激活，激活后将插入表中的数据拷贝并送入一个特殊的逻辑表(inserted表)中，触发 器会根据INSERT表中的值决定如何处理。4) UPDATE选项为创建UPDATE触发器。UPDATE触发器仅在对指定表中进行更新数 据操作时激活0UPDATE触发器激活后把将要被更新的原数据移入delete表中再将要被更新 后的新数据的备份送入insert表中，UPDATE触发器对delete和inserted表进行检查，并决 定如何处理。5)
3、NOT FOR REPLICATION 选项说明当一个复制过程在修改一个触发器表时，与该表 相关联的触发器不能被执行。触发器是一种特殊类型的存储过程，用来保证数据完整性，当对它所保护数据进行插入、 修改和删除时自动激活，对改变的数据进行检查，以防止对数据进行不正确、未授权或不一 致的修改。一个触发器只适用于一个表，每个表最多只能有三个触发器，它们分别是 INSERT、UPDATE和DELETE触发器。触发器仅在实施数据完整性和处理业务规则时使用。 有关更详细的信息，感兴趣的同学可以在帮助中搜索主题。三、实验内容(一)简单触发器练习：1用SQL Server Management Studio创
4、建触发器，当学生表S中有新的记录插入时，提 示用户在选课表SC中加入相应的选课记录。具体步骤：1)打开SQL Server Management Studio扩展到学生选课数据库，选择S表f触发器， 右键单击，选择“新建触发器”出现的触发器Transact-SQL语句输入窗口：CREATE TRIGGER TIGGER_S ON SFORINSERTASBEGINPRINT 学生表 S 中有新的同学加入，别忘了给他增加选课记录END参考下图1【注意下图中表名是中文，比如“学生”，我们这里用英文S】._ i吳 Microsoft SQL Server Management Studio文件旧编辑
5、（E观圏凹查诃CQ）项目（B工具CD茵口险社区 帮助凹Gj 限魄巒 l5 占电El 2 2 5* -理也肚学珅” | #执行凶“霜曙|忆|翦 异嗨胃館匮劄匚二丨辜宰壬田口家融腸库 田口数摇库快照AdventureWorksAdventu reWorks DW Northwind pubs学J J J J J田田田田日田口 魏居库关系囹日口表田口系统表田二 dbo.-H0二dbo.选课E二日血学生13 口列田口適 is 口的束 口竝器 口索引0 口统计信息G U AN WEI- QLQueryl4.sql* XCREATE TRIGGER TRIGGER_ ONFOR INSERTAS*BEGI
6、NPRHNT，学生表中有新的同学加人，别忘了给也増加选课记录，ENDGOE） 口观囹 |UI卜已连接。GUABYfEI-FC (9.0 SF3) sa C52) 子生选 00:00:000 仃ft 6Ch 3图 1 触发器 Transact SQL 语句输入窗口2）在文本框中输入创建触发器的Transact-SQL语句（语句见上图），单击“检查语法” 按钮进行语法的检查，检查无误后，单击“！执行”按钮。3）打开查询分析器，在顶部的下拉框中选中“学生选课”数据库，运行INSERT语句在 学生表中加入一条学生记录，如：INSERT INTO SVALUES（S012，刘东,男,22, 869188
7、42,D2）; 验证触发器，看看会出现什么结果？4）创建好以后还可以在名字弹出项中选择要修改的触发器进行修改或删除。（二）绩点计算【特别注意，以下实验语句可以参考bb平台上的补充课件T-SQL触发器实验参考资料】 绩点计算方法请参考本文档最后面。（1）课程学分绩点Grade的自动更新1 ）添加 1 列：用 alter table add 语句给选课表 SC 增加一个属性：课程学分绩点 Grade, 数据类型为 numeric（4,1）；alter table scadd Grade numeric(4,1)2) 创建一个触发器：当学生的某一门课的成绩有变化时(update)自动修改Grade值
8、, 并进行验证，采用after触发器create trigger upd_Grade on SCafter updateasif update (Score) -使用update (列)函数表示修改了某列的值beginif (select Score from inserted) 60) -成绩小于，绩点为update SC set Grade = 0from insertedwhere SC.Snum = inserted.Snumand SC.Cnum = inserted.Cnum and inserted.Score is not null-第一二个等式表示更新指定学生+课程的Grad
9、e，否则所有的行都更新了 elseupdate SC set Grade = (inserted.Score * 0.1 - 5)*C.CfreqFROM C, inserted 一因为要用到学分，所以需要课程表C,注意这里from的用法where SC.Snum= inserted.Snum and SC.Cnum = inserted.Cnumand C.Cnum = inserted.Cnum and inserted.Score is not null-第一二个等式表示更新指定学生+课程的Grade，第三个等式表示和C表进行连接，否则所有行都更新 End注意：在 update 触发器中
10、修改后的数据从inserted表中读取, 修改前的数据从 deleted 表中读取3) 验证：C003 and Snum = 0903330002C003 and Snum = 0903330002update SC set Score = 78 where CnumSELECT *FROM SC看看结果update SC set Score = 78 where CnumSELECT *FROM SC看看结果4）删除 最后如果不需要这个触发器了，删除 droptrigger upd_Grade5）仿照上面的例子，请写出insert触发器int_Grade，当插入一行时（插入的时候绩点还 不知
11、道，为null），自动更新该行的绩点Grade，写出创建触发器语句，写出验证语句，写出 删除语句【上交语句】(2)平均学分绩点 AvgGrade1) 添加 1 列：用alter table . add.语句给学生表S增加一个属性：平均学分绩点AvgGrade,数据类型为 numeric(4,1)；【上交语句】2) 请写出insert触发器，当插入一行到SC表时，自动修改对应学生表S中该学生的 AvgGrade 值，并进行验证create trigger int_AvgGrade on SC after INSERTasdeclare nSnum varchar(IO)插入行学生的学号decla
12、re sumGrade numeric (5,1)插入行学生的总学分绩点declare sumFreq numeric (5,1)插入行学生的总学分数beginselect nSnum = Snum from inserted 获得学生的学号select sumGrade = sum (Grade) from SC -插入行学生的总学分绩点where SC.Snum = nSnumselect sumFreq = sum (Cfreq) from SC, C 一获得该学生的总学分数where SC.Snum = nSnumand SC.Cnum= C.Cnumif ( sumFreq 0 )-
13、除数不能为0update S set AvgGrade = sumGrade/sumFreq -更新腹表where S.Snum = nSnumend3) 验证：INSERT INTO SC VALUES(0903330002,C003,70,NULL)SELECT *FROM SCselect *from S4) 仿照上面的例子，请写出update触发器udp_AvgGrade，当修改SC 行时，自动 更新该学生对应学生表S的平均学分绩点AvgGrade，写出创建触发器语句，写出验证语句， 写出删除语句【上交语句】四、实验要求1. 上机完成以上步骤。2. 查阅一些关于触发器的资料，对触发器做
14、更深层次的了解。五、注意事项触发器内不能使用如下SQL命令：所有数据库对象的生成命令，如CREATE TABLE 等。所有数据库对象结构修改，如ALTER TABLE等。创建临时表。DROP, GRANT， REVOLE 命令。TRUNCATE TABLE LOAD DATABASE LOAD TRANSACTION 命令。 RECONFIGURE 命令。六、思考题什么是触发器？主要功能是什么？课程的学分绩点和平均学分绩点计算办法一、课程绩点的计算百分制记分的课程绩点（K）=课程考核成绩（X） X0.15，即：课程考核成绩（X ）90-10080-89.970-79.960-69.960分以下课程绩点（K）4.0-5.03.0-3.92.0-2.91.0-1.90等级制记分的课程绩点为：五级制课程考核成绩优秀良好中等及格不及格课程绩点4.53.52.51.50二级制课程考核成绩合格不合格课程绩点2.50、课程学分绩点=课程绩点x课程学分。三、平均学分绩点=工课程学分绩点/工所学课程学分平均学分绩点是衡量学生学习质量的主要指标。平均学分绩点保留小数点后 1 位。
