Loop through columns in MySQL trigger

Question

Is it possible to loop through the all column names while inside a trigger?

Scenario: To log all the columns of a table that have been modified. If some values did not change, do not log those ones.

DROP TRIGGER IF EXISTS t_before_update_test;
DELIMITER $$
CREATE TRIGGER t_before_update_test
BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
    -- Loop here for all columns, not just col1
    IF OLD.col1 <> NEW.col1 THEN
        INSERT INTO change_logs(
            log_on, user_id,
            table_name, colum_name,
            old_data, new_data
        ) VALUES (
            UNIX_TIMESTAMP(NOW()), '0',
            'test', 'col1',
            OLD.col1, NEW.col1
        );
    END IF;
    -- process looping all columns
    -- col1, col2, ... should be dynamic per loop
END $$

This is working copy example, where I now need to loop through all columns available in OLD or NEW.

Answer 1

Unfortunately, using dynamic SQL (i.e PREPARED STATEMENT) in MySQL trigger is not allowed.(This can not be bypassed by calling a stored procedure which has dynamic SQL ). Therefore, we have to hardcode the column name in the trigger. However, if the columns are to change, the trigger will break due to the unmatchable columns, which simply stops the UPDATE trasaction. Therefore, we need to check if it's legit to do the logging job in the change_logs table. If legit, then insert into the change_logs table; else just send a warning message into a warning table. Supposing the test table has two columns namely id and datetm. And a warning table with 3 columns (table_name,log_time,log_content) is created beforehand. The change_logs table is identical to the OP's. The rest is creating the trigger (written and tested in workbench):

delimiter //
 
drop trigger if exists t_before_update_test//
create trigger t_before_update_test  before  update on test for each row begin
if 
    'id' not in (select column_name from information_schema.columns where table_name='test')
    or 'datetm' not in (select column_name from information_schema.columns where table_name='test')
    or (select count(column_name) from information_schema.columns where table_name='test') !=2
then
    insert into warning_table values ('test',now(),'Table column structure has been changed!!');
else 
    IF old.id <> new.id THEN
        INSERT INTO change_logs(
            log_on, user_id,
            `table_name`, colum_name,
            old_data, new_data
        ) VALUES (
            UNIX_TIMESTAMP(NOW()), '0',
            'test', 'id',
            old.id, new.id
        );
    END IF;

    IF old.datetm <> new.datetm THEN
        INSERT INTO change_logs(
            log_on, user_id,
            `table_name`, colum_name,
            old_data, new_data
        ) VALUES (
            UNIX_TIMESTAMP(NOW()), '0',
            'test', 'datetm',
            old.datetm, new.datetm
        );
    END IF;
end if;
end //