4

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?

Mark Davidson
  • 5,503
  • 5
  • 35
  • 54
Norman
  • 6,159
  • 23
  • 88
  • 141
  • Why not just create a view that returns that concatenated value? There is no real need to store this. –  Aug 23 '13 at 12:20
  • I need the value to use elsewhere. I've only used 2 values here. There are many more that'll be concatenated. – Norman Aug 23 '13 at 12:33

1 Answers1

5

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);
Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
  • If I change it too after, I get the error :`/* SQL Error (1362): Updating of NEW row is not allowed in after trigger */`. I using HeidiSql to do this. – Norman Aug 23 '13 at 10:30
  • @Norman As far as I know, there is no way of achieve what you are trying to to by using a trigger. I edited my answer to provide some alternative solutions. – Sylvain Leroux Aug 23 '13 at 10:42
  • @Norman The `ALTER TABLE..` create a *new index* on you table preventing you from having *duplicate* values for `(countryCode, id)`. I only speculated that was what you were trying to achieve based on the name of your column `key`. Maybe you should redefine *what* you are trying to do, since at this point there seems to be no "easy" way of doing it with MySQL the way intended first. – Sylvain Leroux Aug 23 '13 at 11:00
  • Can you use select with the last inserted record and use an after insert trigger? So after a record is inserted, select the last inserted row, do the concatenation and update. Something like that possible? – Norman Aug 23 '13 at 12:18
  • `KEY` doesn't prevent duplicates, only `UNIQUE` does that. – Ja͢ck Aug 23 '13 at 12:57
  • @Jack Thank you for pointing that. It was a stupid typo forgetting the keyword `UNIQUE` :/ – Sylvain Leroux Aug 23 '13 at 12:59