MariaDB BEFORE INSERT触发器未更新特定列的技术咨询

首先，我注意到你的触发器在处理首次插入某公司某年份的发票时会出现核心问题：当没有匹配company_id和year且order_number不为空的记录时，MAX(year_order_nr)会返回NULL，而NULL + 1的结果仍然是NULL——这就导致year_order_nr被设置为NULL，后续拼接order_number时，LPAD(NULL, 8, 0)也会返回NULL，最终order_number的值就是NULL，看起来像是字段完全没被更新。

而测试列e1能正常设置，是因为它的赋值是固定值，不受NULL的影响。

接下来给你几个具体的解决步骤：

1. 修复year_order_nr的NULL值问题 #

用COALESCE()函数把MAX()的结果转为0，这样即使没有现有记录，也能生成从1开始的序号：

DELIMITER //
CREATE OR REPLACE TRIGGER `set_invoice_ordernumber` BEFORE INSERT ON invoices
FOR EACH ROW
BEGIN
    -- 用COALESCE处理MAX返回NULL的情况，默认取0再加1
    SET NEW.year_order_nr = COALESCE(
        (SELECT MAX(`year_order_nr`) FROM `invoices` WHERE `company_id`=NEW.company_id AND `year`=NEW.year AND `order_number` IS NOT NULL),
        0
    ) + 1;
    SET NEW.order_number = CONCAT(NEW.year, '-', LPAD(NEW.year_order_nr, 8, 0));
END;
//
DELIMITER ;

2. 检查INSERT语句是否显式赋值目标字段 #

如果你在INSERT时手动给year_order_nr或order_number赋了值（比如INSERT INTO invoices (...) VALUES (... , NULL, '')），虽然BEFORE触发器理论上会覆盖这些值，但如果字段允许NULL，而触发器生成了NULL值，就会出现“看似没更新”的情况。

建议INSERT时不要给这两个字段赋值，让触发器自动生成：

-- 正确的插入方式（不指定year_order_nr和order_number）
INSERT INTO invoices (company_id, year, ...其他字段...) VALUES (123, 2024, ...);

3. 验证查询逻辑是否正确 #

你可以单独执行触发器里的SELECT语句，代入你要插入的company_id和year，看看返回结果：

-- 替换X和Y为实际的公司ID和年份
SELECT MAX(`year_order_nr`) FROM `invoices` WHERE `company_id`=X AND `year`=Y AND `order_number` IS NOT NULL;

如果返回NULL，说明确实没有匹配的记录，这时候COALESCE就能解决问题；如果返回正常数字，但触发器还是没生效，那可能需要检查字段的权限或触发器的执行权限（不过你测试列能生效，权限问题概率很低）。

额外提示：并发插入的重复问题 #

如果你的系统存在多用户并发插入发票的情况，当前的触发器写法可能会导致year_order_nr重复（因为多个会话同时取MAX值，会得到相同的结果）。这种情况下，建议改用自增序列（Sequence）或者表级锁的方式来避免重复，比如：

-- 为每个公司和年份创建自增序列（MariaDB 10.3+支持）
CREATE SEQUENCE seq_invoice_ordernumber START WITH 1 INCREMENT BY 1;
-- 或者用表级锁确保并发安全（性能稍差，但兼容性好）
DELIMITER //
CREATE OR REPLACE TRIGGER `set_invoice_ordernumber` BEFORE INSERT ON invoices
FOR EACH ROW
BEGIN
    LOCK TABLES invoices WRITE;
    SET NEW.year_order_nr = COALESCE(
        (SELECT MAX(`year_order_nr`) FROM `invoices` WHERE `company_id`=NEW.company_id AND `year`=NEW.year AND `order_number` IS NOT NULL),
        0
    ) + 1;
    SET NEW.order_number = CONCAT(NEW.year, '-', LPAD(NEW.year_order_nr, 8, 0));
    UNLOCK TABLES;
END;
//
DELIMITER ;

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