Friday 6 September 2013

Is there a way to loop through all fields in a MySQL trigger rather than repeating yourself?

Is there a way to loop through all fields in a MySQL trigger rather than
repeating yourself?

Easiest to explain via example. Here is my MySQL query to create the trigger:
DELIMITER $$
DROP TRIGGER IF EXISTS `tblExample_UPDATE`$$
CREATE TRIGGER `tblExample_UPDATE` AFTER UPDATE ON `tblExample`
FOR EACH ROW
BEGIN
IF (NEW.field1 != OLD.field1) THEN
INSERT INTO tblChanges
(object_type,object_id,change_type,field_name,field_before,field_after,log_time)
VALUES
("example",NEW.id,"edited","field1",OLD.field1,NEW.field1,NOW());
END IF;
IF (NEW.field2 != OLD.field2) THEN
INSERT INTO tblChanges
(object_type,object_id,change_type,field_name,field_before,field_after,log_time)
VALUES
("example",NEW.id,"edited","field2",OLD.field2,NEW.field2,NOW());
END IF;
IF (NEW.field3 != OLD.field3) THEN
INSERT INTO tblChanges
(object_type,object_id,change_type,field_name,field_before,field_after,log_time)
VALUES
("example",NEW.id,"edited","field3",OLD.field3,NEW.field3,NOW());
END IF;
.
..
...
..
.
IF (NEW.field1337 != OLD.field1337) THEN
INSERT INTO tblChanges
(object_type,object_id,change_type,field_name,field_before,field_after,log_time)
VALUES
("example",NEW.id,"edited","field1337",OLD.field1337,NEW.field1337,NOW());
END IF;
END$$
DELIMITER ;
So essentially I am checking field by field for changes. It certainly
works but is going to be a PITA to maintain as any schema changes will
cause issues.
Is there any way to loop through each field somehow rather than checking
each individually?

No comments:

Post a Comment