数据库实验——触发器.doc
数据库原理与应用实验报告题目: 触发器学号:_姓名:_教师:_实验题目触发器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 sal numeric(7)declare eno numeric(4)declare job char(8)select sal=Sal from insertedselect eno=Eno from insertedselect job=Job from insertedIF(sal<1)BEGINPrint Sage must be a integer mare than zero!Rollback tansactionEndElseIF(job=教授) and (sal<4000)BEGINselect sal=4000delete from Teacher where Eno=enoinsert 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插入Teacher的三个元组是否和显示出来的结果一致？为什么？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 print 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、 实验目的与要求通过实验加深对数据完整性的理解，学会创建和使用触发器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)declare eno numeric(4)declare job char(8)select sal=Sal from insertedselect eno=Eno from insertedselect job=Job from insertedIF(sal<1)BEGINPrint Sage must be a integer mare than zero!Rollback tansactionEndElseIF(job=教授) and (sal<4000)BEGINselect sal=4000delete from Teacher where Eno=enoinsert 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插入Teacher的三个元组是否和显示出来的结果一致？为什么？不一致因为李列是教授，但Sal=3000<4000，当查询数据时，触发器触发验证操作，直接改为40004 执行下列语句USE SchoolInsert into Teacher values(3,李丽,讲师, -1000,69,1)插入是否能够成功？为什么？（注：rollback表示回滚，表示取消刚刚进行的操作）不能成功，由于Sal=-1000<1， 当插入数据时，触发器触发验证操作5 执行下列语句USE SchoolUpdate Teacher set Sal=-7 where Eno=1更新操作是否能够成功？为什么？不能成功，由于Sal=-7<1，当更新数据时，触发器触发验证操作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 table 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 declare Sage smallintselect Sage=Sage from insertedif(sage<0)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)='21'beginprint '禁止删除学号为号的学生'Rollback Transactionend;13 使用删除语句验证结果。Insert into Student values('21','李玲',20delete from Student where Sname='李玲'
