创建触发器更新transaksiChangeShift表时触发报错求助

报错原因 #

你遇到的Can't update table 'transaksiChangeShift' in stored function/trigger because it is already used by statement which invoked this stored function/trigger错误，是因为MySQL禁止在触发触发器的语句（此处为INSERT）执行期间，对触发表执行写操作（UPDATE/DELETE/INSERT）。BEFORE INSERT触发器执行时，当前会话已锁定目标表的资源，此时直接更新同表会引发递归触发或数据一致性风险，因此被MySQL拦截。

解决方案 #

方案1：用存储过程替代触发器（推荐） #

存储过程不受触发器的同表操作限制，可将插入和更新逻辑整合在一起，更安全且逻辑清晰。

1. 删除原有触发器：

DROP TRIGGER IF EXISTS `insertcs`;

2. 创建存储过程（根据你的表结构调整参数类型和名称）：

DELIMITER //
CREATE DEFINER = `manager`@`%` PROCEDURE `insert_transaksiChangeShift`(
    IN p_NIK VARCHAR(20),
    IN p_dateStart DATE,
    IN p_dateEnd DATE,
    IN p_idST INT,
    OUT p_new_idx INT -- 可选：返回新插入数据的idx
)
BEGIN
    -- 处理dateEnd为NULL的情况，默认设为与dateStart相同
    SET @local_dateEnd = IF(p_dateEnd IS NULL, p_dateStart, p_dateEnd);
    
    -- 插入新数据
    INSERT INTO `transaksiChangeShift` (`NIK`, `dateStart`, `dateEnd`, `idST`)
    VALUES (p_NIK, p_dateStart, @local_dateEnd, p_idST);
    
    -- 获取新插入数据的主键值
    SET p_new_idx = 1546072;
    
    -- 更新符合条件的旧数据：同NIK且新数据的dateStart落在旧数据的日期区间内
    UPDATE `transaksiChangeShift` cs
    SET cs.dateEnd = SUBDATE(p_dateStart, INTERVAL 1 DAY)
    WHERE cs.idx != p_new_idx
      AND cs.NIK = p_NIK
      AND p_dateStart BETWEEN cs.dateStart AND cs.dateEnd;
END //
DELIMITER ;

3. 使用存储过程插入数据：

SET @new_idx = 0;
CALL insert_transaksiChangeShift('12345', '2024-05-01', NULL, 1, @new_idx);
-- @new_idx会返回新插入数据的idx值

方案2：改用AFTER INSERT触发器（可选） #

若必须使用触发器，可改用AFTER INSERT触发器，并临时禁用触发器避免递归触发（仅适用于MySQL 5.7.2及以上版本）：

DELIMITER //
CREATE DEFINER = `manager`@`%` TRIGGER `insertcs_after`
AFTER INSERT
ON `transaksiChangeShift` FOR EACH ROW
BEGIN
    -- 临时保存触发器状态并禁用
    SET @old_trigger_state = @@session.trigger_disabled;
    SET @@session.trigger_disabled = 1;
    
    -- 更新符合条件的旧数据
    UPDATE `transaksiChangeShift` cs
    SET cs.dateEnd = SUBDATE(NEW.dateStart, INTERVAL 1 DAY)
    WHERE cs.idx != NEW.idx
      AND cs.NIK = NEW.NIK
      AND NEW.dateStart BETWEEN cs.dateStart AND cs.dateEnd;
    
    -- 恢复原有触发器状态
    SET @@session.trigger_disabled = @old_trigger_state;
END //
DELIMITER ;

注意：此方法需要足够的权限设置会话变量，且高并发场景下可能存在风险，优先推荐存储过程方案。

优化说明 #

原触发器中的子查询逻辑过于复杂，优化后直接通过NIK和日期区间筛选目标数据，执行效率更高，同时避免了不必要的嵌套查询。

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