演示环境: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 | 实际发放金额 |
前端直观展示:
实际工作中薪资规则差不多是这样的:
- 全勤 ,全勤奖: 400.0。如果员工有迟到或者请假,将不会获得全勤奖,反而要扣除工资;
- 奖金 ,月度奖金一般为基本工资的10%;
- 保险 ,所得的薪资会先扣除五险一金,再根据扣除后的薪资计算个人所得税;
- 实际工资 ,计算出个人所得税后,再扣除,最终得到实际工资;
- 个人所得税 :
<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;