MySQL trigger before insert update column with new auto-increment id
mysql, sql
Solution
`AUTO_INCREMENT` column are only set after insert.
If you need to access that value, you can only in an `AFTER INSERT` trigger. However, you cannot modify a column value in an `AFTER UPDATE` trigger...
In addition, you cannot perform an update on the table used in your `AFTER INSERT` trigger as (http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html):
Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
Here the only reasonable solution would be to create a stored procedure to update the table, adjusting the relevant columns in a transaction to "emulate" you atomic insert statement.
That being said, in your particular case, the `key` column is redundant as that column is just the concatenation of two other columns of the same row.
Given its name, aren't you looking for a way to create a compound key instead? Something like that:
ALTER TABLE tbl ADD UNIQUE KEY (countryCode, id);
Problem
I'd like to update a column in my table with the country code and the new `id` which is an auto-increment value. ``` BEGIN SET new.key = concat(new.countryCode,new.id); END ``` `countryCode` works fine, but the `id` is always blank. How can I achieve something like this? The `id` comes from an `autoincrement` column. I know it's not working because it's generated after the insert. So how can I do something like this?