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

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;

CodePudding user response：

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...