实验D007： 事务处理和触发器实验

实验D007： 事务处理和触发器实验一、实验名称和性质所属课程数据库原理 实验名称事务处理和触发器实验学时2实验性质验证 综合 设计必做/选做必做 选做二、实验目的1了解事务的概念和基本特征。2掌握事务的提交与回滚操作。3掌握触发器的基本原理。4掌握触发器的建立语句。 三、实验的软硬件环境要求硬件环境要求：PC机（单机）。使用的软件名称、版本号以及模块：Windows XP下的SQL Server 2005（或 SQL Server 2008）。四、知识准备1事务是一段与数据库打交道的程序，它能保持企业状态和数据库状态的一致性。当某一事件影响企业状态时，事务会更新数据库状态以体现这个事件的发生。如银行里的存款事务，事件是客户向出纳员提供现金和存款单，事务则是更新数据库中客户的帐号信息以体现这次存款事件。2事务处理具有原子性(atomicity)：系统必须保证这段程序要么执行到结束，要么就一点效果也没有。如果一个事务成功执行，我们称其已经提交（commit)；一个事务没有正常完成，称其已经中止（abort)，这时候，事务监视器有责任保证事物对数据库造成的部分改变要修改回来，这就叫事务回滚(roll back)3触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。q在使用触发器过程中，SQL Server使用到了两张特殊的临时表，分别是inserted和deleted表。（1）在deleted表中存放Update和delete影响的旧数据行。在执行Update和delete时先将数据从基本表中删除，然后被转移到deleted表中。（2）在inserted表中存放Update和insert影响的数据。当用户执行Update和insert时，将数据添加到基本表中，同时，将数据行的备份复制到inserted临时表中。4触发器的创建格式CREATE TRIGGER 触发器名 ON 表名AFTER INSERT/UPDATE/DELETEAS T-SQL语句组五、实验内容1事务的提交与回滚操作。2触发器的建立。3触发器的修改、删除操作。4触发器的删除。六、验证性实验1用ROLLBACK TRAN实现事务的回滚操作。USE STUDENTGOBEGIN TRAN SELECT * FROM CINSERT INTO C VALUES(C08, 决策支持系统, NULL, 3)SELECT * FROM CROLLBACK TRANSELECT * FROM C观察每次显示C表中数据记录条数变化，为什么有这种变化？CNO CNAME cpno credit- - - -c01 数据库原理 c03 4c02 信息系统 c01 4c03 数据结构 NULL 6c04 DB_设计 c01 3CNO CNAME cpno credit- - - -c01 数据库原理 c03 4c02 信息系统 c01 4c03 数据结构 NULL 6c04 DB_设计 c01 3C08 决策支持系统 NULL 3CNO CNAME cpno credit- - - -c01 数据库原理 c03 4c02 信息系统 c01 4c03 数据结构 NULL 6c04 DB_设计 c01 3分析：语句“NSERT INTO C VALUES(C08, 决策支持系统, NULL, 3)”的作用是在表上增添信息(C08, 决策支持系统, NULL, 3)；语句“ROLLBACK TRAN”的作用是回滚，就是撤销先前准备做的事情2 事务回滚点的保存USE STUDENTGO BEGIN TRAN MytranSELECT * FROM CINSERT INTO C VALUES(C09, 信息分析与预测, NULL,4)SAVE TRAN POINT1INSERT INTO C VALUES(C10, 数据挖掘, NULL, 3)SELECT * FROM CROLLBACK TRAN POINT1SELECT * FROM C观察最后的C表中的数据结果与前面C表显示结果的不同。CNO CNAME cpno credit- - - -c01 数据库原理 c03 4c02 信息系统 c01 4c03 数据结构 NULL 6c04 DB_设计 c01 3CNO CNAME cpno credit- - - -c01 数据库原理 c03 4c02 信息系统 c01 4c03 数据结构 NULL 6c04 DB_设计 c01 3C09 信息分析与预测 NULL 4C10 数据挖掘 NULL 3CNO CNAME cpno credit- - - -c01 数据库原理 c03 4c02 信息系统 c01 4c03 数据结构 NULL 6c04 DB_设计 c01 3C09 信息分析与预测 NULL 4分析：只删去了“c10”部分的数据。语句“SAVE TRAN POINT1”相当于保存了POINGT1时候的状态，“ROLLBACK TRAN POINT1”的作用是回滚到POINGT1时候的状态。因此，删去了“c09”部分的数据，删去了“c10”部分的数据。3触发器的建立（1） 在S表建立建立如下触发器CREATE TRIGGER TRIGONSON S FOR UPDATEASSELECT * FROM DELETEDSELECT * FROM INSERTED选中上述触发器创建语句并执行，然后输入下面语句：UPDATE S DELETED表SET DEPA =计算机系 WHERE DEPA=CP 显示结果：INSERTED表（2）在S表上建立下面触发器：CREATE TRIGGER TEMPTABLEON S FOR INSERTASSELECT * FROM INSERTEDROLLBACK TRAN执行上述触发器后在S表中插入数据，INSERT INTO S（SNO，SNAME） VALUES（10012，ZXX）执行插入语句后打开S观察结果。SNO SNAME DEPA AGE SEX- - - - -10012 ZXX NULL NULL NULL(1 行受影响)消息3609，级别16，状态1，第1 行事务在触发器中结束。批处理已中止。4在SC表上插入一条选课记录后，将C表中对应的课程的seleNUM值加1。（认真完成此题，注意每一步的含义）Alter table c add seleNUM int- 在C表增加一个字段goUpdate cSet selenum=(select count(sno) from sc where cno=o)- C表上的SELENUM的值的填入（根据SC表选课的情况)goselect cno,selenum from c-查看C表上每门课的选课人数goCREATE TRIGGER SELECTCOURSEON SC FOR INSERTASIF (SELECT COUNT(*) FROM INSERTED,sc where sc.sno=inserted.sno and o=o)1 -如果SC表中某个学生已有这门课的选课记录BEGIN PRINT YOU CAN INSERT ONE RECORD ONCE. SO THE RECORDS ARE NOT INSERTED INTO THE TABLE ROLLBACK TRANENDELSEBEGIN DECLARE CNO VARCHAR(10) SELECT CNO=CNO FROM INSERTED If ( select seleNum from c where cno=cno) is null Begin Update c set seleNum=0 where cno=cno end UPDATE C SET seleNUM=selenum+1 WHERE CNO=CNO-C表中根据刚插入的选课记录中的课程号，选课人数增加 PRINT THE NUMBER OF STUDENTS WHO SELECTED+CNO + HAS BEENODDEDENDGOinsert scvalues(200512,c02,80)-此记录被拒goinsert scvalues(200912,c02,90)-此次记录成功插入SCselect * from c-查看C表中的记录，看看C02的选课人数是否加GOinsert c(cno,cname,credit)values(c05,操作系统,4) )-C表中插入一条记录（原来没有这门课的）goselect * from c-注意观察C05的选课人数为null goinsert scvalues(200912,c05,90)goselect * from c -再次观察结果(4 行受影响)cno selenum- -c01 4c02 4c03 4c04 2YOU CAN INSERT ONE RECORD ONCE. SO THE RECORDS ARE NOT INSERTED INTO THE TABLE消息3609，级别16，状态1，第1 行事务在触发器中结束。批处理已中止。(1 行受影响)THE NUMBER OF STUDENTS WHO SELECTEDc02 HAS BEENODDED(1 行受影响)CNO CNAME cpno credit seleNUM- - - - -c01 数据库原理 c03 4 4c02 信息系统 c01 4 5c03 数据结构 NULL 6 4c04 DB_设计 c01 3 2消息102，级别15，状态1，第2 行) 附近有语法错误。CNO CNAME cpno credit seleNUM- - - - -c01 数据库原理 c03 4 4c02 信息系统 c01 4 5c03 数据结构 NULL 6 4c04 DB_设计 c01 3 2(4 行受影响)(0 行受影响)THE NUMBER OF STUDENTS WHO SELECTEDc05 HAS BEENODDED(1 行受影响)CNO CNAME cpno credit seleNUM- - - - -c01 数据库原理 c03 4 4c02 信息系统 c01 4 5c03 数据结构 NULL 6 4c04 DB_设计 c01 3 2(4 行受影响)5触发器的修改、删除（1） 修改触发器ALTER TRIGGER 触发器名 ON 表名AFTER INSERT/UPDATE/DELETEAS T-SQL语句组在调试触发器时可以用修改触发器的语句来进行。如 CREATE TRIGGER DELS ON S FOR DELETE ASDECLARE DEPA VARCHAR（20）SELECT DEPA=DEPA FROM INSERTEDIF DEPA=CSBEGIN PRINT YOU CAN NOT DELETE THE STUDENT FROM CS ROLLBACKEND执行上面的触发器，发现错误，则修改触发器。ALTER TRIGGER DELS ON S FOR DELETE ASDECLARE DEPA VARCHAR（20）SELECT DEPA=DEPA FROM DELETEDIF DEPA=CSBEGIN PRINT YOU CAN NOT DELETE THE STUDENT FROM CS ROLLBACKEND（2） 删除触发器DROP TRIGGER 表名.触发器名DROP TRIGGER S. DELS七、设计性实验1实验要求（1） 将S表的PRIMARY KEY完整性约束删除，编写一个S表上的触发器来完成在S表上插入记录时实现如下目标：SNO 不能为空，且SNO不能重复。alter table S drop constraints key1gocreate trigger pksno on s for insertas declare sno varchar(10) select sno=sno from insertedif sno is nullbegin print SNO不能为空 rollback end if sno in (select sno from s) begin print sno不可重复 rollback end（2） 在C表修改记录时，若修改的是CREDIT字段，则修改值超过6时提示出错信息并不将结果写出数据表。create trigger uc on c for updateasdeclare credit intselect credit=cr