How do you combine multiple update statements for the same row using MySQL trigger

Question

Background:

Each time a column is modified, I need to update the associated column (which has the same name) in a second table. This is my first attempt at using a trigger.

Code:

Here's a simplified example of what I'm trying to do, which does its job fine, but inefficiently:

DROP TRIGGER IF EXISTS update_second_table;
DELIMITER //
CREATE TRIGGER update_second_table
 BEFORE UPDATE ON first_table 
 FOR EACH ROW
BEGIN
 /* putting IF statements on one line so it's easier to see what's happening */
 IF NOT(OLD.firstname <=> NEW.firstname) THEN UPDATE second_table SET firstname = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.middlename <=> NEW.middlename) THEN UPDATE second_table SET middlename = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.lastname <=> NEW.lastname) THEN UPDATE second_table SET lastname = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.nickname <=> NEW.nickname) THEN UPDATE second_table SET nickname = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.dob <=> NEW.dob) THEN UPDATE second_table SET dob = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.email <=> NEW.email) THEN UPDATE second_table SET email = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.address <=> NEW.address) THEN UPDATE second_table SET address = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.city <=> NEW.city) THEN UPDATE second_table SET city = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.state <=> NEW.state) THEN UPDATE second_table SET state = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.zip <=> NEW.zip) THEN UPDATE second_table SET zip = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
 IF NOT(OLD.phone <=> NEW.phone) THEN UPDATE second_table SET phone = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
END;
//
DELIMITER;

The problem:

As you can see, depending on how many columns are updated in `first_table`, there can be as many as 11 update statements on the same row in `second_table`.

The question:

Is there any way to combine the update statements into one?

• Lennart - Slava Ukraini: That is a rather unusual variant of a history table. Is thera a specific reason why a more traditional table wont do?
• pbarney: It's not a history table, it's an "age of data" table. For every row, each column needs to be date stamped, and I'm trying one versions with timestamp columns in the original table and one with timestamp columns in a separate table to see which ends up being more practical. What are your thoughts?

Answer 1

You can use prepared statemnts for this purpose to have only one UPDATE command.

A danger from SQL injection isn't possible, because there is no user input.

DROP TRIGGER IF EXISTS update_second_table;
DELIMITER //
CREATE TRIGGER update_second_table
 BEFORE UPDATE ON first_table 
 FOR EACH ROW
BEGIN
 SET @switch = 0;
 SET @sql = 'UPDATE second_table SET ';
 IF NOT(OLD.firstname <=> NEW.firstname) THEN 
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'firstname = CURRENT_TIMESTAMP');
 END IF;
 IF NOT(OLD.middlename <=> NEW.middlename) THEN
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'middlename = CURRENT_TIMESTAMP');
 END IF;
 IF NOT(OLD.lastname <=> NEW.lastname) THEN 
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'lastname = CURRENT_TIMESTAMP');
 END IF;
 IF NOT(OLD.nickname <=> NEW.nickname) THEN 
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'nickname = CURRENT_TIMESTAMP');
 END IF;
 IF NOT(OLD.dob <=> NEW.dob) THEN 
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'dob = CURRENT_TIMESTAMP');
 END IF;
 IF NOT(OLD.email <=> NEW.email) THEN 
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'email = CURRENT_TIMESTAMP');
 END IF;
 IF NOT(OLD.address <=> NEW.address) THEN 
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'address = CURRENT_TIMESTAMP');
 END IF;
 IF NOT(OLD.city <=> NEW.city) THEN 
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'city = CURRENT_TIMESTAMP');
 END IF;
 IF NOT(OLD.state <=> NEW.state) THEN 
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'state = CURRENT_TIMESTAMP');
 END IF;
 IF NOT(OLD.zip <=> NEW.zip) THEN 
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'zip = CURRENT_TIMESTAMP');
 END IF;
 IF NOT(OLD.phone <=> NEW.phone) THEN 
 if @switch = 0 THEN
 SET @switch = 1;
 ELSE
 SET @sql = CONCAT(@sql,',');
 END IF;
 SET @sql = CONCAT(@sql, 'phone = CURRENT_TIMESTAMP');
 END IF;
 SET @sql = CONCAT ( @sql,' WHERE id = OLD.id;');
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;

That said, a GMP audit requieres also to have the old value saved.

• pbarney: Hey, thanks nbk! This is very helpful. I also found that I can clean up the code by removing all of the @switch statements by just adding a comma after each of the CURRENT_TIMESTAMPs and putting a SET @sql = TRIM("," FROM @sql); before the line where you CONCAT the WHERE statement.
• nbk: that will not work with only 1 changed value thiis will not work let it be with the @switch
• pbarney: But it is working. The TRIM statement removes the last comma. Whether there is one column or all columns, it's only the last comma that matters. Then I only needed to check to make sure that at least one field was updated (because if no field is changed, the prepared statement won't work.) I appreciate the help!