1

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
Martin Tournoij
  • 26,737
  • 24
  • 105
  • 146

2 Answers2

1

  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.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Thanks, this works and I implemented it (The first) as a temporary solution, but to be honest it's not really very pretty ... I'd much rather loop over the column names... – Martin Tournoij Aug 04 '11 at 11:25
  • So I went with this in the end. It might not look particularly pretty, but I guess columns won't change that much and it's a hell of a lot simpler (== less to go wrong, less to understand for future maintainers, etc.). – Martin Tournoij Aug 16 '11 at 14:14
0

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;
Alexander Verbitsky
  • 510
  • 5
  • 8
  • 2
    From the reference - SQL syntax for prepared statements can be used within stored procedures, but not in stored functions or triggers. http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html – Devart Aug 03 '11 at 14:38
  • Yes, you're right... Idea: we can copy OLD and NEW contents to some temporary table(s) and call a stored procedure that will create and execute prepared statement. But it would be very expensive. – Alexander Verbitsky Aug 03 '11 at 14:51