Programming

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:

Code:

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

Code:

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:

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:

Code:

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

Code:

for each row set new.col_comb2  =  concat( new.colc,  ' - ',  new.cola,  ', ',  new.colb );

thanks