MySQL Before Insert触发器无效：插入重复行未被拦截

你的触发器逻辑存在根本性错误，导致无法阻止重复插入：

• BEFORE INSERT触发器是在原INSERT操作执行前触发，此时原插入请求已经处于处理流程中。你在触发器里再次执行INSERT INTO roles，要么会额外插入一条数据（无重复时），要么原插入请求仍会完成（有重复时），完全达不到阻止重复的目的。

正确解决方案 #

方案1：修改触发器，抛出错误阻止插入 #

调整触发器逻辑，当检测到重复时抛出错误，中断原插入操作：

DELIMITER $$
CREATE TRIGGER TEST1
BEFORE INSERT ON roles FOR EACH ROW
BEGIN
    DECLARE atExists INT DEFAULT 0;
    SELECT COUNT(id) INTO atExists 
    FROM roles 
    WHERE personal_id = NEW.personal_id 
      AND role = NEW.role 
      AND favorite = NEW.favorite;
    IF atExists > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '禁止插入重复记录';
    END IF;
END $$
DELIMITER ;

当检测到已有相同的personal_id、role、favorite组合时，触发器会抛出错误，MySQL会终止当前的INSERT操作，从而避免重复数据被插入。

方案2：创建唯一约束（更推荐） #

触发器并非处理数据唯一性的最优选择，MySQL的唯一约束性能更高、可靠性更强，直接通过创建联合唯一索引即可实现需求：

ALTER TABLE roles 
ADD UNIQUE INDEX idx_unique_prf (personal_id, role, favorite);

创建该索引后，任何插入重复personal_id+role+favorite组合的操作都会被MySQL直接拒绝，并返回错误信息，无需依赖触发器。

为什么原写法无效？ #

原触发器中，无论检测结果如何，原INSERT语句都会正常执行：

• 当存在重复时，atExists>0，触发器里的INSERT不会执行，但原INSERT还是会把重复数据插入表中；
• 当不存在重复时，触发器里执行一次INSERT，原INSERT又执行一次，反而会导致同一条数据被插入两次。

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