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.

Afghanistan

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

Schneider

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; 

Boarding

Prepared statements, unfortunately, cannot be used inside triggers. dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html – Bahuvrihi

@AldenW. is there any way to access OLD values using the col variable?? – Winnipegosis

@Winnipegosis In a trigger you can access the old values using OLD.col_name. The documentation for that is here, dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html – Bahuvrihi

@AldenW. I kwon that, the question is if we can do something like OLD["col_name"], I think that is not possible in MYSQL. – Winnipegosis

I wonder can the trigger be something generated by the Event Scheduler ? Perhaps have a timed event that checks this every 15 minutes or how ever often, schedule an event to trigger this for a form of automation. Increase the timer for how often you think changes might happen. – Midis

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

Butterandeggs

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

Mohawk

Link only answers are frowned upon on Stack Overflow. It would be better to include the pertinent points in this post in case the links become invalid. – Detection

It also isn't clear how to use the solution in those links inside a trigger (where you need to access OLD and NEW – Trill