CODEWITHSUNDEEP
mysqlsql
John Conde
John Conde

Reputation: 219834

Getting and using the unique ID when making an insert

I am working on a legacy system that generates confirmation numbers based on the ID of their order record in the database. It's just a bunch of zeros append to the front of the row ID which is created automatically using auto increment. Currently the system is inserting the order into the database and then immediately doing an update to add the confirmation number (after getting the insert ID programmatically).

I'd like to reduce this second step if at all possible by adding the confirmation number when the order is placed. However, I am unaware of how this can be done, or if it can be done, via SQL. Is this possible? If so, how? I've Googled this but I just get results telling me how to get the last insert ID after the query has already run.

Upvotes: 2

Views: 93

Answers (1)

dani herrera
dani herrera

Reputation: 51675

You can write a trigger that is execute where order is inserted.

Sample:

CREATE TRIGGER order_zeros BEFORE INSERT ON orders
for each row
begin
  SET NEW.confirmation = LPAD( NEW.id, 8, '0' );
end;

Disclaimer: this code is not tested, only to explain solution.

Upvotes: 3

Related Questions