Migration from Oracle to MariaDB - Trigger, Self-referencing table

Question

I'm migrating my Oracle database to MariaDB, and I am unsure how to create a self-referencing ID on one of my tables.

Essentially, if a belongs_to_id is not specified, it will assume that it belongs to itself (and be the same as the ID generated ON INSERT); however, if a belongs_to_id has been specified, then it will use that instead.

The reason I do this is because I have posts and replies stored in the same table. If the id = belongs_to_id then it's an initiating post, whereas if it differs, it is a reply to another post.

I've thought about letting my application (Node JS) do a quick UPDATE using last_insert_id - but it seems rather messy and I'd like to avoid that if possible.

Here's what I use in Oracle - does anyone know how I replicate this in MariaDB please?

CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN    
    :new.id := my_sequence.NEXTVAL;
    :new.belongs_to_id := NVL(:new.belongs_to_id,:new.id);
END;
/

Answer 1

This should work in MariaDb as it closely conforms to SQL-99 syntax.

CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN    
    :new.id := my_sequence.NEXTVAL;
    :new.belongs_to_id := NVL(:new.belongs_to_id,:new.id);
END;

IF you set your new ID to be an auto increment then you can only use an after insert trigger. Something like

CREATE OR REPLACE TRIGGER my_trigger
AFTER INSERT ON my_table
FOR EACH ROW
v_id NUMBER:
BEGIN    
    v_id := LAST_INSERT_ID();
    :old.belongs_to_id := NVL(:old.belongs_to_id,v_id);
END;