1、课程名称数据库技术实验成绩实验名称存储过程和触发器的使用学号姓名班级日期14.11.25实验目的：1. 掌握存储过程的使用方法；2. 掌握触发器的实现方法；实验平台：利用RDBMS（SQL Server 2008）及其交互查询工具（查询分析器）来操作T-SQL语言；实验内容：1. 存储过程（1） 创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。alter procedure yuangong_infol EmployeeID char(6),name nchar(20)as begin declare year int set year=( select WorkYea
2、r from Employees where EmployeeID=EmployeeID) declare DepartmentID char(3) set DepartmentID=( select DepartmentID from Departments where DepartmentName=name) if (year6) update Employees set DepartmentID=DepartmentID where EmployeeID=EmployeeIDEndexec dbo.yuangong_infol 000000,经理办公室（2） 创建存储过程，根据每个员工的
3、学历将收入提高500元。alter proc SA_IN enu char(6)asbeginupdate Salaryset InCome=InCome+500from Salary,Employeeswhere Employees.EmployeeID=Salary.EmployeeID and Education=enuendselect InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education=本科goexec dbo.sa_in 本科goselect InComefrom
4、 Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education=本科select InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education=本科（3） 创建存储过程，使用游标计算本科及以上学历的员工在总员工数中的比例。declare edu varchar(10), part_count int, all_count int ;declare mycursor cursor for select
5、 distinct education, COUNT(education) over(partition by education) as part_count, COUNT(education) over() as all_count from Employees open mycursorfetch next from mycursor into edu,part_count,all_count while FETCH_STATUS=0begin print edu+占总人数比例：+convert(varchar(100),convert(numeric(38,2),part_count/
6、1.0/all_count*100)+% fetch next from mycursor into edu,part_count,all_countendclose mycusordeallocate mycursor（4） 使用命令方式修改及删除一个存储过程。if exists(select workyear from Employees where workyear=3) drop procedure workyear2. 触发器（1） 对于YGGL数据库，表Employees的Employeeid列与表Salary的Employeeid列应满足参照完整性规则，请用触发器实现两个表间的参
7、照完整性。create trigger Salaryins0 on Salaryfor insert,update asbeginif(select employeeid from inserted) not in(select EmployeeID from Employees)rollbackendcreate trigger Employeesupdate0 on dbo.Employeesfor updateasbeginupdate Salaryset employeeid=(select employeeid from inserted)where employeeid=(sele
8、ct employeeid from deleted)endcreate trigger Employeesdelete0 on Employeesfor deleteasbegindelete from Salarywhere employeeid=(select employeeid from deleted)end（2）当修改表Employees时，若将Employees表中员工的工作时间增加1年，则将收入增加500，若增加2年则增加1000，依次增加。若工作时间减少则无变化。create trigger em_workyear on Employeesafter updateasbeg
9、indeclare a int,b intset a=(select workyear from inserted)set b=(select workyear from deleted)if(ab)update Salaryset income=income + (a-b)*500where enployeeid in(select EmployeesID from inserted)endupdate Employeesset workyear=12where EmployeesID=000001（3）创建UPDATE触发器，当Salary表中InCome值增加500时，outCome值则
10、增加50。create trigger sa_income on Salaryfor update as beginif(select income from inserted)-(select income from deleted)=500)update Salary set outcome=outcome+50where enployeeid=(select enployeeid from inserted)end select income,outcomefrom Salary where enployeeid=000001（5） 创建INSTEAD OF触发器，实现向不可更新视图插入
11、数据。create view a_viewasselect Employees.EmployeesID,name,workyear,income,outcomefrom Employees,Salarywhere Employees.EmployeesID=Salary.enployeeidgocreate trigger gxston a_viewinstead of insertasbegindeclare Ei char(6),name char(10),wy tinyint,ic float,oc floatselect Ei=EmployeesID,name=name,wy=work
12、year,ic=income,oc=outcomefrom insertedinsert into Employees(EmployeesID,name,workyear) values(Ei,name,wy)insert into Salary values(Ei,ic,oc) endinsert into a_viewvalues(000011,小芳,3,2000,1500)select * from a_viewwhere EmployeesID=000011（5）创建DDL触发器，当删除数据库时，提示“无法删除”并回滚删除操作。create trigger table_delete on database after drop_tableas print不能删除表 rollback transaction go drop table YGGL实验总结（结论或问题分析）： 在本次实验中，感觉很难，对触发器和存储过程不是很了解，最后老师讲了，自己通过讲的虽然说做出来了，但是还是有不明白的地方，需要自己在下面复习巩固。
