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
at

2 comments:

  1. pabloj5:10 AM

    The solution is easy (more or less ... :-D), don't go the autoincrement way, but implement sequences and triggers ....

    ReplyDelete
  2. Anonymous9:26 AM

    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

    ReplyDelete