How could I update the LAST_INSERT_ID in Mysql Trigger

Question

In my MySQL Database, I have a table with a composite primary key where the ID is not in auto_increment mode. Something like this :

CREATE TABLE table_a (
    fk_table_b INT UNSIGNED NOT NULL,
    id INT UNSIGNED,
    label VARCHAR(80) NOT NULL,
    PRIMARY KEY (fk_table_b, id),
    FOREIGN KEY fk_table_b
        REFERENCES table_b(id)
);

To increment the ID in function of the foreign key, I made a trigger like this :

DELIMITER $$
CREATE TRIGGER table_a_auto_increment
BEFORE INSERT ON table_a
FOR EACH ROW BEGIN
    SET NEW.id = (
        SELECT IFNULL(MAX(id), 0) + 1
        FROM table_a
        WHERE table_a.fk_table_b  = NEW.fk_table_b
    ); 
END $$
DELIMITER ;

But when I do SELECT LAST_INSERT_ID() I am getting 0 as the new id ... Normally you could override the LAST_INSERT_ID() by giving it a number like this :

INSERT table_a ( fk_table_b, id)
VALUES (1, LAST_INSERT_ID(5));
SELECT LAST_INSERT_ID(); -- -> it gives me 5

So I have tried to combine both to do this trigger :

DELIMITER $$
CREATE TRIGGER table_a_auto_increment
BEFORE INSERT ON table_a
FOR EACH ROW BEGIN
    SET NEW.id = (
        SELECT LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1)
        FROM table_a
        WHERE table_a.fk_table_b  = NEW.fk_table_b
    ); 
END $$
DELIMITER ;

But it's still giving me 0 when I insert something in the base ... Do you know if there is a way to make it work ?

Thanks a lot.

-- EDIT 2020-08-14

Finally it seems impossible to override the LAST_INSERT_ID function inside the TRIGGER, so I changed my solution by removing the trigger and doing it inside my insert function like this :

INSERT table_a ( fk_table_b, id, label)
VALUES (1, LAST_INSERT_ID((
    SELECT IFNULL(MAX(old_one.id), 0) + 1
    FROM table_a AS old_one
    WHERE old_one.fk_table_b  = table_a.fk_table_b
)), "something");

And then, this is giving me the good result I can use in my backend :)

I'm at loss to understand why you want to use last_insert_id() if there is no auto increment involved. — Shadow, Aug 13 '20 at 08:48
Does this answer your question? [For a primary key of an integral type, why is it important to avoid gaps?](https://stackoverflow.com/questions/2535483/for-a-primary-key-of-an-integral-type-why-is-it-important-to-avoid-gaps) — danblack, Aug 13 '20 at 08:50
@Shadow because of my backend code ... It devolves me the Lat_Insert_Id which I need then to link some other tables ... exemple at_table_a_table_c ... I need the id of table_a to insert something in this associative table ... — Simon Trichereau, Aug 13 '20 at 08:55
@danblack not answering nothing ... There is no gap in my table ... and as the answer say : "All that matters with the primary key is that it is unique to all the data in the table. Doesn't matter what the value is, or if the records before and after are sequencial values." — Simon Trichereau, Aug 13 '20 at 08:56
@Simon Trichereau LAST_INSERT_ID only works with an auto_increment column — P.Salmon, Aug 13 '20 at 09:08
@P.Salmon as i said in my question you can override the value ( only INT ) of Last_insert_id (so you can do what you want, not only in AUTO_INCREMENT column) and that's what i tried to do ... but it seems impossible to do in a Trigger — Simon Trichereau, Aug 13 '20 at 09:20

score 0 · Answer 1 · answered Aug 13 '20 at 09:13

0

You may use additional service table:

CREATE TABLE service_table (id BIGINT AUTO_INCREMENT PRIMARY KEY);

and

DELIMITER $$
CREATE TRIGGER table_a_auto_increment
BEFORE INSERT ON table_a
FOR EACH ROW 
BEGIN
    SET NEW.id = (
        SELECT IFNULL(MAX(id), 0) + 1
        FROM table_a
        WHERE table_a.fk_table_b  = NEW.fk_table_b
    ); 
    DELETE FROM service_table WHERE id IS NOT NULL;
    INSERT INTO service_table VALUES (NEW.id - 1);
    INSERT INTO service_table VALUES (NULL);
    SET NEW.id = LAST_INSERT_ID() - 1;
END $$
DELIMITER ;

fiddle (foreign key removed).

Maybe the code may be simplified a little - do it yourself.
Service table may be defined as Engine = MEMORY (if available).
The code is not safe for concurrent inserts.

answered Aug 13 '20 at 09:13

Akina

39,301
5
14
25

it doesn't work ... try to do `SELECT LAST_INSERT_ID()` after a fresh Insert and it will give you 0 ... (I updated the fiddle) – Simon Trichereau Aug 13 '20 at 09:40
@SimonTrichereau *I updated the fiddle* I do not see the link. *after a fresh Insert* What is "fresh insert"? And why you doesn't conlrol all inserts? – Akina Aug 13 '20 at 10:34
sorry for the link : https://www.db-fiddle.com/f/9C7ab7zXf6z9sR4hRi1UNh/2 – Simon Trichereau Aug 13 '20 at 11:51
@SimonTrichereau What you have asked about? about to emulate autoincrement. The code do this. You have told nothing about retrieving generated value as a result of LAST_INSERT_ID() called after is the main task. PS. AI value generated in trigger cannot override the value returned by LAST_INSERT_ID() - although the value is generated, it occurs in a different scope and so cannot be visible. – Akina Aug 13 '20 at 11:55
Sorry for my bad english, maybe i was not explaining myself quite good, that was i was looking for and thanks for your answer, if it's impossible I can understand, but i didn't find nothing about it was impossible ... I just want MySQL to give me the Last_insert_id to the new value I inserted, but from a composite key and without auto increment ... But ok, I'll do something else to emulate this ;) – Simon Trichereau Aug 13 '20 at 12:00
@SimonTrichereau LAST_INSERT_ID() cannot be overrided. If you want to get inserted ID then use `SELECT MAX(id) FROM table WHERE (conditions by the inserted values)`. – Akina Aug 13 '20 at 13:14
*LAST_INSERT_ID() cannot be overrided* Yes you can : https://stackoverflow.com/questions/8746340/is-there-a-way-to-get-last-inserted-id-of-a-non-auto-incremented-column-in-mys – Simon Trichereau Aug 14 '20 at 09:16
@SimonTrichereau This needs explicit additional INSERT - so this is not the same slightly. If you want to return correct value after the procedure you must get it previously from the table by SELECT with the query mentioned above in the procedure. But you cannot insert into the same query - this will cause duplicate error (of course continue handler will help, but it must be checked that the handler does not affect the function output). – Akina Aug 14 '20 at 11:23

How could I update the LAST_INSERT_ID in Mysql Trigger

1 Answers1