数据库实验6触发器.doc
实验6 触发器一、实验目的1了解触发器的基本概念和功能。掌握触发器的创建和使用方法。二、相关知识创建触发器的语法为：CREATE TRIGGER <触发器名> ON <表名>WITH ENCRYPTIONFOR DELETE，INSERT，UPDATEWITH APPENDNOT FOR REPLICATIONAS <SQL 语句组>其中：1）WITH ENCRYPTION 为加密选项。2）DELETE 选项为创建DELETE 触发器。DELETE触发器的作用是当对表执行DELETE操作时触发器被激活，并从指定表中删除元组，同时将删除的元组放入一个特殊的逻辑表（delete表）中。触发器的动作可以检查delete表中的数据，以确定下一步该如何处理。3）INSERT选项为创建INSERT触发器。INSERT触发器在对指定表中执行插入数据操作时激活，激活后将插入表中的数据拷贝并送入一个特殊的逻辑表（inserted 表）中，触发器会根据INSERT表中的值决定如何处理。4）UPDATE选项为创建UPDATE触发器。UPDATE触发器仅在对指定表中进行更新数据操作时激活。UPDATE触发器激活后把将要被更新的原数据移入delete表中再将要被更新后的新数据的备份送入insert表中，UPDATE触发器对delete和inserted表进行检查，并决定如何处理。5）NOT FOR REPLICATION 选项说明当一个复制过程在修改一个触发器表时，与该表相关联的触发器不能被执行。触发器是一种特殊类型的存储过程，用来保证数据完整性，当对它所保护数据进行插入、修改和删除时自动激活，对改变的数据进行检查，以防止对数据进行不正确、未授权或不一致的修改。一个触发器只适用于一个表，每个表最多只能有三个触发器，它们分别是INSERT、UPDATE和DELETE触发器。触发器仅在实施数据完整性和处理业务规则时使用。有关更详细的信息，感兴趣的同学可以在帮助中搜索主题。三、实验内容（一）简单触发器练习：1用SQL Server Management Studio创建触发器，当学生表S中有新的记录插入时，提示用户在选课表SC中加入相应的选课记录。具体步骤：1）打开SQL Server Management Studio扩展到学生选课数据库，选择S表触发器，右键单击，选择“新建触发器”，出现的触发器Transact-SQL语句输入窗口:CREATE TRIGGER TIGGER_S ON S FOR INSERT ASBEGINPRINT 学生表S中有新的同学加入，别忘了给他增加选课记录END参考下图1【注意下图中表名是中文，比如“学生”，我们这里用英文S】。图1触发器TransactSQL语句输入窗口2）在文本框中输入创建触发器的Transact-SQL语句（语句见上图），单击“检查语法”按钮进行语法的检查，检查无误后，单击“！执行”按钮。3）打开查询分析器，在顶部的下拉框中选中“学生选课”数据库，运行INSERT语句在学生表中加入一条学生记录，如：INSERT INTO SVALUES(S012,刘东,男,22, ,D2); 验证触发器，看看会出现什么结果？4）创建好以后还可以在名字弹出项中选择要修改的触发器进行修改或删除。（二）绩点计算【特别注意，以下实验语句可以参考bb平台上的补充课件T-SQL触发器实验参考资料】绩点计算方法请参考本文档最后面。（1）课程学分绩点Grade的自动更新1）添加1列：用alter table add 语句给选课表SC增加一个属性：课程学分绩点Grade, 数据类型为 numeric(4,1)；alter table sc add Grade numeric(4,1)2）创建一个触发器：当学生的某一门课的成绩有变化时（update）自动修改Grade值，并进行验证，采用after触发器create trigger upd_Grade on SCafter update asif update(Score) -使用update(列)函数表示修改了某列的值begin if (select Score from inserted)< 60) -成绩小于，绩点为update SC set Grade = 0from insertedwhere SC.Snum = inserted.Snum and SC.Cnum = inserted.Cnum and inserted.Score is not null -第一二个等式表示更新指定学生+课程的Grade，否则所有的行都更新了else update SC set Grade = (inserted.Score * 0.1 - 5)*C.CfreqFROM C,inserted -因为要用到学分，所以需要课程表C，注意这里from的用法where SC.Snum = inserted.Snum and SC.Cnum = inserted.Cnum and C.Cnum = inserted.Cnum and inserted.Score is not null -第一二个等式表示更新指定学生+课程的Grade，第三个等式表示和C表进行连接，否则所有行都更新End注意：在update 触发器中修改后的数据从inserted表中读取，修改前的数据从deleted 表中读取3）验证：update SC set Score = 78 where Cnum = 'C003' and Snum = ''SELECT * FROM SC看看结果update SC set Score = 78 where Cnum = 'C003' and Snum = ''SELECT * FROM SC看看结果4）删除最后如果不需要这个触发器了，删除：drop trigger upd_Grade5）仿照上面的例子，请写出insert触发器int_Grade，当插入一行时（插入的时候绩点还不知道，为null），自动更新该行的绩点Grade，写出创建触发器语句，写出验证语句，写出删除语句【上交语句】（2）平均学分绩点AvgGrade1）添加1列：用alter table add 语句给学生表S增加一个属性：平均学分绩点AvgGrade, 数据类型为 numeric(4,1)；【上交语句】2）请写出insert触发器，当插入一行到SC表时，自动修改对应学生表S中该学生的 AvgGrade值，并进行验证create trigger int_AvgGrade on SCafter INSERT asdeclare nSnum varchar(10) -插入行学生的学号declare sumGrade numeric(5,1) -插入行学生的总学分绩点declare sumFreq numeric(5,1) -插入行学生的总学分数begin select nSnum = Snum from inserted -获得学生的学号select sumGrade = sum(Grade) from SC -插入行学生的总学分绩点where SC.Snum = nSnumselect sumFreq = sum(Cfreq) from SC,C -获得该学生的总学分数where SC.Snum = nSnum and SC.Cnum = C.Cnumif (sumFreq <> 0) -除数不能为0update S set AvgGrade = sumGrade/sumFreq -更新到S表where S.Snum = nSnum end3）验证：INSERT INTO SC VALUES('','C003',70,NULL)SELECT * FROM SCselect * from S4）仿照上面的例子，请写出update触发器udp_AvgGrade，当修改SC一行时，自动更新该学生对应学生表S的平均学分绩点AvgGrade，写出创建触发器语句，写出验证语句，写出删除语句【上交语句】四、实验要求1. 上机完成以上步骤。2. 查阅一些关于触发器的资料，对触发器做更深层次的了解。五、注意事项触发器内不能使用如下SQL命令：所有数据库对象的生成命令，如CREATE TABLE等。所有数据库对象结构修改，如ALTER TABLE等。创建临时表。DROP，GRANT，REVOLE命令。TRUNCATE TABLE，LOAD DATABASE，LOAD TRANSACTION命令。RECONFIGURE命令。六、思考题什么是触发器？主要功能是什么？课程的学分绩点和平均学分绩点计算办法 一、课程绩点的计算百分制记分的课程绩点（K）= 课程考核成绩（X）×0.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 二、课程学分绩点=课程绩点×课程学分。三、平均学分绩点 =课程学分绩点/所学课程学分平均学分绩点是衡量学生学习质量的主要指标。平均学分绩点保留小数点后1位。
