数据库实验——触发器.doc

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

1、数据库原理与应用实验报告题目: 触发器学号:_姓名:_教师:_实验题目触发器1、 实验内容1 创建数据库School, 表TeacherCREATE Table Teacher(Eno numeric(4) primary key,Ename char(10), Job char(8),Sal numeric(7,2),Deduct numeric(7,2),Deptno numeric(2);2 创建触发器Insert_Or_UpdateSalCREATE TRIGGER Insert_Or_Update_Sal On Teacher for INSERT, UPDATEASdeclare

2、sal numeric(7)declare eno numeric(4)declare job char(8)select sal=Sal from insertedselect eno=Eno from insertedselect job=Job from insertedIF(sal1)BEGINPrint Sage must be a integer mare than zero!Rollback tansactionEndElseIF(job=教授) and (sal4000)BEGINselect sal=4000delete from Teacher where Eno=enoi

3、nsert into Teacher(Eno, Ename, Job, Sal, Deduct, Deptno) select Eno, Ename, Job, sal, Deduct, Deptno, from insertedEND3 执行下列语句USE SchoolInsert into Teacher values(1,林夕,助教, 2500, 65,1)Insert into Teacher values(2,张东华,教授,7000,103,4)Insert into Teacher values(4,李列,教授,3000,80,4)Select * from Teacher插入Te

4、acher的三个元组是否和显示出来的结果一致？为什么？4 执行下列语句USE SchoolInsert into Teacher values(3,李丽,讲师, -1000,69,1)插入是否能够成功？为什么？（注：rollback表示回滚，表示取消刚刚进行的操作）5 执行下列语句USE SchoolUpdate Teacher set Sal=-7 where Eno=1更新操作是否能够成功？为什么？6 创建触发器T2 USE SchoolGoCREATE TRIGGER T2 on TeacherFOR DELETEasif(select eno from delete)=1begin p

5、rint He is the Director! Delete Fail!Rollback TransactionEnd7 执行语句USE schoolDelte from Teacher where Ename=林夕删除操作是否能够成功？为什么？8 创建表Student(Sno, Sname, Sage)9 为Student表创建触发器T3，当插入或者更新表中数据时，保证所操作记录的Sage的值大于0。11 使用插入/更新语句验证结果。12 为Student表创建触发器T4, 禁止删除学号为21号的学生。13 使用删除语句验证结果。2、 实验目的与要求通过实验加深对数据完整性的理解，学会创建

6、和使用触发器3、 实验环境SQL 20084、 实验结果与分析1 创建数据库School, 表TeacherCREATE Table Teacher(Eno numeric(4) primary key,Ename char(10), Job char(8),Sal numeric(7,2),Deduct numeric(7,2),Deptno numeric(2);2 创建触发器Insert_Or_UpdateSalCREATE TRIGGER Insert_Or_Update_Sal On Teacher for INSERT, UPDATEASdeclare sal numeric(7)

7、declare eno numeric(4)declare job char(8)select sal=Sal from insertedselect eno=Eno from insertedselect job=Job from insertedIF(sal1)BEGINPrint Sage must be a integer mare than zero!Rollback tansactionEndElseIF(job=教授) and (sal4000)BEGINselect sal=4000delete from Teacher where Eno=enoinsert into Tea

8、cher(Eno, Ename, Job, Sal, Deduct, Deptno) select Eno, Ename, Job, sal, Deduct, Deptno, from insertedEND3 执行下列语句USE SchoolInsert into Teacher values(1,林夕,助教, 2500, 65,1)Insert into Teacher values(2,张东华,教授,7000,103,4)Insert into Teacher values(4,李列,教授,3000,80,4)Select * from Teacher插入Teacher的三个元组是否和显

9、示出来的结果一致？为什么？不一致因为李列是教授，但Sal=30004000，当查询数据时，触发器触发验证操作，直接改为40004 执行下列语句USE SchoolInsert into Teacher values(3,李丽,讲师, -1000,69,1)插入是否能够成功？为什么？（注：rollback表示回滚，表示取消刚刚进行的操作）不能成功，由于Sal=-10001， 当插入数据时，触发器触发验证操作5 执行下列语句USE SchoolUpdate Teacher set Sal=-7 where Eno=1更新操作是否能够成功？为什么？不能成功，由于Sal=-71，当更新数据时，触发器触

10、发验证操作6 创建触发器T2 USE SchoolGoCREATE TRIGGER T2 on TeacherFOR DELETEasif(select eno from deleted)=1begin print He is the Director! Delete Fail!Rollback TransactionEnd7 执行语句USE schoolDelete from Teacher where Ename=林夕删除操作是否能够成功？为什么？不能成功，因为林夕的Eno=1，当删除数据时，触发器触发验证操作8 创建表Student(Sno, Sname, Sage)create tab

11、le Student(Sno varchar(10) primary key,Sname varchar(20),Sage smallint)Insert into Student values(1001,李磊,19)Insert into Student values(1002,张华,21)Insert into Student values(1003,韩梅,18)9 为Student表创建触发器T3，当插入或者更新表中数据时，保证所操作记录的Sage的值大于0。USE SchoolGoCREATE TRIGGER T3 on StudentFOR INSERT,UPDATEas decla

12、re Sage smallintselect Sage=Sage from insertedif(sage0)begin print 年龄应该大于Rollback TransactionEnd;11 使用插入/更新语句验证结果。Insert into Student values(1004,王城,-1)12 为Student表创建触发器T4, 禁止删除学号为21号的学生。USE SchoolGoCREATE TRIGGER T4 ON student for DELETEasif(select sno from deleted)=21beginprint 禁止删除学号为号的学生Rollback Transactionend;13 使用删除语句验证结果。Insert into Student values(21,李玲,20delete from Student where Sname=李玲