数据库系统SSD7 实验4《触发器与游标》教程

实验4《触发器与游标》

一、实验目的

进一步熟悉SQL语句对数据库进行完整性控制的方法；理解触发器的概念、定义方法和触发条件。理解游标的定义、打开、使用、关闭与释放的方法。

二、实验内容

针对实验3所建销售数据库：

1. 设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据，否则出错。
2. 针对employee表写一个DELETE触发器。
3. 针对employee表写一个UPDATE触发器。
4. 统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工的姓名与薪水（使用游标）。

三、实验方法

本次实验主要是利用SQL Server数据库管理工具结合实验3所建的销售数据库进行触发器设置和游标使用。
实验方案主要是结合课程PPT和网络教程进行实验开展。

四、实验步骤

1. 设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据，否则出错。
2. 针对employee表写一个DELETE触发器。
3. 针对employee表写一个UPDATE触发器。
4. 统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工的姓名与薪水（使用游标）。

五、实验结果

1．设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据，否则出错。

CREATE TRIGGER dboDelete
ON employee
FOR DELETE
AS
    IF exists (SELECT * from deleted)
        begin
            if USER != 'dbo'
            begin
                print '只有dbo用户具有删除权限！'
                rollback
            end
        end

DELETE FROM employee WHERE emp_no='E0019'
SELECT * FROM employee WHERE emp_no='E0019'

当用户为“dbo”时，删除成功：

2．针对employee表写一个DELETE触发器。

CREATE TRIGGER employeeDelete
ON employee
FOR DELETE
AS
    print('正在执行employee表的删除操作。')

DELETE FROM employee WHERE emp_no='E0019'

3．针对employee表写一个UPDATE触发器。

CREATE TRIGGER employeeUpdate
ON employee
FOR UPDATE
AS
    print('正在执行employee表的更新操作。')

UPDATE employee SET emp_name='王仁华' WHERE emp_no='E0019'

4．统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工的姓名与薪水（使用游标）。

DECLARE @name varchar(10),
        @salary varchar(10)
--1.声明游标
DECLARE lowAvgSalaryEmployee CURSOR
FOR
    SELECT emp_name, salary
    FROM employee
    WHERE salary<(SELECT avg(salary) FROM employee)
--2.打开游标
OPEN lowAvgSalaryEmployee
--3.推进游标
print('name  salary')
FETCH NEXT
FROM lowAvgSalaryEmployee
INTO @name,@salary
--4.逐行操作
while(@@fetch_status=0)
    begin
        print(@name+'  '+@salary)
        FETCH NEXT FROM lowAvgSalaryEmployee INTO @name,@salary
    end
--5.关闭游标
CLOSE lowAvgSalaryEmployee
--6.释放游标
DEALLOCATE lowAvgSalaryEmployee

六、实验结论

实验数据和结果见第五点，获取结果均符合预期，无差错。
结论：数据库中可以利用触发器实现强制业务规则和数据完整性，实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。
利用游标可以实行对查询等结果逐行操作，而不必对整个关系表进行操作。

七、实验小结

本次实验主要学会了如何利用触发器对数据库中的数据进行进一步完整性约束，对触发器的理解更加深刻。同时学会了利用游标操作进行对查询等结果逐行操作，使用游标六步走、变量类型等细节处理。
遇到的问题主要是在使用游标输出员工的姓名与薪资时，一直显示类型转换错误；

解决方式：思考尝试后发现经过游标获取的值类型已默认转换为char，所以将获取薪资的变量的类型设为varchar即解决。