MySQL AFTER INSERT触发器导致无法插入数据报错问题求助

问题原因 #

MySQL 本身有内置限制：针对某张表的触发器运行时，不允许对触发该触发器的同一张表执行增删改查操作，避免出现循环调用、读写锁冲突的问题。你编写的是transaksiChangeShift表的AFTER INSERT触发器，触发器内部所有UPDATE、DELETE、SELECT操作都是针对同一张transaksiChangeShift表，直接触发了该限制，因此插入时报错。

解决方案 #

推荐优先使用存储过程替代触发器实现业务逻辑，完全规避同表操作限制，原有业务逻辑无需改动，仅需将插入操作和后续的更新删除逻辑封装到同一个存储过程中即可，示例代码如下：

DELIMITER //
CREATE DEFINER = 'manager'@'%' PROCEDURE HRS_1.InsertChangeShift(
    -- 按实际表结构补充所有需要插入的字段参数
    IN p_NIK VARCHAR(20),
    IN p_dateStart DATE,
    IN p_dateEnd DATE,
    OUT p_result_idx INT
)
BEGIN
    -- 第一步：插入新记录
    INSERT INTO HRS_1.transaksiChangeShift (NIK, dateStart, dateEnd, dateCreate /*其余插入字段*/)
    VALUES (p_NIK, p_dateStart, p_dateEnd, NOW() /*其余字段对应值*/);
    SET @new_idx = 2304783;
    -- 获取新插入记录的核心字段
    SELECT NIK, dateStart, dateEnd INTO @new_nik, @new_dateStart, @new_dateEnd 
    FROM HRS_1.transaksiChangeShift WHERE idx = @new_idx;

    -- 第二步：执行原有更新逻辑
    UPDATE HRS_1.transaksiChangeShift cs
    SET cs.dateEnd = IF(cs.dateStart!=@new_dateStart,IF(cs.dateStart=ADDDATE(@new_dateStart, INTERVAL -1 DAY),NULL,ADDDATE(@new_dateStart, INTERVAL -1 DAY)),NULL)
    WHERE cs.NIK=@new_nik AND (cs.dateEnd BETWEEN @new_dateStart AND @new_dateEnd OR @new_dateStart BETWEEN cs.dateStart AND cs.dateEnd) AND cs.idx < @new_idx;

    UPDATE HRS_1.transaksiChangeShift cs
    SET cs.dateStart = IF(cs.dateStart!=@new_dateStart,IF(cs.dateStart=ADDDATE(@new_dateEnd, INTERVAL 1 DAY),NULL,ADDDATE(@new_dateEnd, INTERVAL 1 DAY)),NULL)
    WHERE cs.NIK=@new_nik AND (cs.dateStart BETWEEN @new_dateStart AND @new_dateEnd AND cs.dateEnd > @new_dateEnd) AND @new_dateEnd IS NOT NULL AND cs.idx < @new_idx;

    -- 第三步：执行原有删除逻辑
    DELETE FROM HRS_1.transaksiChangeShift 
    WHERE NIK = @new_nik AND dateStart = @new_dateStart AND dateEnd IS NULL AND idx < @new_idx;

    DELETE FROM HRS_1.transaksiChangeShift 
    WHERE NIK = @new_nik AND (dateStart BETWEEN @new_dateStart AND @new_dateEnd AND dateEnd IS NULL) AND idx < @new_idx;

    DELETE FROM HRS_1.transaksiChangeShift 
    WHERE NIK = @new_nik AND (dateStart BETWEEN @new_dateStart AND @new_dateEnd AND dateEnd <= @new_dateEnd AND dateStart >= @new_dateStart) AND idx < @new_idx;

    SET p_result_idx = @new_idx;
END //
DELIMITER ;

后续插入数据时直接调用存储过程即可，调用示例：

CALL InsertChangeShift('对应NIK值', '2024-01-01', '2024-01-05', @result_id);

如果实在需要保留触发器机制，可以新增一张临时中转表，触发器仅负责将新插入的记录ID写入中转表，再通过定时任务或者后台服务读取中转表的ID执行后续的更新删除操作，该方案实时性略低于存储过程方案。

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