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 ....

BG 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