Geek asks and answers

MySQL UPDATE trigger: INSERT column values ​​that are actually changed

I am working on a fairly simple ticket management system. I want to keep a journal for materials to be added, deleted and modified.

I created three triggers, AFTER INSERT, AFTER DELETEand AFTER UPDATE. Triggers INSERT/ DELETEare simple, this is the trigger UPDATEthat I'm having problems with.

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

The trigger that I am currently “working” is, except that it does not insert the actual values ​​that I would like.

How to get value from OLDand NEWusing a variable col_name?

I'm also not sure if this is the best way to do this ... So, if anyone has any ideas on this, they are also welcome ... This trigger started a lot easier ...

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
source share
2 answers

  • Since using prepared statements is not possible here, I would suggest you invoke some INSERT statements, for example. -

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

  • Or try copying all the fields you need into another table.

In these cases, you will avoid using the cursor.

+1
source

Try using 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;
0
source

More articles:


All Articles