MySQL UPDATE trigger: INSERTing the values of the columns that actually changed

I'm working on a fairly simple ticket managment system. I want to keep a log for stuff that gets added, deleted, and changed.

I created three triggers, AFTER INSERT, AFTER DELETE, and AFTER UPDATE. The INSERT/DELETE triggers are straightforward, it's theUPDATE trigger I'm having problems with.

I would like to add which columns has changed in the table with their old & new values, i.e. colname changed from X to Y

The trigger I have now "works", except of course that it doesn't insert the actual values I'd like.

How do I get the value from OLD and NEW using the col_name variable?

I'm also not sure if this is the best possible way of doing this ... So if anyone has ideas on that, they're welcome too ... This trigger started out a lot simpler ...

BEGIN
    DECLARE num_rows, i int default 1;
    DECLARE col_name CHAR(255);
    DECLARE updated TEXT;

    DECLARE col_names CURSOR FOR
        SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'storing'
        ORDER BY ordinal_position;

    OPEN col_names;
    SELECT FOUND_ROWS() INTO num_rows;
    SET i = 1;
    SET @updated = 'Updated columns: ';

    the_loop: LOOP
        IF i > num_rows THEN
            LEAVE the_loop;
        END IF;

        FETCH col_names INTO col_name;

        /* So, how do I get the proper values? */
        /* IF NEW.@col_name != OLD.@col_name THEN */
        /*SET @updated = CONCAT(@updated, OLD.@col_name, ' changed into ', NEW.@col_name, ' ');*/
        SET @updated = CONCAT(@updated, 'OLD', ' changed into ', 'NEW', ' ');
        /* END IF;*/

        SET i = i + 1;
    END LOOP the_loop;

    CLOSE col_names;

    INSERT INTO `log` (`storing`, `medewerker`, `actie`, `开发者_运维知识库data`)
    VALUES (NEW.`id`, NEW.`medewerker`, "Storing aangepast", @updated);
END

---

1. Since usage of prepared statements here is impossible, I would suggest you to call some INSERT statements, e.g. -

   IF NEW.column1 <> OLD.column1 THEN INSERT INTO... END IF; IF NEW.column2 <> OLD.column2 THEN INSERT INTO... END IF; ...

2. Or try to copy all fields you need into another table.

In these cases you will avoid using cursor.

---

Try to use prepared statements Something like this:

SET @s = CONCAT('SELECT new.', @col_name, ', old.', @col_name, ' FROM ', /*here is the query details like inner joins etc.*/, ' where ', 'NEW.', @col_name, '!= OLD.', @col_name )
    PREPARE stmt FROM @s;
    EXECUTE stmt;