数据库系统——触发器

一、实验目的：
熟悉通过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代表触发器里面的命令在修改数据命令之后执行。