Visit Our UNIX and Linux User Community


MYSQL - trigger update on record insert or update


 
Thread Tools Search this Thread
Top Forums Programming MYSQL - trigger update on record insert or update
# 1  
Old 02-16-2014
MYSQL - trigger update on record insert or update

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
# 2  
Old 02-21-2014
Post your mySql database version and the code for your trigger you are attempting to use.
# 3  
Old 02-23-2014
Quote:
Originally Posted by spacebar
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
# 4  
Old 02-23-2014
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 );

This User Gave Thanks to spacebar For This Post:
# 5  
Old 02-24-2014
Quote:
Originally Posted by 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:
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 :-)

Last edited by barrydocks; 02-24-2014 at 06:45 PM..
# 6  
Old 05-19-2014
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

Previous Thread | Next Thread
Test Your Knowledge in Computers #546
Difficulty: Medium
In computer science, an operator takes the values of its operands and combines them in some way to produce a new value.
True or False?

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Insert one table and update another with shellscript

I have a problem with my shell script. I want to insert data from file to table1(empty) and then, compare table1 with table2 and update some fields. The first part is correct, but the second part does not work. The only way it works is if after the first part I truncate table1 and run the script... (1 Reply)
Discussion started by: nika_mill
1 Replies

2. UNIX for Dummies Questions & Answers

How to update DNS record in Solaris 5.10?

Hi I have a DNS server running on Solaris 5.10, I need to update ip address of two urls defined on our dns server how do i do that? and do i need to restart dns/named service after? how do i restart the service? Thanks (4 Replies)
Discussion started by: arocker
4 Replies

3. UNIX for Dummies Questions & Answers

Mysql: How to update value in 27000 rows?

Hello, some member created 27000 posts in wrong section (lol :D) so i need to edit all his entries to get new section ID. SELECT * FROM `phpbb_topics` WHERE `topic_first_poster_name` LIKE "%ozerway%"; this will select all his topics... the column with forum id is named "forum_id" and... (3 Replies)
Discussion started by: postcd
3 Replies

4. Programming

MYSQL - trigger to track changes to fields on update

So I have a php web application that allows updating of records. I would like to track the changes of only the fields that have changed. There are plenty of how to's that involve recreating the original row in a separate table with an additional time/date stamp column but I think this is a big... (1 Reply)
Discussion started by: barrydocks
1 Replies

5. Shell Programming and Scripting

Insert/Update using sed

Hi, I have a xml file (Config.xml) with following entry <Date="" Node1="50" Groups="20"> Now I want to use sed to insert/update the Date field with the latest date say - 20120711. I can't use a simple replace command becuase the Date field could be blank ("") or sometimes could have value in... (9 Replies)
Discussion started by: vivek_damodaran
9 Replies

6. Shell Programming and Scripting

Update trigger for unix file (solaris)

Hello, from log error file of process that i's updating in append mode, i need to capture the new entries for every day. How i can know and save only the new errors? This it's a sample of error log file (oas report server engine) thanks and regards Fran (2 Replies)
Discussion started by: fran61
2 Replies

7. UNIX and Linux Applications

update trigger

hi all, i hope i am posting this /beginner) question in the right forum: i want to create an update trigger, which rolls back a transaction if a record of a table is updated. the table has - amongst others - a field 'statusid' - if a record in this table has the statusid X and it is attempted... (0 Replies)
Discussion started by: kalinkula
0 Replies

8. Shell Programming and Scripting

awk script to update header record

I am using HP UX and think this may be done with awk but bot sure. I have a file with a several header records and undeneath many detail records I need to put in the header record the number of detail records above this header record and number of detail records below this header record Header... (5 Replies)
Discussion started by: klut
5 Replies

Featured Tech Videos