Hello,
I'm working with 5.0.18 (5.0.18-Debian_6-log specifically) with InnoDB engine and am trying to manipulate the value returned by LAST_INSERT_ID() by passing it an argument (expr), as is discussed at
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html. I have tried the example provided and it works as described. But when I try to do it inside a trigger, it does not seem to work.
I would like to do this inside a trigger because I need to manipulate the value of a column in a table when I insert a new row. Conceptually, you could think of the column as an auto_increment column, but in this case the value by which the column advances is not 1, but the result of a non-trivial calculation. This is a modification to an existing database, and the original column being replaced was an auto_increment column.
I am dealing with a large code base (PHP) that uses the mysql_insert_id() function to obtain the "ID" value that was last inserted. Modifying the code base is not a realistic option given the costs that would be involved, and hence my interest in manipulating the value returned by LAST_INSERT_ID() within a trigger so the code base can remain unchanged.
Here's a very simple example (for mysql utility) which reproduces what I have observed:
CREATE DATABASE last_insert_test;
USE last_insert_test;
CREATE TABLE table1 (
id1 int
) ENGINE=InnoDB;
CREATE TABLE table2 (
id2 int
) ENGINE=InnoDB;
INSERT INTO table2 VALUES(NULL);
delimiter //
CREATE TRIGGER table1_before_insert BEFORE INSERT ON table1 FOR EACH ROW
BEGIN
DECLARE last_id1 INT;
IF NEW.id1 IS NULL THEN
BEGIN
SELECT MAX(id1) INTO last_id1 FROM table1;
IF last_id1 IS NULL THEN
SET NEW.id1 = 101;
ELSE
SET NEW.id1 = last_id1 + 5;
END IF;
UPDATE table2 SET id2=LAST_INSERT_ID(NEW.id1);
END;
END IF;
END;//
delimiter ;
INSERT INTO table1 VALUES (NULL);
SELECT LAST_INSERT_ID(); # does not change to reflect last insert into table1
Any response that can account for what I am observing, point out a mistake I am making, or offer a working alternative solution, would be much appreciated.
Thanks,
Jeff Harp