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?
Best 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.
That said, a GMP audit requieres also to have the old value saved.