Back in 2000 I posted a tip on using SQL Server triggers to obtain assigned identity field values. Someone recently asked if this was doable in MySQL, and the answer is yes, now that MySQL 5 finally provides basic trigger support. Suppose you had a table named products which contained a column named product_id which was defined as AUTO_INCREMENT. You could use the following statement to create a trigger which would return the newly assigned product_id each time a new product was inserted.
CREATE TRIGGER newproductid AFTER INSERT ON products
FOR EACH ROW SELECT NEW.product_id;
Obviously, this would require MySQL 5 to work.
Leave a Reply