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

12 comments:

Scott Noyes said...

Why not just use a composite primary key?

Scott Noyes said...

I guess a link would have been useful. See the second example at http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Frankly Speaking! said...

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

Anonymous said...

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

Anonymous said...

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

Pam said...

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

sorry bad ENGLISH said...

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!

Getz said...

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.

Anthony Bush said...

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)

Anonymous said...

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

cheers

Paul Smith said...

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.

darky said...

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