实验六-存储过程和触发器

实验六存储过程和触发器142208100149陈聪1、目的与要求（1）掌握存储过程的使用方法。（2）掌握触发器的使用方法。2、实验准备（1）了解存储过程的使用方法。（2）了解触发器的使用过程。（3）了解inserted逻辑表和deleted逻辑表的使用。（4）了解如何编写CRL存储过程与触发器。3、实验内容。（1）存储过程。①创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。注：此段T-SQL命令，书上变量名书写错误，漏掉了下划线。（@nu_MBER1和@nuMBER1区别）执行该存储过程，并查看结果。②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出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表中对应的记录。④创建INSTEADOF触发器，当向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.C、创建UPDATE触发器，当Salary表中InCome值增加500时，OutCome增加50.将其中职工000001的InCome修改为2600.8（增加500时，OutCome增加50）从上面的截图中可以显示，当InCome增加500时，相应的OutCome也增加50.D、创建INSTEADOF触发器，实现向不可更新视图插入数据。首先创建一个视图a_view然后创建一个触发器最后实现向视图a_view中插入数据：E、创建DLL触发器，当删除数据库时，提示“不可删除”，并回滚删除操作。4、实验总结通过本次实验大致的知道了触发器和存储过程的使用方法。知道存储过程可以像C语言中函数样使用，触发器则能够实现定义好很多触发的功能，像地雷一样。同样使用触发器能够是表中间满足参照完整性规则。通过本次实验对T-SQL命令有了更加熟悉的了解，对T-SQL逻辑有了更加深刻的认识。总而言之，收获很大。