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
auto_id
for? – ypercubeᵀᴹ Jul 2 '13 at 6:02