Insert using LAST_INSERT_ID

Question

How would I go about inserting while incrementing and using LAST_INSERT_ID? I have a table and trigger as such:

CREATE TABLE A(
    id int NOT NULL AUTO_INCREMENT,
    name char(15),
    PRIMARY KEY(id)
);

CREATE TABLE B(
    id int NOT NULL,
    name char(15),
    FOREIGN KEY(id) REFERENCES A(id)
);

delimiter //

CREATE TRIGGER T
AFTER INSERT ON B
FOR EACH ROW 
BEGIN
     IF (NEW.name LIKE 'A') THEN
          INSERT INTO A VALUES(LAST_INSERT_ID() + 1, 'A');
     END IF;
END//

delimiter ;

I know that INSERT INTO B VALUES(LAST_INSERT_ID() + 1, 'A'); doesn't work if I have multiple inserts into B because LAST_INSERT_ID() when doing multiple-row inserts, LAST_INSERT_ID() will return the value of the first row inserted (not the last). How would I go about incrementing the ID so that LAST_INSERT_ID() returns the most recent ID from the inserts`

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-02-27T03:21:25

If you want to insert the id from B, then use the id column:

CREATE TRIGGER T
AFTER INSERT ON B
FOR EACH ROW 
BEGIN
     IF (NEW.name LIKE 'A') THEN
          INSERT INTO A VALUES(B.ID + 1, 'A');
     END IF;
END//

This seems like a curious expression. Perhaps your real intention is simply to have an auto incremented id on both tables.

Insert using LAST_INSERT_ID

2 Answers