dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
2563
share rss forum feed


Nordy

join:2007-10-20
kudos:1

MySQL trigger before insert and auto-increment data

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 auto-increment column.

I know it's not working because it's generated after the insert. So how can I do something like this?


cowboyro
Premium
join:2000-10-11
Shelton, CT
Cast the numeric id to a char type.


Nordy

join:2007-10-20
kudos:1
Umm... you mean change it from INT to CHAR? What do you mean by cast the numeric id to a char type?


cowboyro
Premium
join:2000-10-11
Shelton, CT
reply to Nordy
CONCAT takes strings as arguments
SET new.key = concat(new.countryCode,CAST(new.id AS VARCHAR(20));

Side note - if you want to use this column as a lookup, you may be better off creating an index from the id and countrycode fields especially if you'll have many records - string lookups are slow, int lookups are fast.


Nordy

join:2007-10-20
kudos:1
Somehow, VARCHAR(X) wont work in the trigger. I can only use CHAR. And with CHAR the result is still blank. Only the country code, no new Id. Someone said this was not possible since it's a new Id...


cowboyro
Premium
join:2000-10-11
Shelton, CT
reply to Nordy
Does the trigger fire before of after the insert?


hairspring

join:2007-11-23
Oakville, ON
Reviews:
·Cogeco Cable
reply to Nordy
You won't see a value for the NEW row id until the row is inserted. This would suggest an AFTER trigger, but the subject table cannot be updated this way..... so it's a bit of a catch 22 to do with auto-increment columns.

2 Options:

1) Control inserts to this table with a stored procedure, and calculate the 'key' column's data after the insert. Wrap the INSERT and UPDATE in a transaction.

2) Your 'key' column is redundant, since it can be constructed at query time. Provide a view that calculates 'key' to your devs and block access to the base table.

#1 is faster, since the data is stored and can be indexed.
#2 simplifies the base tables.


Nordy

join:2007-10-20
kudos:1
reply to cowboyro
@cowboyro before Insert
@hairspring I'm thinking of doing a trigger that's called after Insert, that'll call another stored procedure. How's that?


hairspring

join:2007-11-23
Oakville, ON
Reviews:
·Cogeco Cable
said by Nordy:

@hairspring I'm thinking of doing a trigger that's called after Insert, that'll call another stored procedure. How's that?

I've never attempted that. Depending on the storage engine you're using, you may run into locking issues.


Nordy

join:2007-10-20
kudos:1
I did. I've given up on this