数据库技术实验六

课程名称 实验名称 学号 数据库技术 存储过程和触发器的使用 姓名 班级 实验 成绩 日期 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 WorkYear from Employees where EmployeeID=@EmployeeID) declare @DepartmentID char(3) set @DepartmentID=( select DepartmentID from Departments where DepartmentName=@name) if (@year>6) update Employees set DepartmentID=@DepartmentID where EmployeeID=@EmployeeID End exec dbo.yuangong_infol '000000','经理办公室'

（2） 创建存储过程，根据每个员工的学历将收入提高500元。 alter proc SA_IN @enu char(6) as begin update Salary set InCome=InCome+500 from Salary,Employees where Employees.EmployeeID=Salary.EmployeeID and Education=@enu end select InCome from Salary,Employees where Salary.EmployeeID=Employees.EmployeeID and Education='本科' go exec dbo.sa_in '本科' go select InCome from Salary,Employees where Salary.EmployeeID=Employees.EmployeeID and Education='本科' select InCome from Salary,Employees where Salary.EmployeeID=Employees.EmployeeID and Education='本科' （3） 创建存储过程，使用游标计算本科及以上学历的员工在总员工数中的比例。 declare @edu varchar(10), @part_count int, @all_count int ; declare mycursor cursor for select distinct education, COUNT(education) over(partition by education) as part_count, COUNT(education) over() as all_count

from Employees open mycursor fetch next from mycursor into @edu,@part_count,@all_count while @@FETCH_STATUS=0begin print @edu+'占总人数比例：'+convert(varchar(100),convert(numeric(38,2),@part_count/1.0/@all_count*100))+'%' fetch next from mycursor into @edu,@part_count,@all_count end close mycusor deallocate mycursor （4） 使用命令方式修改及删除一个存储过程。 if exists(select workyear from Employees where workyear=3) drop procedure workyear 2. 触发器 （1） 对于YGGL数据库，表Employees的Employeeid列与表Salary的Employeeid列应满足参照完整性规则，请用触发器实现两个表间的参照完整性。 create trigger Salaryins0 on Salary for insert,update as begin if(select employeeid from inserted) not in(select EmployeeID from Employees) rollback end create trigger Employeesupdate0 on dbo.Employees for update as begin update Salary set employeeid=(select employeeid from inserted) where employeeid=(select employeeid from deleted) end create trigger Employeesdelete0 on Employees

for delete as begin delete from Salary where employeeid=(select employeeid from deleted) end （2）当修改表Employees时，若将Employees表中员工的工作时间增加1年，则将收入增加500，若增加2年则增加1000，依次增加。若工作时间减少则无变化。 create trigger em_workyear on Employees after update as begin declare @a int,@b int set @a=(select workyear from inserted) set @b=(select workyear from deleted) if(@a>@b) update Salary set income=income + (@a-@b)*500 where enployeeid in(select EmployeesID from inserted) end update Employees set workyear=12 where EmployeesID='000001' （3）创建UPDATE触发器，当Salary表中InCome值增加500时，outCome值则增加50。 create trigger sa_income on Salary for update as begin if((select income from inserted)-(select income from deleted)=500) update Salary set outcome=outcome+50 where enployeeid=(select enployeeid from inserted) end select income,outcome from Salary where enployeeid='000001' （5） 创建INSTEAD OF触发器，实现向不可更新视图插入数据。

create view a_view as select Employees.EmployeesID,name,workyear,income,outcome from Employees,Salary where Employees.EmployeesID=Salary.enployeeid go create trigger gxst on a_view instead of insert as begin declare @Ei char(6),@name char(10),@wy tinyint,@ic float,@oc float select @Ei=EmployeesID,@name=name,@wy=workyear,@ic=income,@oc=outcome from inserted insert into Employees(EmployeesID,name,workyear) values(@Ei,@name,@wy) insert into Salary values(@Ei,@ic,@oc) end insert into a_view values('000011','小芳',3,2000,1500) select * from a_view where EmployeesID='000011' （5）创建DDL触发器，当删除数据库时，提示“无法删除”并回滚删除操作。 create trigger table_delete on database after drop_table as print'不能删除表' rollback transaction go drop table YGGL 实验总结（结论或问题分析）： 在本次实验中，感觉很难，对触发器和存储过程不是很了解，最后老师讲了，自己通过讲的虽然说做出来了，但是还是有不明白的地方，需要自己在下面复习巩固。