MySQL 触发器 入门 实操教学

演示环境：Windows10 ， MySQL8.0.30
文末有salary表及触发器完整sql代码

trigger简介

MySQL触发器(trigger)是一种数据库对象，用于在指定的数据库表上自动执行特定的操作，当满足特定的事件发生时。它们允许您在数据库进行更高级别的控制，例如在插入、更新或删除数据时执行特定的操作或应用业务规则。

实操教学

那么MySQL触发器能发挥什么作用呢？
假如我现在有个salary工资表:

字段名	描述
name	员工姓名
departmentId	部门ID
basicSalary	基本工资
allowance	津贴
bonus	奖金
subsidy	补贴
tax	个税
insurance	五险一金
late	迟到
vacation	请假
fullAttendance	全勤
overtime	加班工资
startDate	薪资生效起始日期
last_Updated_date	薪资最后更新日期
actual_payment	实际发放金额

前端直观展示：

实际工作中薪资规则差不多是这样的：

1. 全勤 ，全勤奖: 400.0。如果员工有迟到或者请假，将不会获得全勤奖，反而要扣除工资；
2. 奖金 ，月度奖金一般为基本工资的10%；
3. 保险 ，所得的薪资会先扣除五险一金，再根据扣除后的薪资计算个人所得税；
4. 实际工资 ，计算出个人所得税后，再扣除，最终得到实际工资；
5. 个人所得税 :
   <5000，不用缴税;
   <=10000，缴纳10%;
   >10000，缴纳20%；

---

咋一看，添加存储过程就能解决此规则，但是会有点麻烦，这里我们就能用到MySQL触发器轻松解决。
首先，创建触发器：

CREATE TRIGGER ***
BEFORE UPDATE ON salary
FOR EACH ROW
BEGIN
-- 触发器的具体操作逻辑
END

触发器名称可以是任意的，上面的***就是填写触发器名称的地方。

触发类型 触发器的触发类型一般有两种 ：before、after ，分别对应触发事件之前和之后。我这里选择before，因为选择before可以在数据更新之前进行数据验证和完整性，并处理和转换数据，也能阻止不符合条件的操作。(选择after可以对其他表联动更新，也能进行异步处理，是适合高阶操作的触发类型)

触发事件：
INSERT：在向表中插入新行触发。
UPDATE：在更新表中的行触发。
DELETE：在从表中删除行触发。
至于是修改之前触发还是修改之后触发取决于触发类型选择before还是after。

FOR EACH ROW 代表对salary表中的每一行都能响应的触发器，并且修改哪行，就只触发哪行，节省资源。

---

触发逻辑

现在到触发器的主体部分，也就是
BEGIN

END
里面的内容。

奖金

先来个最简单的，当有员工的基本工资(basic_Salary)有变动时，始终保持月度奖金(bonus)为基本工资的10%
那么语句就是这样：

SET NEW.bonus = NEW.basic_Salary * 0.1

这样的话，不管哪条薪资，只要基本工资变化了，奖金就会随之变化。

全勤

全勤奖默认是 400.0。如果员工有迟到(late)或者请假(vacation)，将不会获得全勤奖，反而要扣除工资；

我的salary表的迟到和请假字段都是具体的扣款数额，那么只要这两个数额任一一个不等于0，就说明有缺勤扣款，也就没有全勤奖，否则直接给出全勤奖(full_Attendance)400.0。

    IF NEW.late <> 0 OR NEW.vacation <> 0 THEN
        SET NEW.full_Attendance = 0;
    ELSE
        SET NEW.full_Attendance = 400.0;
    END IF;

总工资(未计算五险一金，未缴税)

salary表没有一个字段存储 “未计算五险一金,未缴税” 时的总工资，那么我们需要一个 临时变量(用@符号即可) 存储此数值：
也就是 基本工资+津贴+奖金+补贴+全勤奖+加班工资-迟到扣款-请假扣款 的金额：

