Hello there I'm trying to figure out an elegant way with Mysql 5.5.25 to log changes via a before change trigger to a table including the column name of the field that changed... How can I dynamically enumerate the field names and populate the field log into the t1_log test table ... Would a cursor be the most efficient way ?
CREATE TABLE `t1` ( `a` varchar(12) DEFAULT NULL, `b` varchar(12) DEFAULT NULL, `c` varchar(12) DEFAULT NULL, `hostid` int(12) NOT NULL AUTO_INCREMENT, `date` datetime DEFAULT NULL, UNIQUE KEY `hostid_UNIQUE` (`hostid`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 CREATE TABLE `t1_log` ( `hostid` int(12) DEFAULT NULL, `field` varchar(12) DEFAULT NULL, `old_value` varchar(12) DEFAULT NULL, `new_value` varchar(12) DEFAULT NULL, `datechanged` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The result would be like: mysql> select * from t1_log; +--------+-------+-----------+-----------+---------------------+ | hostid | field | old_value | new_value | datechanged | +--------+-------+-----------+-----------+---------------------+ | 1 | a | 1 | 4 | 2012-06-21 16:30:25 | | 2 | a | 2 | 4 | 2012-06-21 16:35:40 | | 1 | a | 4 | 43 | 2012-06-21 16:35:59 | +--------+-------+-----------+-----------+---------------------+ 3 rows in set (0.00 sec) I'm getting that done today thru a large static trigger script and I would like something more dynamic... Regards Gael -- Gaël Martinez
