如何创建支持多行插入的SQL AFTER INSERT触发器？

看起来你碰到了触发器在批量插入时失效的问题，结合你给出的表结构，我来帮你拆解可能的原因和解决办法：

1. 先排查触发器的类型与逻辑漏洞 #

触发器分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT），这两种类型在多行插入时的表现完全不同：

• 行级触发器会为每一行插入单独触发一次，理论上多行插入时每行都会执行触发器逻辑，除非你的逻辑依赖了全局共享状态（比如共享变量）导致冲突。
• 语句级触发器仅在整个插入语句执行时触发一次，如果你没有遍历所有插入的行，就会导致只有部分行被正确处理。

最常见的错误场景（以自动生成主键为例） #

假设你的触发器是用来自动生成id_pengiriman的，比如下面这种行级触发器：

DELIMITER //
CREATE TRIGGER trg_gen_pengiriman_id
BEFORE INSERT ON tb_pengiriman
FOR EACH ROW
BEGIN
    DECLARE latest_id INT;
    -- 获取当前最大的ID后缀
    SELECT CAST(SUBSTRING(MAX(id_pengiriman), 3) AS UNSIGNED) INTO latest_id FROM tb_pengiriman;
    -- 生成新ID
    SET NEW.id_pengiriman = CONCAT('PE', LPAD(IFNULL(latest_id, 0) + 1, 3, '0'));
END //
DELIMITER ;

这种逻辑在单行插入时完全正常，但多行插入时，所有行都会读取同一个latest_id，导致生成重复的id_pengiriman，直接触发主键冲突错误。

对应解决方案 #

方案A：改用序列（Sequence）生成唯一ID

如果你的数据库支持序列（比如MySQL 8.0+、PostgreSQL、Oracle），这是最稳妥的方式：

-- MySQL 8.0+ 创建序列
CREATE SEQUENCE seq_pengiriman_id START WITH 1 INCREMENT BY 1;

-- 修改触发器使用序列生成ID
DELIMITER //
CREATE TRIGGER trg_gen_pengiriman_id
BEFORE INSERT ON tb_pengiriman
FOR EACH ROW
BEGIN
    SET NEW.id_pengiriman = CONCAT('PE', LPAD(NEXTVAL(seq_pengiriman_id), 3, '0'));
END //
DELIMITER ;

这样每次插入时都会获取序列的下一个唯一值，多行插入时也能保证ID不重复。

方案B：调整语句级触发器逻辑遍历所有插入行

如果是语句级触发器，需要遍历插入的所有行集合（比如SQL Server的INSERTED表、PostgreSQL的NEW TABLE）。以MySQL为例，你可以通过临时表来捕获并处理批量插入的数据：

DELIMITER //
CREATE TRIGGER trg_gen_pengiriman_id_batch
BEFORE INSERT ON tb_pengiriman
FOR EACH STATEMENT
BEGIN
    -- 创建临时表存储待插入数据
    CREATE TEMPORARY TABLE temp_insert (provinsi VARCHAR(30), kota VARCHAR(30), harga_ongkir BIGINT);
    -- 捕获当前要插入的所有行（这里需要结合你的插入逻辑调整，比如如果是INSERT...SELECT，直接捕获源数据）
    INSERT INTO temp_insert SELECT provinsi, kota, harga_ongkir FROM your_source_table;
    
    -- 遍历临时表生成唯一ID并插入到主表
    DECLARE done INT DEFAULT 0;
    DECLARE v_provinsi VARCHAR(30);
    DECLARE v_kota VARCHAR(30);
    DECLARE v_harga BIGINT;
    DECLARE cur CURSOR FOR SELECT * FROM temp_insert;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_provinsi, v_kota, v_harga;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 生成唯一ID
        SET @new_id = CONCAT('PE', LPAD(NEXTVAL(seq_pengiriman_id), 3, '0'));
        INSERT INTO tb_pengiriman(id_pengiriman, provinsi, kota, harga_ongkir) VALUES(@new_id, v_provinsi, v_kota, v_harga);
    END LOOP;
    CLOSE cur;
    
    -- 阻止原始插入（因为我们已经通过触发器完成插入）
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Batch insert handled by trigger';
END //
DELIMITER ;

2. 修复表中检查约束的逻辑错误 #

另外，我注意到你添加的cek_kota_banten约束存在逻辑问题：

alter table tb_pengiriman add constraint cek_kota_banten check ((provinsi = 'Banten') and (kota in ('Tangerang', 'Serang', 'Cilegon', 'Lebak', 'Pandeglang')));

这个约束会导致所有非Banten省份的插入都失败——因为当provinsi != 'Banten'时，(provinsi = 'Banten')为false，整个约束表达式结果为false，直接违反检查约束。

正确的逻辑应该是：当省份是Banten时，城市必须在指定列表中；其他省份无限制，修改后的约束如下：

alter table tb_pengiriman add constraint cek_kota_banten check ((provinsi != 'Banten') OR (kota in ('Tangerang', 'Serang', 'Cilegon', 'Lebak', 'Pandeglang')));

总结 #

• 如果触发器逻辑依赖全局状态导致多行冲突：优先改用序列生成唯一值，避免共享变量带来的问题。
• 如果是语句级触发器未遍历所有行：调整逻辑处理插入的全量行集合。
• 同时修复检查约束的逻辑错误，避免不必要的插入失败。

内容的提问来源于stack exchange，提问作者Alifa Al Farizi