MariaDB触发器问题：层级列为空时用主键值更新

问题背景 #

现有表结构如下：

需求：插入根节点行时（即parentId为NULL），自动将parentId和rootId设置为该行自增主键id的值。

遇到的问题：

• 使用AFTER INSERT触发器时，直接修改NEW行报错：Updating of NEW row not allowed in AFTER trigger
• 使用BEFORE INSERT触发器时无报错，但parentId和rootId并未更新为正确的自增id值（因为此时自增id尚未生成）

环境：MariaDB 10.6.16-MariaDB-log + MyPHP

之前尝试的无效代码：

尝试1（AFTER触发器） #

CREATE TRIGGER xxx AFTER INSERT ON TBL_COMMENTS
FOR EACH ROW
BEGIN
    IF NEW.parentCommentId IS NULL THEN 
       SET NEW.parentCommentId = NEW.id, NEW.rootCommentId = NEW.id;
    END IF;
END

尝试2（BEFORE触发器） #

CREATE TRIGGER xxx BEFORE INSERT ON TBL_COMMENTS
FOR EACH ROW
BEGIN
    IF NEW.parentCommentId IS NULL THEN 
       SET NEW.parentCommentId = NEW.id, NEW.rootCommentId = NEW.id;
    END IF;
END

解决方案 #

方法1：使用AFTER INSERT触发器执行UPDATE操作 #

在AFTER触发器中，通过UPDATE语句直接修改刚插入的行，而非修改NEW对象。此时自增id已生成，可通过NEW.id定位目标行：

DELIMITER //
CREATE TRIGGER trg_comments_set_root AFTER INSERT ON TBL_COMMENTS
FOR EACH ROW
BEGIN
    IF NEW.parentCommentId IS NULL THEN 
        UPDATE TBL_COMMENTS
        SET parentCommentId = NEW.id, rootCommentId = NEW.id
        WHERE id = NEW.id;
    END IF;
END //
DELIMITER ;

关键说明：

• MariaDB的AFTER触发器不允许直接修改NEW行对象，但允许执行UPDATE语句操作目标表
• 触发器中NEW.id是插入后生成的自增主键值，可准确定位刚插入的行
• 执行触发器创建语句时需临时修改分隔符（DELIMITER //），避免BEGIN...END中的分号提前终止语句执行

方法2：程序端插入时直接关联自增id（可选） #

如果允许在业务代码中处理，可先执行插入获取自增id，再执行更新操作；或者针对根节点场景使用如下插入语句：

INSERT INTO TBL_COMMENTS (parentCommentId, rootCommentId)
VALUES (259780, 259780);

这种方式依赖259780的时效性，仅适用于单条插入的根节点场景，通用性不如触发器。

为什么之前的BEFORE触发器无效？ #

BEFORE INSERT阶段，自增主键id尚未被分配，此时NEW.id的值为0（或字段默认值），因此设置parentId和rootId为NEW.id实际是赋值为0，而非最终生成的自增id，导致不符合预期。

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