MySQL Consulting and NoSQL Consulting: MySQL DBA: Once solution to triggers with Auto Increment field. How to access next auto increment value reliably?

Saturday, March 25, 2006

Once solution to triggers with Auto Increment field. How to access next auto increment value reliably?

In my earlier post I asked about how to create a trigger that can access the auto incremented value.

One solution is:

CREATE TRIGGER article_filename BEFORE INSERT ON adoppt.articles2 FOR EACH ROW SET NEW.permalink = CONCAT(NEW.permalink, "-" , ( SELECT MAX(id) FROM articles2 ) + 1 )

This however doesn't works properly if the lastest record, or the record with the highest value has been deleted after insertion.

Is there a reliable way to access the auto increment value for the next record? LAST_INSERT_ID() doesn't work if there was no insert in the current session.

--Frank

2 comments:

pabloj said...: The solution is easy (more or less ... :-D), don't go the autoincrement way, but implement sequences and triggers ....; 5:10 AM
Anonymous said...: You may try to do it like this:

CREATE TRIGGER `trg_sample` BEFORE INSERT ON `table`
FOR EACH ROW
BEGIN
DECLARE newid BIGINT(21);

SELECT AUTO_INCREMENT INTO newid FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'table';
...
END

I am not sure if it is 100% safe with concurrent inserts.

Solution based on:
http://forums.mysql.com/read.php?99,142933,143646#msg-143646; 9:26 AM