CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

MySQL trigger before insert update column with new auto-increment id

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?

Upvotes: 4

Views: 5583

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 52020

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);

Upvotes: 5

Related Questions