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?

nbk · Answer

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.

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

One Answer

Add your own answers!

Ask a Question