Generic logging with differences using triggers

Question

I am trying to write a trigger that would simplify logging changes when a record is updated. I have a stored procedure that does an INSERT to a logging table called log_update and a trigger that does the job for 1 field:

DROP TRIGGER IF EXISTS `parents_update`;
DELIMITER $$
CREATE TRIGGER `parents_update` AFTER UPDATE ON `parents`
FOR EACH ROW
BEGIN
    IF ( NEW.motherLastName != OLD.motherLastName ) THEN
        CALL log_update( 'parent', NEW.id, 4, CONCAT( OLD.motherLastName, ' > ', NEW.motherLastName ) );
    END IF;
END;
$$
DELIMITER ;

Is it a way to make this more generic like provide list of fields:

( 'motherFirstName', 'motherLastName', 'fatherFistName', .... )

and loop through this list with a parameter for the single IF statement?

Edit:

I come up with such loop:

DROP TRIGGER IF EXISTS `parents_update`;
DELIMITER $$
CREATE TRIGGER `parents_update` AFTER UPDATE ON `parents`
FOR EACH ROW
BEGIN
    DECLARE _fieldName VARCHAR(25);
    DECLARE done INT DEFAULT FALSE;
    DECLARE fieldsCursor CURSOR FOR SELECT fieldName FROM log_fields WHERE tableName = 'parents';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN fieldsCursor;

    the_loop : LOOP
        FETCH fieldsCursor INTO _fieldName;
        IF done THEN
          LEAVE the_loop;
        END IF;

        -- _fieldName will contain values such 'motherLastName'
        IF ( NEW ( _fieldName ) != OLD ( _fieldName ) ) THEN
            CALL log_update( 'parent', NEW.id, 4, CONCAT( OLD ( _fieldName ), ' > ', NEW ( _fieldName ) ) );
        END IF;
    END LOOP the_loop;

    CLOSE fieldCursor;
END;
$$
DELIMITER ;

where table log_fields will contain fields to check. Now I am facing the problem of how to access the NEW. or OLD. property if the field name is in a variable.

Can you just send OLD and NEW values in two separate parameters? And let 'log_update' store procedure determine if it's same or different. If different, store. If same, do nothing. It will be like log_update(table, id, field, old, new) — Tin
– Tin, Commented Mar 2, 2016 at 19:23
I would consider writing a script that inserts the triggers with the fields hardcoded (for example repeating the if for each field configured in the script), it is way more efficient and you still have the possibility to change it quickly and centrally. After each change you would just rerun the script. — Daniele Bernardini
– Daniele Bernardini, Commented Mar 3, 2016 at 18:14

Community · Accepted Answer · 2017-05-23 10:34:01Z

1

+200

I would say you can use prepared statements to achieve it, but unfortunately it is not supported in triggers.

You can read more about it here: http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html OR answer here: Alternative to using Prepared Statement in Trigger with MySQL

which mean you can't create dynamic SQL query in your trigger the same applied to NEW. or OLD. variables dinamic build, so the only way is create separate triggers for each table with listed all column names one by one

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Feb 26, 2016 at 10:01

Armen

4,2123 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generic logging with differences using triggers

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related