Auto increment value needs to be changed inside trigger I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table.

Original structure:

id varchar(15),
seqno int(11) auto increment 
Joint PK index on the above 2 fields


New structure:

auto_id INT (11) auto increment,
id varchar(15),
seqno int(11)


The original structure had a joint PK on id and seqno with seqno being auto incremented for each unique id value. It would then return the seqno as the last_insert_id in my web application.

With the new innodb structure, because innodb doesnt support joint primary keys, I've had to add in an auto_id which now has the auto increment.

I then implemented a trigger to generate the seqno unique per id value. My question is how do I set the auto increment id so that it returns in place of the auto_id value? This trigger would be called when I insert into my_table, so that it can calculate the next seqno value to insert with.

DROP TRIGGER IF EXISTS `innodb_seqno_trigger`;
//

CREATE TRIGGER `innodb_seqno_trigger` BEFORE INSERT ON `my_table`
 FOR EACH ROW BEGIN

SET @newseqno = 0;

SELECT MAX(seqno) INTO @newseqno
FROM my_table
WHERE id = NEW.id;

SET NEW.seqno = (@newseqno + 1);
/* HERE I NEED HELP PLEASE TO GET THE AUTO INCREMENT VALUE TO ACTUALLY RETURN NEW.seqno */
END;
//


Thanks Peter
I don't think you can get the new value of auto_id in a BEFORE insert trigger. This will do what you want (if I have understood correctly): 

CREATE TRIGGER innodb_seqno_trigger 
   BEFORE INSERT ON my_table 
   FOR EACH ROW 
BEGIN

  SELECT MAX(seqno) INTO @newseqno 
  FROM my_table 
  WHERE id = NEW.id;

  SET NEW.seqno = COALESCE(@newseqno + 1, 1); 

END; 


Test at SQL-Fiddle

But have you thought what will happen if you have 2 separate connections/threads/transactions that try to insert the same id?