Saturday, March 25, 2006

MySQL triggers - Accessing the value of auto-increment field?

I would like to set a trigger where upon inserting a new record with permalink set to "myfilename" would become "myfilename-id" where id represents the auto-incremented value of the id field. So I tried:

drop trigger article_filename;
CREATE TRIGGER article_filename BEFORE INSERT ON adoppt.articles2 FOR EACH ROW SET NEW.permalink = CONCAT(NEW.permalink, "-" , NEW.id );



However this creates a permalink of myfilename-0 as the id value was obviously not available.

So I tried changing the above to AFTER INSERT:

CREATE TRIGGER article_filename AFTER INSERT ON adoppt.articles2 FOR EACH ROW SET NEW.permalink = CONCAT(NEW.permalink, "-" , NEW.id );



and got:

#1362 - Updating of NEW row is not allowed in after trigger



So I tried

CREATE TRIGGER article_filename BEFORE INSERT ON adoppt.articles2 FOR EACH ROW UPDATE articles2 SET permalink = CONCAT(NEW.permalink, "-" , NEW.id );



and although the trigger was successfully created, I get the following error when trying to insert a record.

#1442 - Can't update table 'articles2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.



The solution to above is to do what I was doing above but then I don't get the value of the auto-incremented field.

Any suggestions on how I can get the auto-incremented id in the above trigger? If not, what are my options?

Many thanks in advance.

-- Frank
at

10 comments:

  1. Frank3:15 PM

    Thanks Scott for the suggestions. I am looking into the options and will post them here.

    ReplyDelete
  2. Anonymous3:05 PM

    if you ever solved this please give me an email, i am trying to do the same thing...forrestfsu [at] aol doot com

    ReplyDelete
  3. Anonymous10:59 AM

    Here is how you can get it without a trigger in PHP. function is mysql_insert_key()

    http://www.plus2net.com/sql_tutorial/mysql_insert_id.php

    ReplyDelete
  4. Anonymous3:54 PM

    Try using this

    CREATE TABLE Test.Prueba (
    MyId int(10) unsigned NOT NULL auto_increment,
    idDup int(10) unsigned NOT NULL,
    );


    CREATE TRIGGER Test.Prueba_InsAfter
    BEFORE INSERT ON Test.Prueba
    FOR EACH ROW BEGIN
    SET New.idDup = LAST_INSERT_ID()+1;
    END;

    This store the auto-generated MyId in idDup.

    I don't know if this works always.

    Does anybody knows ?

    Please any comments to info@fairware.cl

    ReplyDelete
  5. Anonymous11:15 PM

    LAST_INSERT_ID() can effected by other table's AUTO_INCREMENT column.

    so.. i decide...... update one more.

    this posting top of google's search result on "mysql trigger auto_increment" so if anyone know answer.

    LET US KNOW!

    ReplyDelete
  6. Anonymous10:50 AM

    Trigger is:

    DECLARE NewID INT;
    SELECT MAX([auto_increment_column])+1 INTO NewID from articles2;
    SET @x:=CAST(NewID AS CHAR);
    SET NEW.permalink=CONCAT(NEW.permalink,"-",@x);

    I Hope this solve your problem.

    ReplyDelete
  7. Anonymous5:29 PM

    The above does not work when using master to master replication due to the auto_increment_increment value not being equal to 1 and MAX() returning the wrong value.

    For example, instead of:

    SELECT MAX([auto_increment_column]) + 1 INTO NewID from articles2;

    You could try:

    SELECT MAX([auto_increment_column]) + @@session.auto_increment_increment INTO NewID from articles2;

    But, it will fail because MAX([auto_increment_column]) could return a value too high and/or with wrong offset.

    Consider the master to master setup:

    Server 1 with auto_increment_increment = 2, auto_increment_offset = 1;
    Server 2 with auto_increment_increment = 2, auto_increment_offset = 2;

    Server 1: INSERT INTO tbl (col) "test1"; // auto_increment_column gets 1
    Server 1: INSERT INTO tbl (col) "test2"; // auto_increment_column gets 3
    Server 1: INSERT INTO tbl (col) "test3"; // auto_increment_column gets 5
    Server 2: INSERT INTO tbl (col) "test4"; // auto_increment_column gets 2

    The last insert on server 2 would fail because MAX([auto_increment_column]) returns 5, we add two to it getting 7, which is wrong for too reasons:

    1. It has the wrong offset (in this 2-server setup, it should be even number, not odd), and
    2. It has too high of a value (should be 2, not 6 or 4)

    ReplyDelete
  8. Anonymous2:15 PM

    http://forums.mysql.com/read.php?99,142933,143103#msg-143103

    cheers

    ReplyDelete
  9. Paul Smith5:57 PM

    I found a solution that seems to work.

    CREATE TRIGGER `event` BEFORE INSERT ON `table` FOR EACH ROW SET NEW.column2=(SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='database' AND `TABLE_NAME`='table');

    I havnt tested this on a new table with no rows and ive noticed that AUTO_INCREMENT is sometimes null instead of 1 so maybe IFNULL() would be needed.

    ReplyDelete
  10. darky9:43 AM

    Last solution from Paul Smith works well, here's a function I created, hope it'll be helpful:

    DELIMITER $$

    CREATE FUNCTION get_next_auto_incr( t_name VARCHAR(64) ) RETURNS BIGINT(21)
    BEGIN

    DECLARE auto_inc BIGINT(21);

    SELECT AUTO_INCREMENT INTO auto_inc FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME = t_name;

    RETURN auto_inc;

    END $$

    ReplyDelete