MySQL trigger. Is it possible to get the OLD and NEW values ​​dynamically?

Question

MySQL

The essence of the task - if something has changed in a row - write the old and new values ​​\u200b\u200bin another table (in order to keep a log of changes).

Is it possible to do something automated and not hardcode the column names?

Right now, this is the code:

CREATE TRIGGER rates_change
    AFTER UPDATE ON duty_rates
     FOR EACH ROW
     BEGIN
     DECLARE done INT default 0;
     DECLARE every_column_name varchar(4000);
     DECLARE cur1 CURSOR FOR select column_name from information_schema.columns where TABLE_SCHEMA='rates_database' and table_name='duty_rates';
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
     OPEN cur1;
     REPEAT
     FETCH cur1 INTO every_column_name;
     IF NOT done THEN
      -- тут иф с проверкой изменилось ли значение  для колонки, название которой every_column_name
  INSERT INTO log_rates_changes -- дальше автоматизировать бы
    END IF;
    UNTIL done END REPEAT;
    CLOSE cur1;
    END;

Those. I go through all the columns that are in the structure, but I can’t get access to OLD and NEW, and there is a suspicion that it won’t work.

Tried through "PREPARE stmt" - writes an error that it is impossible to execute dynamic SQL in the trigger. Even if you place it in a procedure and call it from a trigger, they say it will be the same.

Answer 1

I myself struggled with a similar logger, but, unfortunately, MySQL does not allow such liberties.

Answer 2

Maybe I didn't understand the essence of your problem, but why can't you use NEW and OLD aliases ?
I use the following trigger without problems in a particular case:

CREATE TRIGGER triggerName 
   AFTER INSERT ON table1 
   FOR EACH ROW 
      INSERT INTO table2 VALUES(NULL, NEW.col1, NEW.col2)

Answer 3

I would generate a static serial trigger code from the table schema in the application assembly scripts.