0

I am following mysql docs to create a sequence for a table

Main table:

CREATE TABLE person (
    id   INT UNSIGNED PRIMARY KEY,
    name VARCHAR(255)
);

Sequence table:

CREATE TABLE person_sequence (
    id INT NOT NULL
);
INSERT INTO person_sequence VALUES (0);

Trigger:

CREATE TRIGGER person_before_insert
    BEFORE INSERT ON person
    FOR EACH ROW
BEGIN
    IF new.id is null THEN
        UPDATE person_sequence SET id=LAST_INSERT_ID(id+1);
        SET new.id = (SELECT LAST_INSERT_ID());
    END IF;
END;

When I try to insert into the main table:

INSERT INTO person (name) VALUES ('John');
SELECT LAST_INSERT_ID();

The insert with sequence works fine. but the SELECT LAST_INSERT_ID(); returns 0

Limitation: I can't modify the insert query(to first call the sequence and then insert with id provided in the insert query).

Why don't I use an AUTO_INCREMENT id for the main table? part of data is replicated from another database which has its own sequence and id range.

Update

Also doesn't work if I insert into the sequence table. changes:

CREATE TABLE person_sequence (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
);

CREATE TRIGGER person_before_insert
    BEFORE INSERT
    ON person
    FOR EACH ROW
BEGIN
    IF new.id is null THEN
        insert into person_sequence (id) values (null);
        SET new.id = (SELECT LAST_INSERT_ID());
    END IF;
END;
Hadi Mansouri
  • 76
  • 6
  • 1
    You have NOT done an INSERT prior to using the `LAST_INSERT_ID()` because this is a `BEFORE INSERT` trigger :) and it should probably have been coded as `LAST_INSERT_ID()+1` anyway – RiggsFolly Jun 28 '22 at 15:50
  • check this answer https://stackoverflow.com/questions/13880267/mysql-last-insert-id-returns-0 , still, if you want the last Id you can use " SELECT id FROM person ORDER BY id DESC LIMIT 1 " – Fatiha IMOUSSAINE Jun 28 '22 at 15:53

0 Answers0