MySQL 触发器之 before 插入值检查

MySQL 触发器（Trigger）是指在一个定义的事件发生时，自动调用一个函数或一条语句的数据库对象。其中，before 触发器是在发生某个事件之前执行的，例如在插入数据之前执行检查某些数据是否符合要求。

本文将重点介绍 MySQL 触发器中 before 插入值的检查方法，以便开发者可以使用此功能来保证数据库中的数据质量。

阅读更多：MySQL 教程

什么是 before Insert value Checking

before Insert value Checking 意为在插入数据之前进行检查。其实现方式是使用 MySQL 触发器，在数据插入之前执行一个 SQL 语句或调用一个函数来检查插入的数据是否符合要求。

为什么需要 before Insert value Checking 呢？因为在现实应用场景中，数据的插入可能会存在不合法或不符合业务要求的数据，例如插入重复的数据、插入超出范围的数据、插入空数据等等。在这些情况下，需要 before Insert value Checking 来进行数据检查，以确保插入的数据符合要求。

MySQL 触发器的使用方法

创建一个触发器，需要以下基本元素：

触发器事件：在何时触发，例如 INSERT、UPDATE、DELETE 操作之前或之后。
触发器类型：在触发器事件之前或之后触发，分别为 BEFORE 和 AFTER。
触发器表：在何张表中触发。
触发器函数：在触发器事件之前或之后运行的函数。

下面是一个 before Insert value Checking 的触发器的基本语法：

CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE] 
ON table_name
FOR EACH ROW [FOLLOWS|PRECEDES] other_trigger_name
BEGIN
-- trigger function
END;

其中的 trigger_name 为触发器的名称，可以自定义；BEFORE 为 before 类型的触发器，INSERT 为插入操作的触发事件；table_name 为表的名称；trigger function 为需要执行的函数体，用 BEGIN 和 END 包含。

例如，在用户表（user）中插入一条记录时，需要检查用户的邮箱是否正确，如果不正确则不能插入。可以使用如下 SQL 语句来创建一个触发器：

CREATE TRIGGER user_insert_trigger
BEFORE INSERT
ON user
FOR EACH ROW
BEGIN
   IF (NEW.email NOT LIKE '%@%.%') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email';
   END IF;
END;

该触发器用户检查用户输入的邮箱是否符合包含“@”和“.”的规范。如果不符合，则阻止插入并返回错误信息。

before Insert value Checking 的应用实例

下面，我们将通过两个具体的示例来深入了解 before Insert value Checking 的应用方法。

示例1：检查手机号是否合法

在中国，手机号码通常为十一位数字。针对手机号的合法性，我们可以编写以下的触发器：

CREATE TRIGGER user_phone_insert_trigger
BEFORE INSERT
ON user
FOR EACH ROW
BEGIN
   IF(LENGTH(NEW.phone) <> 11 || NEW.phone NOT REGEXP '^[[:digit:]]+$') THEN
       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid phone number';
   END IF;
END;

该触发器用到了 MySQL 内置的函数 LENGTH 和 REGEXP，前者用于获取手机号码的数字长度，后者则用于正则匹配输入的号码是否全部为数字。如果手机号不符合要求，则阻止插入并返回错误信息。

示例2：检查加班费用

假设有一个员工加班费用表（overtime）需要跟踪员工每月的加班情况并计算加班费用。表具有以下字段：

id：加班记录的唯一标识符
employee_id：员工的唯一标识符
date：加班日期
hours：加班时长
rate：加班费率
cost：该次加班的费用

现在，我们需要为该表创建一个触发器，以确保插入的加班费用不会超过员工当月的工资。以下是该触发器的 SQL 语句：

CREATE TRIGGER overtime_insert_trigger
BEFORE INSERT
ON overtime
FOR EACH ROW
BEGIN
   DECLARE salary DECIMAL(10, 2);
   SELECT SUM(salary) INTO salary
   FROM employee
   WHERE employee.id = NEW.employee_id;
   IF (NEW.cost > salary/30 *0.3) THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Overtime cost exceeds month salary limit';
   END IF;
END;