How do I get the auto incremented id in a before insert trigger for mySQL?

Issue

I need to create a trigger in mySQL that uses the auto incremented id to fill another column.
Let’s say the id is "12", i need another column to be automatically filled with "12-xxx".
I’m trying to do that using an before insert trigger but it is not working.

CREATE TRIGGER TR_CARTAO_BI BEFORE INSERT ON CARTAO FOR EACH ROW
BEGIN
    SET NEW.NUMERO = CONCAT(NEW.IDCARTAO, '-XXX');
END $$
  • It seems that I can’t use the id in the before insert trigger because it hasn’t been generated yet;
  • I heard about the query "SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES…" that returns the next auto increment element but sometimes it works, sometimes it doesn’t;
  • I tried to use an after insert trigger but apparently you can’t change the row that you are inserting in the after insert trigger;

Solution

From your question it looks like you’ve exhausted all possible routes (I would have initially suggested the SELECT AUTO_INCREMENT query, but this isn’t reliable for you) that avoid using a secondary table.

So, as a hacky work-around, you could try this instead… You could use an AFTER INSERT trigger to create a row in a secondary table, which would have the ID of the row you just created and your secondary column with the ID-XXX value. On the secondary table, set up an AFTER INSERT trigger to update your primary table row with the ID-XXX value.

This could be expensive, depending on your use-case and velocity of transactions etc. But I thought I’d offer lateral thinking…

Answered By – Jonathan Nathanson

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published