0

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;
/
ash
  • 1,224
  • 3
  • 26
  • 46

2 Answers2

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;
kevinskio
  • 4,431
  • 1
  • 22
  • 36
  • Thank you @kevinsky - I have set the table to be an autoincrement - can I access `my_sequence` in an object somewhere? (forgive my MariaDB ignorance) – ash Nov 18 '15 at 15:43
  • 2
    @doublesidedstickytape: Beware of the error that can occur: `Updating of NEW row is not allowed in after trigger`. – wchiquito Nov 18 '15 at 17:02
  • Thank you both very much - SO should have a 'buy a pint' button :) Do you know of there are there any risks in using LAST_INSERT_ID, say if there are multiple INSERT requests please? – ash Nov 18 '15 at 17:09
  • @doublesidedstickytape why not make a new question? – kevinskio Nov 18 '15 at 17:21
  • I've had some issues with this answer with it not compiling correctly - I did get something working and I have posted a new question: http://stackoverflow.com/questions/33800272/mariadb-self-referencing-trigger-before-insert – ash Nov 19 '15 at 10:12
1

Maybe simply

When INSERTing, set belongs_to_id to NULL (no TRIGGER). When SELECTing, do COALESCE(belongs_to_id, id).

Meanwhile, completely switch from SEQUENCE to AUTO_INCREMENT (again no TRIGGER).

Rick James
  • 135,179
  • 13
  • 127
  • 222