Änderungs-Logging, Eintragen beim Löschen mit Detail-Tabelle

[Sir Rufo]

Ich habe das mal für MySQL 5.6 (TIMESTAMP mit Nachkommastellen) zusammengebaut inkl. eines kleinen Tests. Durch die Verwendung des TIMESTAMP(6) kann man im Trigger prüfen, ob es tatsächlich eine Änderung am Datensatz gegeben hat.

Wird nämlich eine Zeile mit identischen Werten aktualisiert, dann wird die TIMESTAMP Spalte nicht aktualisiert, der Trigger wird trotzdem ausgelöst. Mit einem genauen TIMESTAMP kann nun im Trigger zuverlässig (genug ) auf eine Änderung geprüft werden.

Natürlich lösen die Detail-Löschung durch den Foreign-Key keine weiteren Log-Einträge aus -> siehe Trigger auf der Tabelle detail . Das grundlegende Prinzip ist aber für jedes Datenbanksystem gleich

SQL-Code:

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `master`
-- ----------------------------
DROP TABLE IF EXISTS `master`;
CREATE TABLE `master` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `data` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `detail`
-- ----------------------------
DROP TABLE IF EXISTS `detail`;
CREATE TABLE `detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `mastid` int(11) NOT NULL,
  `data` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mastid` (`mastid`),
  CONSTRAINT `setail_master` FOREIGN KEY (`mastid`) REFERENCES `master` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `log`
-- ----------------------------
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `tab` varchar(30) NOT NULL,
  `id` int(11) NOT NULL,
  `act` varchar(1) NOT NULL,
  `org` varchar(30) NOT NULL,
  `ocurred` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER //

-- ----------------------------
--  Triggers structure for table detail
-- ----------------------------

CREATE TRIGGER `detail_AI` AFTER INSERT ON `detail` FOR EACH ROW BEGIN
   INSERT INTO 
      log ( tab, id, act, org )
   SELECT
      'master', id, 'U', 'detail insert'
   FROM 
      `master`
   WHERE
      id = NEW.mastid;
END;//

CREATE TRIGGER `detail_AU` AFTER UPDATE ON `detail` FOR EACH ROW BEGIN
   -- Ist wirklich etwas geändert worden?
   IF OLD.updated <> NEW.updated THEN

      -- Änderung für die ALTE Master-ID eintragen
      INSERT INTO 
         log ( tab, id, act, org )
      SELECT
         'master', id, 'U', 'detail update'
      FROM 
         `master`
      WHERE
         id = OLD.mastid;

      -- Hat sich die Master-ID geändert?
      IF OLD.mastid <> NEW.mastid THEN
         -- Änderung für die NEUE Master-ID eintragen
         INSERT INTO 
            log ( tab, id, act, org )
         SELECT
            'master', id, 'U', 'detail update'
         FROM 
            `master`
         WHERE
            id = NEW.mastid;

      END IF;
   END IF;
END;//

CREATE TRIGGER `detail_AD` AFTER DELETE ON `detail` FOR EACH ROW BEGIN
   INSERT INTO 
      log ( tab, id, act, org )
   SELECT
      'master', id, 'U', 'detail delete'
   FROM `master`
   WHERE
      id = OLD.mastid;
END;//

-- ----------------------------
--  Triggers structure for table master
-- ----------------------------
CREATE TRIGGER `master_AI` AFTER INSERT ON `master` FOR EACH ROW BEGIN
   INSERT INTO 
      log ( tab, id, act, org )
   VALUES 
      ( 'master', NEW.id, 'I', 'master insert' );
END;//

CREATE TRIGGER `master_AU` AFTER UPDATE ON `master` FOR EACH ROW BEGIN
   IF NEW.updated <> OLD.updated THEN
      IF NEW.ID = OLD.id THEN
         INSERT INTO 
            log ( tab, id, act, org )
         VALUES 
            ( 'master', NEW.id, 'U', 'master update' );
      ELSE
         INSERT INTO 
            log ( tab, id, act, org )
         VALUES 
            ( 'master', OLD.id, 'D', 'master update' ), 
            ( 'master', NEW.id, 'I', 'master update' );
      END IF;
   END IF;
END;//

CREATE TRIGGER `master_AD` AFTER DELETE ON `master` FOR EACH ROW BEGIN
   INSERT INTO 
      log ( tab, id, act, org )
   VALUES 
      ( 'master', OLD.id, 'D', 'master delete' );
END;//

DELIMITER ;

SET FOREIGN_KEY_CHECKS = 1;

Hier ein kleiner Test ...

SQL-Code:

-- 
-- Test the structure
--

-- Create some master records

INSERT INTO 
   `master` ( `data` )
VALUES 
   ( 'master 1' ), 
   ( 'master' ), 
   ( 'master 3' );
   
-- 3 log entries

-- Create some details

INSERT INTO 
   `detail` ( `mastid`, `data` )
VALUES 
   ( 1, 'detail' ), 
   ( 2, 'detail 2' ), 
   ( 2, 'detail' ), 
   ( 3, 'detail' );
   
-- 4 log entries

-- Do some fake and real updates on master

UPDATE `master` SET `data` = 'master 1' WHERE id = 1; -- no real update
UPDATE `master` SET `data` = 'master 2' WHERE id = 2; -- real update
UPDATE `master` SET `data` = 'master 3' WHERE id = 3; -- no real update

-- 1 log entry ( for touching 3 records )

-- Do some fake and real updates on detail

UPDATE `detail` SET `data` = 'detail 1' WHERE mastid = 1; -- 1 real update
UPDATE `detail` SET `data` = 'detail 2' WHERE mastid = 2; -- 1 real update, 1 fake update

-- 2 log entries (for touching 3 records )

-- switch the master of detail

UPDATE `detail` SET `mastid` = 3 WHERE mastid = 1;

-- 2 log entries ( for master 1 and master 3 )

-- delete a detail record

DELETE FROM `detail` WHERE `data`='detail';

-- 1 log entry

-- delete a master with details

DELETE FROM `master` WHERE id = 2;

-- 1 log entry

... der dann folgendes LOG produziert

tab	id	act	org	ocurred
master	1	I	master insert	2014-10-01 18:52:36.184279
master	2	I	master insert	2014-10-01 18:52:36.184279
master	3	I	master insert	2014-10-01 18:52:36.184279
master	1	U	detail insert	2014-10-01 18:52:36.185614
master	2	U	detail insert	2014-10-01 18:52:36.185614
master	2	U	detail insert	2014-10-01 18:52:36.185614
master	3	U	detail insert	2014-10-01 18:52:36.185614
master	2	U	master update	2014-10-01 18:52:36.186496
master	1	U	detail update	2014-10-01 18:52:36.187054
master	2	U	detail update	2014-10-01 18:52:36.187564
master	1	U	detail update	2014-10-01 18:52:36.188104
master	3	U	detail update	2014-10-01 18:52:36.188104
master	3	U	detail delete	2014-10-01 18:52:36.188513
master	2	D	master delete	2014-10-01 18:52:36.188889