实验六存储过程和触发器

实验六存储过程和触发器

142208100149 陈聪

1、目的与要求

（1）掌握存储过程的使用方法。

（2）掌握触发器的使用方法。

2、实验准备

（1）了解存储过程的使用方法。

（2）了解触发器的使用过程。

（3）了解inserted逻辑表和deleted逻辑表的使用。

（4）了解如何编写CRL存储过程与触发器。

3、实验内容。

（1）存储过程。

①创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。

注：此段T-SQL命令，书上变量名书写错误，漏掉了下划线。（@nu_MBER1和@nu MBER1区别）

执行该存储过程，并查看结果。

②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1.

执行该存储过程并查改结果。

③创建添加职员记录的存储过程EmployeeAdd。

执行该存储过程。

执行上面T-SQL命令，在结果栏中显示一行受影响，说明结果已经插入成功，在表Employees中也够看到结果已经存在。

④创建存储过程，使用游标确定一个员工的实际收入是否排在前3名。结果为1表示是，结果为0表示否。

执行该存储过程，在结果栏中显示，命令已成功完成，并且在左侧对象资源管理器中进行刷新会显示刚刚创建的存储过程。

执行该存储过程：

上面T-SQL结果中输出“1”，代表着员工“108991”的实际收入排在前三名。

【思考与练习】

A、创建存储过程，要求当一个员工的工作年份大于6年时，将其转到经理办公室工作。

创建这个存储过程时候，在存储过程的条件判断中我添加了一项，判断工作年份在6年以上的员工是否在经理办公室，只有不在经理办公室的时候才进行调整，这样更符合实际问题些。

然后执行上面的存储过程，并且查看结果：

此时有可能会出现（我就出现了）这样的问题，在之前实验三中就出现了。

解决方法同样是解除规则的绑定。

查看表Employees中员工“000001”的DepartmentID已经修改为“3”（经理办公室）。

B、创建存储过程，根据每个员工的学历将收入提高500.

首先创建一个存储过程，利用什么学历增加收入，在这里，本科学历增加收入500.

执行上面的存储过程，查看结果：

由于在执行这个存储过程之前，我先执行了一个查询语句，将增加收入之前的所有本科学历职工的收入输出，便于与执行操作之后的结果进行比较。

（增加之前）（增加之后）

C、创建存储过程，使用游标计算本科及以上学历的员工在总员工数中所占的比例。

D、使用命令方式修改存储过程的定义。

在资源管理器中选择“可编程性”→“存储过程”→“系统存储过程”右击选择存储器，

“修改”在“查询分析器”中修改T-SQL命令行。

（2）触发器

对于YGGL数据库，表Employees的DepartmentID列和表Departments的DepartmentID 列满足参照完整性规则，即：

向Employees表添加记录时，该记录的“DepartmentID”字段值Departments表中应存在。

修改Departments表的“DepartmentID”字段值时，该字段在Employees表中的对应值也应该修改。

删除Departments表中记录时，该记录的“DepartmentID”字段值在Employees表中对应记录也应删除。

对于上述参照完整性规则，在此听过触发器实现。

在“查询分析器”窗口中输入各触发器的代码并执行。

①向Employees表插入或修改一个记录时，通过触发器检查记录的DepartmentID值在

Departments表中是否存在，若不存在，则取消插入或修改操作。

向Employees表插入一行记录，查看效果。

向表Employees中插入一个DepartmentID为6的职员信息，但是DepartmentID=6并不在Departments表中，因此会出现下面的触发器的提示。

②修改Departments表“DepartmentID”字段值时，该字段在Employees表中对应值也

做相应修改。

③删除Departments表中记录的同时修改记录DepartmentID字段值在Employees表中

对应的记录。

④创建INSTEAD OF触发器，当向Salary表中插入记录时，先检查EmployeeID列上的

值在Employees中是否存在，如果存在则执行插入操作，如果不存在则提示“员工号不存在”。

向Salary表中插入一行记录查看效果。

在结果中显示触发器提示的信息：

⑤创建DDL触发器，当删除YGGL数据库一个表时，提示“不能删除表”，并回滚删除操

作。

当利用资源管理器中窗口操作时，尝试着鼠标点击删除Salary表时，会出现如下结果：触发器阻止删除YGGL中表。

【思考与练习】

A、对于YGGL数据库，表Employees的EmployeeID列与表Salary的EmployeeID列应满足参照完整性规则，请用触发器实现两个表间的参照完整性规则。

当删除员工号为“000001”的职工，那么就会在Employees表哥Salary表中删除该员工的所有信息。

但是上面的触发器只设定了删除Salary中数据，并没有删除Employees中数据。

另外，如果直接删除Employees中数据：

就会出现这样的报错：

由于此时的EmployeeID存在于Salary表中，有外键关联无法删除，但是如果将Salary 表中的EmployeeID=‘010008’的记录删除就能删除了。

B、当修改表Employees时，若将Employees表中员工的工作时间增加一年，则将收入增加500，若增加2年则增加1000，依次增加。若工作时间减少则无变化。

创建一个触发器updateInCome

修改Employees表中职工号为000001的员工的工龄（修改为9），刷新Salary表显示工资增加500.