Right I have a MYSQL database with table1 with 3 columns, colA, colB and colC. I want to combine the data in the 3 columns into a 4th column names col_comb. Here's the SQL command that works:
UPDATE table1 SET `col_comb` = CONCAT( `colA` , ' - ', `colB` , ', ', `colC` );
So now I want this to happen automaitically if a record is either inerted or updated in table1. I have tried setting up a trigger but it is not possible to use UPDATE in a trigger, here's an example:
php - MySql Error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger - Stack Overflow
Any suggestions on how to achieve this?
Thanks
Post your mySql database version and the code for your trigger you are attempting to use.
MYSQL v5.6.11
I am trying to achieve this using phpmyadmin but here's the code it generates
CREATE TRIGGER `concat` BEFORE INSERT ON `table1`
FOR EACH
ROW UPDATE table1 SET `col_comb` = CONCAT( `colA` , ' - ', `colB` , ', ', `colC` ) ;
Thanks
Since mySql doesn't allow "update or insert" syntax you will need to create a 'before insert' and a 'before update' trigger.
Example code:
create trigger before_insert_concat before insert on table1
for each row set col_comb = concat( new.cola, ' - ', new.colb, ', ', new.colc );
create trigger before_update_concat before update on table1
for each row set new.col_comb = concat( new.cola, ' - ', new.colb, ', ', new.colc );
1 Like
spacebar:
Since mySql doesn't allow "update or insert" syntax you will need to create a 'before insert' and a 'before update' trigger.
Example code:
create trigger before_insert_concat before insert on table1
for each row set col_comb = concat( new.cola, ' - ', new.colb, ', ', new.colc );
create trigger before_update_concat before update on table1
for each row set new.col_comb = concat( new.cola, ' - ', new.colb, ', ', new.colc );
Sorry I am a bit confused, does that mean I need to create a table named 'new' with 4 columns, 'col_comb', 'cola', colb', and 'colc'??
--------------Edit--------------------
Ignore all that, it works - thanks your a star
One additional thing, how do I add an additional SQL command to the trigger? For example say I want to create another column with the same data, ie:
create trigger before_update_concat before update on table1
for each row set new.col_comb = concat( new.cola, ' - ', new.colb, ', ', new.colc );
then add
for each row set new.col_comb2 = concat( new.colc, ' - ', new.cola, ', ', new.colb );
thanks