MySQL关闭外键约束 && 设置编号自增 && 触发器 - - Navicat Premium 12

关闭外键约束

在数据库定义中，不可避免要在一个表中引用另一个表的主键，因此外键就此出现。那么当我们需要根据外键删除一个或多个元组时，因为外键约束，我们无法完成删除操作！

SET FOREIGN_KEY_CHECKS=0;（1表示开，0表示关）

当关闭外键约束后，我们就可以顺利进行 delect 操作了哦！！

敲重点，实战来袭（上次触发器博客 up 版）

创建员工employee表、部门变动历史dept_history表，工资变动历史sal_history表

• 创建emp库

CREATE DATABASE emp DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

• 创建employee表

use emp;
CREATE TABLE employee
(
 empid VARCHAR(5) PRIMARY KEY, -- 员工编号
 empname VARCHAR(10), -- 员工姓名
 dept VARCHAR(15), -- 所在部门
 salary INT -- 工资
);

• 创建dept_history表

CREATE TABLE dept_history
(
 dhid INT auto_increment PRIMARY KEY, -- 部门变动编号（自动增长，无需赋值）
 empid VARCHAR(5), -- 员工编号
 olddept VARCHAR(15), -- 调动前的部门（新入职的员工old值记为NULL）
 newdept VARCHAR(15), -- 调动后的部门
 uptime DATETIME, -- 修改时间
 FOREIGN KEY (empid) REFERENCES employee(empid)
);

• 创建sal_history表

CREATE TABLE sal_history
(
 shid INT auto_increment PRIMARY KEY, -- 工资变动编号（自动增长，无需赋值）
 empid VARCHAR(5), -- 员工编号
 oldsal INT, -- 变动前的工资
 newsal INT, -- 变动后的工资
 uptime DATETIME, -- 修改时间
 FOREIGN KEY (empid) REFERENCES employee(empid)
);

任务一：

当新职工入职时，员工信息表将插入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(empid,olddept,newdept,uptime) VALUES(new.empid,null,new.dept,now());
 INSERT INTO sal_history(empid,oldsal,newsal,uptime) VALUES(new.empid,0,new.salary,now());
END$
DELIMITER ;

• 插入数据

INSERT INTO employee VALUES('101','李华','策划部',6000);
INSERT INTO employee VALUES('102','王梅','信息部',8000);
INSERT INTO employee VALUES('103','王刚','信息部',9000);
INSERT INTO employee VALUES('104','梅芳','宣传部',4000);

• 验证结果

employee表

dept_history表

sal_history表

任务二：

当员工离职时，员工信息表将删除1条数据。同时，触发器在工资变动历史中增加1条记录，其中newsal值为0；在部门变动历史中增加1条记录，其中newdept值为null

• 创建delect触发器

DELIMITER $
CREATE TRIGGER emp_delete
AFTER DELETE ON employee
FOR EACH ROW
BEGIN
 INSERT INTO dept_history(empid,olddept,newdept,uptime) VALUES(old.empid,old.dept,null,now());
 INSERT INTO sal_history(empid,oldsal,newsal,uptime) VALUES(old.empid,old.salary,0,now());
END$
DELIMITER ;

• 删除数据

SET FOREIGN_KEY_CHECKS=0; -- 关闭外键约束
DELETE FROM employee WHERE empid='101';
SET FOREIGN_KEY_CHECKS=1; -- 打开外键约束

• 验证结果

employee表

dept_history表

sal_history表

任务三：

当新职工部门或工资发生变化时，触发器执行以下操作。若部门发生变动，则在变动历史中增加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(empid,olddept,newdept,uptime) VALUES(new.empid,old.dept,new.dept,now());
 END IF;
 IF(old.salary!=new.salary)
  THEN
   INSERT INTO sal_history(empid,oldsal,newsal,uptime) VALUES(new.empid,old.salary,new.salary,now());
 END IF;
END$
DELIMITER ;

• 更新数据

UPDATE employee
SET dept='技术部'
WHERE empid='102';
UPDATE employee
SET salary=6000
WHERE empid='104';

• 验证结果

employee表

dept_history表

sal_history表