SET @total = NEW.basic_Salary + NEW.allowance + NEW.bonus + NEW.subsidy + NEW.full_Attendance + NEW.overtime - NEW.late - NEW.vacation;

然后根据此total值，先计算五险一金(insurance)，并扣除：

SET @insurance = @total * 0.1;
SET @total = @total - @insurance;

得到扣除五险一金后的total值后，剩下的就是个人所得，然后就需要计算个人所得税(tax) ，规则是
<5000，不用缴税;
<=10000，缴纳10%;
>10000，缴纳20%；

IF @total <= 5000 THEN
    SET @tax = 0;
ELSEIF @total <= 10000 THEN
    SET @tax = @total * 0.1;
ELSE
    SET @tax = @total * 0.2;
END IF;

这样一来， 实际发放金额(actual_payment) 就出来了：

SET @actualPayment = @total - @tax;

---

由于带@符号的都是临时变量，需要同步更新到数据库表中，添加NEW. 前缀就能绑定数据库表的数值并更新数据。

SET NEW.insurance = @insurance,
    NEW.tax = @tax,
    NEW.actual_Payment = @actualPayment,
	NEW.bonus = NEW.basic_Salary * 0.1,
    NEW.last_Updated_Date = CURRENT_TIMESTAMP;

---

完整代码

创建此触发器的完整sql代码(以上面的salary表为标准)

CREATE TRIGGER salary_update
BEFORE UPDATE ON salary
FOR EACH ROW
BEGIN
    IF NEW.late <> 0 OR NEW.vacation <> 0 THEN
        SET NEW.full_Attendance = 0;
    ELSE
        SET NEW.full_Attendance = 400.0;
    END IF;

    SET @total = NEW.basic_Salary + NEW.allowance + NEW.bonus + NEW.subsidy + NEW.full_Attendance + NEW.overtime - NEW.late - NEW.vacation;

    -- 计算五险一金
    SET @insurance = @total * 0.1;
    SET @total = @total - @insurance;

    -- 计算个人所得税
    IF @total <= 5000 THEN
        SET @tax = 0;
    ELSEIF @total <= 10000 THEN
        SET @tax = @total * 0.1;
    ELSE
        SET @tax = @total * 0.2;
    END IF;

    -- 计算实发工资
    SET @actualPayment = @total - @tax;

    -- 同步更新到salary表
    SET NEW.insurance = @insurance,
        NEW.tax = @tax,
        NEW.actual_Payment = @actualPayment,
		NEW.bonus = NEW.basic_Salary * 0.1,
        NEW.last_Updated_Date = CURRENT_TIMESTAMP;
END;

---

salary表创建代码

创建salary表的部分sql代码(已删除与员工表、职位表、部门表的关联)

DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '薪资ID',
  `basic_salary` decimal(10,1) NOT NULL COMMENT '基本工资',
  `allowance` decimal(10,1) DEFAULT '0.0' COMMENT '津贴',
  `bonus` decimal(10,1) DEFAULT '0.0' COMMENT '奖金',
  `subsidy` decimal(10,1) DEFAULT '0.0' COMMENT '补贴',
  `tax` decimal(5,1) DEFAULT '0.0' COMMENT '个税',
  `insurance` decimal(5,1) DEFAULT '0.0' COMMENT '五险一金',
  `late` decimal(5,1) DEFAULT '0.0' COMMENT '迟到',
  `vacation` decimal(5,1) DEFAULT '0.0' COMMENT '请假',
  `full_attendance` decimal(5,0) DEFAULT '0' COMMENT '全勤',
  `overtime` decimal(10,1) DEFAULT '0.0' COMMENT '加班工资',
  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '薪资生效起始日期',
  `last_updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '薪资最后更新日期',
  `actual_payment` decimal(10,1) DEFAULT (((((((`basic_salary` + `allowance`) + `bonus`) + `subsidy`) + `full_attendance`) + `overtime`) - (((`tax` + `insurance`) + `late`) + `vacation`))) COMMENT '实际发放金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=327 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;