数据库技术实验六

课 程 名 称 数 据 库 技 术 实 验 名 称 存 储 过 程 和 触 发 器 的 使 用 实 验 成 绩 学 号 姓 名 班 级 日 期 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 (year6) 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(ab) 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