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
10 comments:
Thanks Scott for the suggestions. I am looking into the options and will post them here.
if you ever solved this please give me an email, i am trying to do the same thing...forrestfsu [at] aol doot com
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
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
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!
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.
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)
http://forums.mysql.com/read.php?99,142933,143103#msg-143103
cheers
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.
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 $$
Post a Comment