MYSQL update Trigger check changes in all columns and insert values to other table

Question

I've come to this code, but from this i have to manually insert all columns and check it by each column

CREATE TRIGGER table_a_update BEFORE UPDATE ON table_a
FOR EACH ROW     
BEGIN
   IF (NEW.column1 <=> OLD.column1) 
   THEN
       INSERT INTO table_updates(table, object_id, user_id, column, old_value, new_value)
       VALUES ("table_a",NEW.id, NEW.user_id, "column1", OLD.column1, new.column1)
   END IF;
   IF (NEW.column2 <=> OLD.column2) 
   THEN
       INSERT INTO table_updates(table, object_id, user_id, column, old_value, new_value)
       VALUES ("table_a",NEW.id, NEW.user_id, "column2", OLD.column2, new.column2)
   END IF;
   (other columns... ifs)
END;

I want to know how could i get all the columns from NEW that have differences to OLD columns, and then insert it, by something like this example using a php to what i dont know:

BEGIN
    for(columns(NEW) as $column){
        IF (NEW.$column <=> OLD.$column)
        THEN
            INSERT INTO table_updates(table, object_id, user_id,column, old_value, new_value)
            VALUES ("table_a", NEW.id, NEW.user_id, $column, $OLD[$column], $NEW[$column])
        END IF;
    }
END;

[system versioned tables](https://mariadb.com/kb/en/library/system-versioned-tables/) is a less manual way of having a historical record. — danblack, Mar 25 '19 at 00:51
Possible duplicate of [Best practice for a mysql data versioning system](https://stackoverflow.com/questions/17925435/best-practice-for-a-mysql-data-versioning-system) — Raymond Nijland, Mar 25 '19 at 00:58

score 2 · Answer 1 · answered Mar 25 '19 at 23:36

IIUC:

DELIMITER $$
CREATE TRIGGER before_update_table_a
BEFORE UPDATE ON table_a 
FOR EACH ROW BEGIN

    IF (NEW.col1 <> OLD.col1) THEN
        INSERT INTO table_updates (col1, col2, ... 
        VALUES (OLD.col1, NEW.col2 ...
    END IF;

    IF (NEW.col2 <> OLD.col2) THEN
        INSERT INTO table_updates (col1, col2, ... 
        VALUES (NEW.col1, OLD.col2 ...
    END IF;

    ... 

END $$
DELIMITER ;

I believe you need to use the DELIMITER keyword in this trigger, if you want to read more about this, check out this question.

Yes, thanks, but i want to change the code to be more like the second code, not the first. The first is what i have, i just removed the delimiters, but i have it. — Rodrigo Butzke, Mar 28 '19 at 00:45

MYSQL update Trigger check changes in all columns and insert values to other table

1 Answers1

Linked