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.
id varchar(15), seqno int(11) auto increment Joint PK index on the above 2 fields
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; //