实验六存储过程和触发器

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”，代表着员工“1091”的实际收入排在前三名。

【思考与练习】

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表中对应记录也应删除。

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

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

1向Employees表插入或修改一个记录时，通过触发器检查记录的DepartmentID值在Departments表中是否存在，若不存在，则取消插入或修改操作。

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

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

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

3删除Departments表中记录的同时修改记录DepartmentID字段值在Employees表中对应的记录。

4创建INSTEAD OF触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在，如果存在则执行插入操作，如果不存在则提示“员工号不存在”。

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

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

5创建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.

C、创建UPDATE触发器，当Salary表中InCome值增加500时，OutCome增加50.

将其中职工000001的InCome修改为2600.8（增加500时，OutCome增加50）

    从上面的截图中可以显示，当InCome增加500时，相应的OutCome也增加50.

D、创建INSTEAD OF触发器，实现向不可更新视图插入数据。

首先创建一个视图a_view

然后创建一个触发器

最后实现向视图a_view中插入数据：

E、创建DLL触发器 ，当删除数据库时，提示“不可删除”，并回滚删除操作。

4、实验总结

    通过本次实验大致的知道了触发器和存储过程的使用方法。知道存储过程可以像C语言中函数样使用，触发器则能够实现定义好很多触发的功能，像地雷一样。同样使用触发器能够是表中间满足参照完整性规则。通过本次实验对T-SQL命令有了更加熟悉的了解，对T-SQL逻辑有了更加深刻的认识。总而言之，收获很大。