MySQL触发器无法删除数据求助：告警生效但未删除对应记录

问题根源 #

你的触发器中，DELETE FROM accident操作会和后续的SIGNAL错误一起触发事务回滚。MySQL中，BEFORE INSERT触发器属于当前INSERT操作的事务上下文，一旦抛出错误，整个事务内的所有操作（包括触发器里的DELETE）都会被回滚，因此accident表的目标记录无法被删除。

解决方案 #

方案1：用存储过程封装完整操作（推荐） #

放弃先单独插入accident再插入participated的拆分流程，改用存储过程统一处理逻辑，先检查司机的事故次数，再决定是否插入两张表的记录，从根源避免事后删除的问题：

DELIMITER $$
CREATE PROCEDURE add_accident_participation(
    IN p_report_no INT,
    IN p_date DATE,
    IN p_location VARCHAR(255),
    IN p_driver_id VARCHAR(50),
    IN p_reg_no VARCHAR(50),
    IN p_amount DECIMAL(10,2)
)
BEGIN
    DECLARE driver_accident_count INT;
    
    -- 统计司机已参与的事故数量
    SELECT COUNT(*) INTO driver_accident_count 
    FROM participated 
    WHERE driver_id = p_driver_id;
    
    -- 判断是否达到事故次数限制
    IF driver_accident_count >= 3 THEN
        SIGNAL SQLSTATE '45000' 
        SET message_text = "Driver is already involved in 3 accidents";
    END IF;
    
    -- 插入事故记录
    INSERT INTO accident(report_no, date, location)
    VALUES(p_report_no, p_date, p_location);
    
    -- 插入关联参与记录
    INSERT INTO participated(driver_id, reg_no, report_no, amount)
    VALUES(p_driver_id, p_reg_no, p_report_no, p_amount);
END$$
DELIMITER ;

调用方式：

CALL add_accident_participation(34, '2022-04-05', 'bangalore', 'D1', 'KA-09-MM-5644', 20000);

方案2：修正原触发器逻辑（适配原有流程） #

如果必须保留先插入accident的流程，可以调整触发器为AFTER INSERT类型，同时修正判断条件（原条件COUNT(*) >3应改为COUNT(*) >=3，匹配"3次以上"的需求）：

DELIMITER $$
CREATE TRIGGER trigger2
AFTER INSERT ON participated FOR EACH ROW
BEGIN
    DECLARE driver_accident_count INT;
    
    SELECT COUNT(*) INTO driver_accident_count 
    FROM participated 
    WHERE driver_id = NEW.driver_id;
    
    IF driver_accident_count >= 3 THEN
        -- 删除对应的事故记录
        DELETE FROM accident WHERE report_no = NEW.report_no;
        -- 删除刚插入的参与记录
        DELETE FROM participated WHERE driver_id = NEW.driver_id AND report_no = NEW.report_no;
        SIGNAL SQLSTATE '45000' 
        SET message_text = "Driver is already involved in 3 accidents";
    END IF;
END$$
DELIMITER ;

注意：此方案依赖事务回滚特性，抛出错误后会撤销所有未提交的操作，确保两张表的数据一致性。

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