PART 1
创建trigger_practice库
CREATE DATABASE trigger_practice DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE trigger_practice;
#准备工作:创建员工employee表、部门变动历史dept_history表,工资变动历史sal_history表
#1,创建员工employee表:
CREATE TABLE employee
(
empid VARCHAR(20) PRIMARY KEY, -- 员工编号;在列级定义主码empid
empname VARCHAR(20), -- 员工姓名
dept VARCHAR(20), -- 所在部门
salary INT, -- 工资
uptime DATE, -- 修改时间
stutus INT -- 状态(1表示在职,0表示离职)
);
#2,创建部门变动历史dept_history表
CREATE TABLE dept_history
(
dhid INT auto_increment PRIMARY KEY, -- 部门变动编号(自动增长,无需赋值)
empid VARCHAR(20), -- 员工编号
olddept VARCHAR(20), -- 调动前的部门(新入职的员工old值记为NULL)
newdept VARCHAR(20), -- 调动后的部门
uptime DATE, -- 修改时间
FOREIGN KEY (empid) REFERENCES employee(empid) #FOREIGN key定义empid为外码;REFERENCES指明这些外码参照哪些表的主码
);
#3,创建工资变动历史sal_history表
CREATE TABLE sal_history
(
shid INT auto_increment PRIMARY KEY, -- 工资变动编号(自动增长,无需赋值)
empid VARCHAR(20), -- 员工编号
oldsal INT, -- 变动前的工资
newsal INT, -- 变动后的工资
uptime DATE, -- 修改时间
FOREIGN KEY (empid) REFERENCES employee(empid)
);
PART 2
#问题一:当新职工入职时,员工信息表将插入1条数据。同时,触发器在部门变动历史中增加1条记录,其中olddept值为null;在工资变动历史中增加1条记录,其中oldsal值为0。
#1.1,创建insert触发器
DELIMITER $
CREATE TRIGGER emp_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
INSERT INTO dept_history(empid,olddept,newdept,uptime) VALUES(new.empid,null,new.dept,new.uptime);
INSERT INTO sal_history(empid,oldsal,newsal,uptime) VALUES(new.empid,0,new.salary,new.uptime);
END$
DELIMITER ;
#1.2,插入数据验证:
INSERT INTO employee VALUES('001','朱嘉顺','策划部',6000,'1997-08-19',1);
INSERT INTO employee VALUES('002','程文扬','信息部',7000,'1996-08-26',1);
INSERT INTO employee VALUES('003','刘朝阳','销售部',8000,'1998-01-16',1);
INSERT INTO employee VALUES('004','徐鹏彪','宣传部',9000,'1999-10-30',1);
PART 3
#问题二:当新职工部门或工资发生变化时,触发器执行以下操作。若部门发生变动,则在变动历史中增加1条记录;若工资发生变动,则在工资变动历史中增加1条记录。
#2.1,创建update触发器:
DELIMITER $
CREATE TRIGGER emp_update
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
IF(new.dept!=old.dept OR new.stutus=0)
THEN
INSERT INTO dept_history(empid,olddept,newdept,uptime) VALUES(new.empid,old.dept,new.dept,new.uptime);
END IF;
IF(new.salary!=old.salary OR new.stutus=0)
THEN
INSERT INTO sal_history(empid,oldsal,newsal,uptime) VALUES(new.empid,old.salary,new.salary,new.uptime);
END IF;
END$
DELIMITER ;
#2.2,更新数据验证:
UPDATE employee
SET dept=null,salary=0,uptime='2020-11-02',stutus=0
WHERE empid='001';
UPDATE employee
SET dept='技术部',uptime='2020-11-02'
WHERE empid='002';
UPDATE employee
SET salary=6000,uptime='2020-11-02'
WHERE empid='004';