MySQL Trigger compare all columns in OLD and NEW

Question

I am setting up a products database in PHPMyAdmin to use and maintain data directly from PHPMyAdmin, where I will be using csv export and import to update rows, I am having difficulty setting a trigger before update that will set CURRENT_USER() to a column modified_by, which will set the username of the MySQL user who updated the row. consider the following table products_data having columns id, name, model, modified_by

CREATE TABLE `products_master`.`products_data` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `name` VARCHAR(50) NOT NULL , 
    `model` VARCHAR(50) NOT NULL , 
    `modified_by` VARCHAR(50) NOT NULL , 
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

I have set the following trigger to set username in modified_by before update

CREATE TRIGGER `products_data_before_update` 
BEFORE UPDATE ON `products_data` 
FOR EACH ROW 
    SET NEW.modified_by = CURRENT_USER();

Now when I update rows using the PHPMyAdmin it will only query when there is a change in the data, but when I use csv files to update rows, enabling the option, Update data when duplicate keys found on import (add ON DUPLICATE KEY UPDATE), In this case update query is executed for all the rows irrespective of which rows have changed, In this case I am not getting the desired functionality of modified_by as the username is updated even when no rows are affected.

Hence I have changed my trigger to compare each column in NEW and OLD before updating username like so

CREATE TRIGGER `products_data_before_update` 
BEFORE UPDATE ON `products_data` 
    FOR EACH ROW 
        IF     OLD.id <> NEW.id 
            OR OLD.name <> NEW.name 
            OR OLD.model <> NEW.model 
            OR OLD.modified_by <> NEW.modified_by 
        THEN 
            SET NEW.modified_by = CURRENT_USER(); 
        END IF

where I am comparing each columns in OLD and NEW to check whether the row is actually changed

I want to know if there is a better solution to my problem, or maybe a direct way to compare all columns in OLD and NEW so that I can use the same Trigger in other tables without adding conditions for each column

@RiggsFolly No id wont change in case of uploading csv, but it could change if you execute a query manually — Aun Rizvi, Jan 22 '21 at 17:31

score 0 · Answer 1 · answered Jan 22 '21 at 17:51

I want to know if there is a better solution to my problem

CREATE TRIGGER `products_data_before_update` 
BEFORE UPDATE 
ON `products_data` 
FOR EACH ROW 
SET NEW.modified_by = CASE WHEN    (OLD.id, OLD.name, OLD.model, OLD.modified_by) 
                                != (NEW.id, NEW.name, NEW.model, NEW.modified_by)
                           THEN CURRENT_USER()
                           ELSE NEW.modified_by
                           END;

maybe a direct way to compare all rows in OLD and NEW so that I can use the same Trigger in other tables without adding conditions for each column

Maybe columns, not rows?

No, this is impossible. OLD and NEW are NOT tables.

instead of comparing all columns at once, wont it be faster to compare single columns and stop comparing when difference found, using IF...OR...OR like in my question — Aun Rizvi, Jan 25 '21 at 08:21

MySQL Trigger compare all columns in OLD and NEW

1 Answers1

Linked