1

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?

2
  • That is a rather unusual variant of a history table. Is thera a specific reason why a more traditional table wont do? – Lennart Jul 20 '20 at 22:59
  • 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? – pbarney Jul 21 '20 at 16:43
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.

3
  • 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. – pbarney Jul 21 '20 at 20:21
  • that will not work with only 1 changed value thiis will not work let it be with the @switch – nbk Jul 21 '20 at 20:28
  • 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! – pbarney Jul 21 '20 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.