数据库系统（四）——触发器

数据库数据库

发布时间 : 2021-01-03 19:07

字数:1k 阅读 :

一、实验目的：
二、实验内容：
三、题目：
四、实验过程：
五、实验小结：

一、实验目的：

熟悉通过SQL对数据进行完整性控制；
练习实际应用中完整性控制方法；
练习触发器的创建和使用。

二、实验内容：

基于某员工管理数据库，使用触发器实现数据完整性控制，完成以下功能：

创建触发器实现员工入职时，记录员工初始部门和工资信息到变动历史表中；
创建触发器实现员工信息变更时，记录员工的部门或工资变动情况到变动历史表中；
创建触发器实现员工离职时，自动备份离职前的工资到变动历史表中。

三、题目：

某员工信息管理系统中，当员工入职、离职、调动部门或工资变动时，需将变动信息记入变动历史中，请使用触发器完成此功能。变动历史包含部门变动历史和工资变动历史，分别使用部门变动历史表和工资变动历史表记录对应信息。

参考表结构如下：

员工表：employee(eid,ename,dept,salary,uptime,stutus)
其中eid为员工编号，ename为员工姓名，dept为所在部门，salary为工资，uptime为修改时间，stutus为状态（1表示在职，0表示离职）。
部门变动历史表：dept_history(dhid, eid, old, new, uptime)
其中dhid为部门变动编号（自动增长，无需赋值），eid为员工编号，old为调动前的部门（新入职的员工old值记为NULL），new为调动后的部门，uptime为修改时间。
工资变动历史表：sal_history(shid, eid, old, new, uptime)
其中shid为工资变动编号（自动增长，无需赋值），eid为员工编号，old为变动前的工资，new为变动后的工资，uptime为修改时间。

触发器要求如下：

当新职工入职时，员工信息表将插入1条数据。同时，触发器在部门变动历史中增加1条记录，其中old值为null；在工资变动历史中增加1条记录，其中old值为0。
当新职工部门或工资发生变化时，触发器执行以下操作。若部门发生变动，则在变动历史中增加1条记录；若工资发生变动，则在工资变动历史中增加1条记录。

四、实验过程：

创建数据库：

CREATE DATABASE emp;

-- 创建员工表
CREATE TABLE employee (
eid CHAR (6) PRIMARY KEY,
ename VARCHAR (10) UNIQUE,
dept VARCHAR (10) UNIQUE,
salary INT,
uptime datetime,
status char(1)
) 

--插入数据
INSERT INTO employee VALUES( '1001', 'zs', '软开', 3000, CURRENT_TIMESTAMP, '1' );

-- 创建部门变动历史表
CREATE TABLE dept_history (
    dhid INT PRIMARY KEY AUTO_INCREMENT,
    eid CHAR (6),
    old VARCHAR (10),
    new VARCHAR (10),
    uptime datetime 
) 

-- 创建工资变动历史表
CREATE TABLE sal_history ( 
shid INT PRIMARY KEY AUTO_INCREMENT, 
eid CHAR (6), 
old INT, 
new INT, 
uptime datetime 
)

问题一：当新职工入职时，员工信息表将插入1条数据。同时，触发器在部门变动历史中增加1条记录，其中olddept值为null；在工资变动历史中增加1条记录，其中oldsal值为0。

创建insert触发器：

DELIMITER $
CREATE TRIGGER emp_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
INSERT INTO dept_history ( eid, old, new, uptime )
    VALUES( new.eid, NULL, new.dept, new.uptime );
    INSERT INTO sal_history ( eid, old, new, uptime )
    VALUES( new.eid, 0, new.salary, new.uptime );
END$
DELIMITER ;

问题二：当新职工部门或工资发生变化时，触发器执行以下操作。若部门发生变动，则在变动历史中增加1条记录；若工资发生变动，则在工资变动历史中增加1条记录。

创建update触发器：

DELIMITER $
CREATE TRIGGER emp_update
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
     IF
    (old.dept != new.dept) THEN
    INSERT INTO dept_history ( eid, old, new, uptime )
    VALUES(new.eid, old.dept, new.dept, new.uptime );
    END IF;
    IF
    (old.salary != new.salary) THEN
    INSERT INTO sal_history ( eid, old, new, uptime )
    VALUES(new.eid, old.salary, new.salary, new.uptime );    
    END IF;
END$
DELIMITER ;

五、实验小结：

触发器触发时间分为 before和 after，顾名思义，before代表触发器里面的命令在修改数据之前执行，after代表触发器里面的命令在修改数据命令之后执行。

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 2058751973@qq.com