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

[raller09]

Hi,

Ich möchte Änderungen an einer Tabelle in einer Interbase-DB über Trigger in einer Export-Tabelle erfassen um so eine Liste von zu exportierenden Datensätzen zu erstellen.


Ich habe folgende Tabellen:

Code:

Export (
  ID,
  SCHLUESSELFELDID,
  AENDERUNGSART        VARCHAR(1),
  AENDERUNGSDATUM
);

Kopf (
  KOPF_ID
  Daten...
);
PK: KOPF_ID

und folgende Trigger:

Code:

Kopf_AI_EXPORT for Kopf active after insert;
Kopf_AU_EXPORT for Kopf active after update;
Kopf_AD_EXPORT for Kopf active after delete;

die jeweils einen Eintrag in die "Export"-Tabelle entsprechend der Änderungs-Art "I", "U", "D" vornehmen (In Echt noch etwas genauer, so dass nur Änderungen an bestimmten Feldern einen Eintrag auslösen).

Das funktioniert für mich sehr gut. Ich habe so eine Tabelle, in der alle Änderungen (das es eine Änderung gab, nicht welche) protokolliert werden.
Diese Tabelle wird abgearbeitet und daraus ein Export mittels einem weiteren Programm ausgelöst.


Jetzt muss ich zusätzlich Änderungen an einer vom Kopf abhängigen Positions-Tabelle mit erfassen.

Hierzu habe ich die Positions-Tabelle, einen "Foreigen Key" auf den Kopf und entsprechende Trigger angelegt:

Code:

Detail (
  DETAIL_ID,
  KOPF_ID,
  Daten...
);
PK: DETAIL_ID
FK: KOPF_ID -> Kopf.KOPF_ID on update cascade on delete cascade;

Detail_AI_EXPORT for Detail active after insert;
Detail_AU_EXPORT for Detail active after update;
Detail_AD_EXPORT for Detail active after delete;

Diese drei Trigger schreiben jetzt in die Export-Tabelle immer einen Eintrag mit der Änderungs-Art "U" für Update.
Jedes Hinzufügen, Änderung an einem Positions-Datensatz oder Löschen einer Position ist ein "Update" des Kopfes.
Das funktioniert bis auf einen Fall für mich korrekt:

Wenn der Kopf-Datensatz gelöscht wird, dann löscht der "Foreign Key" der Detail-Tabelle die entsprechenden Datensätze.
Die dadurch gelöschten Positionen schreiben einen "U"-Eintrag nach dem "D"-Eintrag des "Kopf_AD_EXPORT".
Diese Datensätze können vom Export-Programm nicht Exportiert werden, da sie nicht mehr in der Kopf-Tabelle vorhanden sind.

Auch eine Umstellung des "Detail_AD_EXPORT" auf "before delete" führt zum selben Ergebnis.

Anscheinend ist hier die Reihenfolge bei Interbase folgende:

Kopf.Delete ->
Kopf.BeforeDelete
Kopf.Delete
Kopf.AfterDelete
Durch "Foreign Key" ausgelöst:

Detail.BeforeDelete
Detail.Delete
Dateil.AfterDelete

Ich kann jetzt den "Foreigen Key" auf

on update cascade on delete no action;

ändern und einen "Kopf.BeforeDelete"-Trigger erstellen, der die abhängigen Datensätze in der Detail-Tabelle löscht.
Dadurch würde die Reihenfolge passen. Das will ich aber eigentlich vermeiden, da der "cascade"-Aufruf so simple und gut funktioniert.


Gibt es hierfür bessere Lösungen oder ein besseres Konzept?

[Blup]

Protokollieren kannst du natürlich auch im BeforeDelete.
Sollte das Delete aus irgendeinem Grund fehlschlagen, wird auch dein Protokolleintrag wieder entfernt.
Das System wird automatisch auf einen internen Savepoint zurückgesetzt, der vor dem Delete gesetzt wird.

Du kannst also im BEFOREDELETE der Haupttabelle die Detaildatensätze einfach löschen und so die Detailtrigger auslösen.

[raller09]

Hi,

das hilft mir aber nicht weiter oder ist das was ich eigentlich nicht will oder?


es gibt 2 "Verschiedene" Löschungen aus der Detail-Tabelle:

• Es wird eine oder mehrere Positionen gelöscht.

Dann benötige ich einen "U"-Eintrag in der Export-Tabelle

• Der Master-DS wird gelöscht und durch den Foreign Key-Constraint werden auch die Positionen gelöscht.

Hier darf das "U" der Positionen nicht nach dem "D" der Master-Tabelle in der Log-Tabelle eingetragen werden.


Kann ich das ohne Umstellung des "on delete cascade;" Constraint erreichen?

[Dejan Vu]

Frag doch im Trigger der Details bei einer Löschung, ob es den Eltern/Kopfeintrag noch gibt...

[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

[raller09]

Hi,

Die Umsetzung von

Zitat von Dejan Vu:

Frag doch im Trigger der Details bei einer Löschung, ob es den Eltern/Kopfeintrag noch gibt...

entsprechend

Zitat von Sir Rufo:

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

war das, was mir als Idee gefehlt hat:

Select auf die Master-Tabelle

Danke.
Besonders Sir Rufo für den kompletten Aufbau.

Meine Struktur sah ähnlich aus. Ich habe bei mir nur die "Update ohne Änderung"-Prüfung und das Ändern des PKey weg gelassen.
Der insert in die Log-Tabelle wurde nur direkt gemacht...