Ad

Generic Logging With Differences Using Triggers

- 1 answer

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.

Ad

Answer

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

Ad
source: stackoverflow.com
Ad