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 // 

Answer 2

I don't have enough time to finish this right now, but I think that using CONCAT() to prepare a statement and using the result of that for a conditional might enable you to do what you want. Something along these lines:

DECLARE num_rows INT DEFAULT 0;
DECLARE cols CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = 'table_name' ORDER BY ordinal_position;

OPEN cols; 
SELECT FOUND_ROWS() INTO num_rows; 
SET @i = 1;

cols_loop: LOOP

    IF @i > num_rows THEN
        CLOSE cols;
        LEAVE cols_loop;
    END IF;

    FETCH cols INTO col;

    SET @do_stuff = 0;
    SET @s = CONCAT('SELECT IF(NEW.', col, ' <> OLD.', col, ', 1, 0) INTO @do_stuff');

    PREPARE stmt1 FROM @s;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

    IF @do_stuff = 1 THEN
        SET @s2 = CONCAT('INSERT INTO change_logs(log_on, user_id, table_name, colum_name, old_data, new_data ) 
                          VALUES (UNIX_TIMESTAMP(NOW()), ''0'', ''test'', ''', col,''', OLD.', col, ', NEW.', col, ');');

        PREPARE stmt2 FROM @s2;
        EXECUTE stmt2;
        DEALLOCATE PREPARE stmt2;
    END IF;

    SET @i = @i + 1;  
END LOOP cols_loop;

CLOSE cols; 

Answer 3

Unfortunately you can't do that. You can get the column names by accessing INFORMATION_SCHEMA but it's not possible to access the OLD and NEW values from that column names. I think it make sense because unlike stored procedure you're creating a trigger for a specific table not for the database . Calling a Stored procedure inside the trigger will help you to reduce code up-to some extend.

DROP TRIGGER IF EXISTS t_before_update_test;
DELIMITER $$
CREATE TRIGGER t_before_update_test
BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
    IF OLD.col1 <> NEW.col1 THEN
        /*pseudo*/
        CALL SP_insert_log (  
        'test', 
        'colum_name',
        'old_value',
        ''old_value');
    ELSEIF OLD.col2 <> NEW.col2 THEN
      //call above sp with this column related data
    END IF;
END $$

Answer 4

yes, a cursor can be added within a trigger to loop through columns. here are a couple of links :

mysql, iterate through column names

https://dba.stackexchange.com/questions/22925/mysql-loop-over-cursor-results-ends-ahead-of-schedule

from experience, it might be easier to create a stored procedure that does the looping and inserts and call it from the trigger