如何在SQL INSERT前按Type_ID校验数据范围？触发器报错排查

你的错误点 #

1. DELIMITER 命令不被数据库驱动支持
   DELIMITER 是 MySQL 客户端（比如命令行工具）专属命令，不属于标准SQL语法，不能通过 myDataSource.query() 执行，这是导致语法错误的直接原因。

2. 重复创建触发器
   每次调用 createRecordData 都执行 CREATE TRIGGER，会触发"触发器已存在"的错误，触发器是数据库持久化对象，只需创建一次，无需每次插入时重复定义。

3. 触发器字段名与表结构不匹配
   你的 data 表字段是 Type_ID 和 data_count，但触发器里写的是 NEW.data_type、NEW.data，字段名完全错误，会引发字段不存在的问题。

4. 范围规则与需求不符
   你触发器里定义的范围（比如Type a是10-90）和你实际需求的Type a（50-100）、Type b（0-100）、Type c（10-30）完全不一致。

5. 错误信息描述错误
   所有分支的错误信息都写的"Type a"，未对应实际的类型。

修复步骤 #

第一步：提前在数据库中创建正确的触发器 #

直接在MySQL客户端（如Navicat、mysql命令行）执行以下SQL创建触发器，无需在代码中动态创建：

CREATE TRIGGER check_data_count_before_insert
BEFORE INSERT ON data
FOR EACH ROW
BEGIN
    IF NEW.Type_ID = 1 THEN
        -- Type a：限制50-100
        IF NEW.data_count < 50 OR NEW.data_count > 100 THEN
            SIGNAL SQLSTATE '45000'
            SET message_text = 'Type a: data_count必须在50-100之间';
        END IF;
    ELSEIF NEW.Type_ID = 2 THEN
        -- Type b：限制0-100
        IF NEW.data_count < 0 OR NEW.data_count > 100 THEN
            SIGNAL SQLSTATE '45000'
            SET message_text = 'Type b: data_count必须在0-100之间';
        END IF;
    ELSEIF NEW.Type_ID = 3 THEN
        -- Type c：限制10-30
        IF NEW.data_count < 10 OR NEW.data_count > 30 THEN
            SIGNAL SQLSTATE '45000'
            SET message_text = 'Type c: data_count必须在10-30之间';
        END IF;
    END IF;
END;

如果后续需要修改规则，直接ALTER TRIGGER或DROP后重新创建即可。

第二步：修正DAO层代码 #

移除动态创建触发器的逻辑，同时修复a_id的获取方式（你之前写的固定值1939276是硬编码，应该获取刚插入a表的自增ID），并捕获触发器抛出的错误返回400：

const createRecordData = async (userId, a1, a2, typeId, count) => {
  // 插入a表并获取自增ID
  const [insertAResult] = await myDataSource.query(
    `INSERT INTO a (user_id,a1,a2) VALUES (?,?,?)`,
    [userId, a1, a2]
  );
  const aId = insertAResult.insertId;

  try {
    // 插入data表，触发器自动校验范围
    const [dataResult] = await myDataSource.query(
      `INSERT INTO data (a_id, Type_ID, data_count) VALUES (?,?,?)`,
      [aId, typeId, count]
    );
    return dataResult;
  } catch (err) {
    // 捕获触发器抛出的自定义错误，返回400
    if (err.sqlState === '45000') {
      const error = new Error('400 Bad Request: ' + err.message);
      error.statusCode = 400;
      throw error;
    }
    // 其他错误正常抛出
    throw err;
  }
};

额外说明 #

• SQLSTATE '45000'是MySQL标准的用户自定义错误状态码，用来标识应用程序级别的错误。
• 如果你希望规则和data_type表关联（比如后续修改Type的范围无需修改触发器），可以把触发器改成关联data_type表的动态查询方式，示例：

CREATE TRIGGER check_data_count_before_insert
BEFORE INSERT ON data
FOR EACH ROW
BEGIN
    DECLARE min_val INT;
    DECLARE max_val INT;
    -- 从data_type表获取对应Type的范围
    SELECT 
        CASE Type
            WHEN 'a' THEN 50
            WHEN 'b' THEN 0
            WHEN 'c' THEN 10
        END,
        CASE Type
            WHEN 'a' THEN 100
            WHEN 'b' THEN 100
            WHEN 'c' THEN 30
        END
    INTO min_val, max_val
    FROM data_type
    WHERE id = NEW.Type_ID;

    IF NEW.data_count < min_val OR NEW.data_count > max_val THEN
        SIGNAL SQLSTATE '45000'
        SET message_text = CONCAT('Type ', (SELECT Type FROM data_type WHERE id = NEW.Type_ID), ': data_count必须在', min_val, '-', max_val, '之间');
    END IF;
END;

内容的提问来源于stack exchange，提问作者ggyoE