Update hierarchical columns with primary key value when they are null

I have a table like:

When I insert a new row that will be a root (eg. parentId and rootId = id), i need a trigger to update parentId and rootId with the value of "id" (autoincrementing primary key)

However, I can't seem to update the inserted row with and AFTER INSERT trigger and I can't seem to reference the value of ID with a BEFORE INSERT trigger.

I'm using 10.6.16-MariaDB-log

• MyPHP

Attempt #1 Result: "Updating of NEW row not allowed in AFTER trigger"

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

Attempt #2 Result: No error, but parent/root key not updated

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

I've tried a wide variety of other methods but can't seem to get it to work

You can't use auto increment columns in a trigger that way, but you can use a column that defaults from a sequence:

create sequence TBL_COMMENTS_id;
create table TBL_COMMENTS (id int not null default nextval(TBL_COMMENTS_id) primary key, parentCommentId int, rootCommentId int);
delimiter $$
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
$$
delimiter ;
insert into TBL_COMMENTS () values ();
select * from TBL_COMMENTS;

+----+-----------------+---------------+
| id | parentCommentId | rootCommentId |
+----+-----------------+---------------+
|  1 |               1 |             1 |
+----+-----------------+---------------+