List:General Discussion« Previous MessageNext Message »
From:Gael Martinez Date:June 21 2012 10:06pm
Subject:Triggers and column names
View as plain text  
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

Thread
Triggers and column namesGael Martinez21 Jun
  • Re: Triggers and column nameshsv22 Jun
    • RE: Triggers and column namesRick James22 Jun
      • Re: Triggers and column namesGael Martinez23 Jun