MySQL call before inserting update with new auto increment id

Question

I would like to update a column in my table with a country code and a new id , which is the auto increment value.

 BEGIN SET new.key = concat(new.countryCode,new.id); END 

countryCode working fine, but id always empty. How can I achieve something like this? id comes from the autoincrement column.

I know that it does not work, because it is generated after insertion. So how can I do something like this?

Answer 1

Column

AUTO_INCREMENT set only after insertion.

If you need to access this value, you can only use the AFTER INSERT trigger. However, you cannot change the value of a column in an AFTER UPDATE trigger ...

In addition, you cannot update the table used in your AFTER INSERT trigger, like ( http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html ):

Inside a stored function or trigger, it is not allowed to modify a table that is already used (for reading or writing) by the operator that calls the function or trigger.

---

Here, the only sensible solution would be to create a stored procedure to update the table by adjusting the corresponding columns in the transaction to "emulate" your atomic insertion operator.

---

In this case, in your particular case, the key column is redundant, since this column is simply a concatenation of two other columns of the same row.

Given his name, are you not looking for a way to create a composite key? Something like that:

 ALTER TABLE tbl ADD UNIQUE KEY (countryCode, id);