Update Zeitstempel setzen wenn ein Datensatz aktualisiert wird.
|
1 2 3 4 |
CREATE TRIGGER `update_trigger_for_table` BEFORE UPDATE ON `table_name` FOR EACH ROW SET NEW.`updated` = NOW(); |
Datenänderungen bestimmter Spalten aufzeichnen
Die Datenänderungen werden in folgender Tabelle geloggt.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE `data_tracking` ( `tracking_id` int(11) NOT NULL AUTO_INCREMENT, `table` varchar(50) NOT NULL, `data_id` int(11) NOT NULL, `field` varchar(50) NOT NULL, `old_value` text, `new_value` text, `modified` datetime NOT NULL, PRIMARY KEY (`tracking_id`), KEY `[data_id]` (`data_id`), KEY `[modified]` (`modified`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
Trigger der je Tabelle angelegt werden muss
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DELIMITER $$ CREATE TRIGGER `data_tracking_for_table_name` AFTER UPDATE ON `table_name` FOR EACH ROW BEGIN IF (NEW.`columnA` != OLD.`columnA`) THEN INSERT INTO data_tracking (`data_id` , `table`, `field` , `old_value` , `new_value` , `modified` ) VALUES (NEW.id, 'table_name', 'columnA', OLD.`columnA`, NEW.`columnA`, NOW()); END IF; IF (NEW.`columnB` != OLD.`columnB`) THEN INSERT INTO data_tracking (`data_id` , `table`, `field` , `old_value` , `new_value` , `modified` ) VALUES (NEW.id, 'table_name', 'columnB', OLD.`columnB`, NEW.`columnB`, NOW()); END IF; END$$ |