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;
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值