Trigger - Changelog über mehrere Tabellen

warface · 13. September 2017

Hallo Zusammen,

ich würde gerne einen Trigger erstellen, der Spalten über mehrere Tabellen überwacht und bei Änderung, Erstellung oder Löschung diese in eine Changelog-Tabelle schreibt.

Der Grund hierfür ist, dass die Daten von z.B. Artikeln auf bis zu 15 Tabellen verteilt sind.
Ich würde gern die Änderung von Kritischen Spalten überwachen.

Die Changelog-Tabelle sollte dann so in etwa aussehen.

Artikelnummer | Änderungszeitpunkt | Bezeichnung_alt | Bezeichnung_neu | Preis_alt | Preis_neu

dabei befindet sich die Spalte Bezeichnung in einer anderen Tabelle als die Spalte Preis.

ist sowas möglich, wenn ja könnt ihr mir eine mal schreiben wie ungefähr ich das aufbauen müsste.
Danke schon mal im Voraus für die Unterstützung

Biber3 · 13. September 2017

Moin warface,

warface hat gesagt.:
Artikelnummer | Änderungszeitpunkt | Bezeichnung_alt | Bezeichnung_neu | Preis_alt | Preis_neu

dabei befindet sich die Spalte Bezeichnung in einer anderen Tabelle als die Spalte Preis.

Was soll denn dann der Primary Key der Changelog-Tabelle sein?

Wenn es stimmt, das die Bezeichnung des Artikels 4711 in einer anderen Tabelle geändert werden kann als der Preis des Artikels 4711,
dann kann der Trigger für die Bezeichnungs-Änderung doch nur die Felder "Artikelnummer", "Änderungszeitpunkt", "Bezeichnung_alt", "Bezeichnung_neu" schreiben. Die Preis_alt/neu-Felder kann er gar nicht kennen. Vice versa der Trigger auf der Artikel-Preistabelle.

Und was bedeutet: "die Daten von z.B. Artikeln auf bis zu 15 Tabellen verteilt sind"??

Kann ein Artikel in einer Tabelle Artikel001, Artikel002, Artikel00x stehen oder wie können wir uns das vorstellen?

Grüße
Biber

P.S. Du hast ja schon ein paar Fragen zum Thema Trigger hier gestellt.
Du versuchst aber nicht etwa, Designfehler im Datenmodell durch Trigger auszubügeln, oder?

warface · 14. September 2017

@Biber3
der Primary Key wäre die Artikelnummer und Artikelvariante diese werden nicht geändert, es sollen aber diverse Spalten die zu dieser Artikelnummer und Artikelvariante gehören bei Änderung protokolliert werden.
Da die Artikelverwaltung ca. 200-300 Informationen beinhaltet, die u.a. eine 1 zu n Verknüpfung haben (z.B. Bezeichnung in mehreren Sprachen) sind die Daten auf mehrere Tabellen aufgeteilt.

Diese Anfrage hat nichts mit der vorherigen Anfrage zu tun ;-)
Ich möchte mit diesem Trigger prüfen, wie oft welche Informationen geändert werden, um die Berechtigungen für das Ändern der Artikeldaten anzupassen.

ich bräuchte nur ein kleines Beispiel, wie man das aufbauen könnte, denn Rest sollte ich selbst hinkriegen

Biber3 · 14. September 2017

Na gut,

als kleines Beispiel würde ich es so skizzieren.

SQL:

create or replace trigger DeinSchema.ArtikelBez_TR_aue
   after UPDATE of BEZEICHNUNG
      on DeinSchema.ArtikelBez_TR
      for each row
      -- enable/disable
begin
   INSERT INTO DeinSchema.Changelog( ArtikelNummer, Artikelvariante, ChangeTs, ChangedField, InTable, NewValue, Oldvalue)
   VALUES ( :new.ArtikelNummer, new.Artikelvariante, systimestamp, "BEZEICHNUNG", "ArtikelBez_TR", :new.bezeichnung, :old.bezeichnung)
end ArtikelBez_TR_aue;

Das wäre jetzt zB. ein Trigger auf der Tabelle "ArtikelBez_TR", in der die türkischen Übersetzungen für Artikel/Varianten gepflegt werden.

Trigger würde bei mir den Suffix "_aue" bekommen (AFTER UPDATE, for Each row).
Reagiert nur auf Update (nicht Insert oder Delete) und nur auf Feld-Änderungen von Feld BEZEICHNUNG.

Die Changelog-Tabelle hätte bei mir eine etwas andere Struktur als deine, nämlich
- PK: ArtikelNummer, ArtikelVariante, ChangeTs (=Änderungszeitpunkt), InTable(=in welcher Tabelle würde geändert)
- Nutzdaten ChangedField =(welches Feld wurde geändert) , newValue/oldValue(= zwei Varchar2(100)-Felder)

In oldvalue/newValue würde ich jeweils als Text neuen/alten Wert schreiben, also meinetwegen auch als String neuen/alten Preis, wenn mal ein Trigger diese Preisänderung tracken soll.

Jedenfalls würdest du mit deinem PK "Artikelnummer und Artikelvariante" nicht weit kommen.
Denn es kann ja sein, dass Bezeichnung einer Artkelnummer+Variante zufällig mal um Punkt 14:23h am selben Tag in der türkischen und in der kasachischen Artikeltabelle von unterschiedlichen Anwendern geändert werden.

Ich würde jedenfalls jeweils einen Trigger je Changelog-fütternder Tabelle anlegen.
Einen globalen Datenbanktrigger, der ständig im Hintergrund auf jede Datenbank-Aktivität lauscht- das würde ich meiden.

Grüße
Biber

warface · 14. September 2017

@Biber3 vielen Dank für deinen Tipp
ich werde mal schauen wie ich das umsetzen kann.
und werde das Ergebnis, dann mal posten

warface · 19. September 2017

@Biber3 nochmals viele Dank für deinen Tipp
ich hab das jetzt wie folgt umgesetzt.
Werde wohl für jede Tabelle einen Trigger machen.

SQL:

create or replace TRIGGER demodb.customer_teile_logger
AFTER UPDATE OF bild, ean, kzes, ts, zeichnr
ON demodb.g040
FOR EACH ROW

BEGIN

------------------------
--------- Bild ----------
------------------------
    IF (:new.bild <> :old.bild) THEN
        INSERT INTO demodb.customer_teile_log (
            "Changenr",
            fi_nr,
            identnr,
            var,
            "ChangeTime",
            "ChangedField",
            "ChangedTable",
            "NewValue",
            "OldValue",
            "ChangedBy")
        VALUES (
            (select max("Changenr")+1 from demodb.customer_teile_log),
            :new.fi_nr,
            :new.identnr,
            :new.var,
            systimestamp,
            'bild',
            'g040',
            :new.bild,
            :old.bild,
            :new.useraen);
    END IF;
   
------------------------
--------- EAN ----------
------------------------
    IF (:new.ean <> :old.ean) THEN
        INSERT INTO demodb.customer_teile_log (
            "Changenr",
            fi_nr,
            identnr,
            var,
            "ChangeTime",
            "ChangedField",
            "ChangedTable",
            "NewValue",
            "OldValue",
            "ChangedBy")
        VALUES (
            (select max("Changenr")+1 from demodb.customer_teile_log),
            :new.fi_nr,
            :new.identnr,
            :new.var,
            systimestamp,
            'ean',
            'g040',
            :new.ean,
            :old.ean,
            :new.useraen);
    END IF;

------------------------
----- Status(kzes)------
------------------------
    IF (:new.kzes <> :old.kzes) THEN
        INSERT INTO demodb.customer_teile_log (
            "Changenr",
            fi_nr,
            identnr,
            var,
            "ChangeTime",
            "ChangedField",
            "ChangedTable",
            "NewValue",
            "OldValue",
            "ChangedBy")
        VALUES (
            (select max("Changenr")+1 from demodb.customer_teile_log),
            :new.fi_nr,
            :new.identnr,
            :new.var,
            systimestamp,
            'kzes',
            'g040',
            :new.kzes,
            :old.kzes,
            :new.useraen);
    END IF;

------------------------
--- Teilestatus (TS) ---
------------------------
    IF (:new.ts <> :old.ts) THEN
        INSERT INTO demodb.customer_teile_log (
            "Changenr",
            fi_nr,
            identnr,
            var,
            "ChangeTime",
            "ChangedField",
            "ChangedTable",
            "NewValue",
            "OldValue",
            "ChangedBy")
        VALUES (
            (select max("Changenr")+1 from demodb.customer_teile_log),
            :new.fi_nr,
            :new.identnr,
            :new.var,
            systimestamp,
            'ts',
            'g040',
            :new.ts,
            :old.ts,
            :new.useraen);
    END IF;

------------------------
------- ZEICHNR---------
------------------------
    IF (:new.zeichnr <> :old.zeichnr) THEN
        INSERT INTO demodb.customer_teile_log (
            "Changenr",
            fi_nr,
            identnr,
            var,
            "ChangeTime",
            "ChangedField",
            "ChangedTable",
            "NewValue",
            "OldValue",
            "ChangedBy")
        VALUES (
            (select max("Changenr")+1 from demodb.customer_teile_log),
            :new.fi_nr,
            :new.identnr,
            :new.var,
            systimestamp,
            'zeichnr',
            'g040',
            :new.zeichnr,
            :old.zeichnr,
            :new.useraen);
    END IF;
END;

Biber3 · 19. September 2017

Moin warface,

erstmal ein großes Danke für die Rückmeldung und die Lösung.
Ist wirklich in diesem Forum nicht selbstverständlich und hat mich gefreut.

Ein paar Sachen würde ich (geringfügig) anders machen, zwei davon kannst du ja mal prüfen.
a) für das Speichern des Änderungs-Zeitpunkts würde ich noch eine Variable spendieren.
Also vor dem BEGIN noch zwei Zeilen
Declare
chgTs Timestamp;
BEGIN
chgTS := systimestamp;
...
Und dann DIESEN Wert in das Feld "ChangeTime" schreiben.
Dann haben auch alle Timestamps für die geänderten Felder eines Datensatzes den gleichen Zeitstempel.
Ist etwas näher an der Wahrheit als die heutige Mimik.

b) Ich würde ganz/vollkommen/ersatzlos auf dieses Feld "ChangeNr" in der Tabelle customer_teile_log verzichten.
Du HAST einen eindeutigen PK (Zu welchem fi_nr, ident_nr, var wurde welches Attribut in welcher Tabelle wann geändert)
Klingt lang, aber was soll's , du musst es nie eintippen und - du wirst niemals nich' nach irgendeiner "ChangeNr" suchen oder sortieren oder über dieses Feld einen JOIN machen.
Du brauchst es nicht.
Andersrum: Jede spätere Auswertung der changeLog-Tabelle wird Fragestellungen haben, die sich immer an den genannte -zig PK-Schlüsseln langhangeln.
("Gib mir alle Änderungen von Artikel ..<bla> üner alle Varianten, wo Attribut "bild" geändert wurde in den letzten 3 Monaten").

Also hau diese ChangeNr weg.

Grüße
Biber

warface · 19. September 2017

@Biber3 danke für deine Verbesserungsvorschläge

Ich habe die ChangeNr eigentlich nur aus einem Grund eingebaut.
Im ChangeTime hab ich nur das Datum ohne Uhrzeit und es kann vorkommen, dass jemand den Artikel z.B. "Test" dann "Test1 und dann wieder "Test" nennt und das an einem Tag.
Das würde bedeuten das ein Datensatz doppelt ist.

ich hab auch schon überlegt ein Feld nur mit der Uhrzeit einzubauen, habs aber nicht hingekriegt :-D
Wenn ich ein Feld mit der Uhrzeit hätte, dann wäre auch dein Vorschlag mit der Timestamp-Variable sinnvoll.

Biber3 · 19. September 2017

Moin Warface,

na, das Feld ChangeTime als Timestamp zu definieren bzw. es darauf zu ändern, das sollte aber ein lösbares Problem sin.
Und ja, ich gebe dir recht, bei Datum ohne Uhrzeit sind Kollisionen vorprogrammiert.
Bitte bitte nicht den Krams mit 2 Feldern abfackeln (eines für Datum und eines für Uhrzeit).
Es gibt Timestamp-Felder. Auch in deiner Datenbank. Weiss ich genau.

Grüße
Biber

warface · 20. September 2017

@Biber3 danke für deine Untersützung

ich hab das Feld nun auf Timestamp umgestellt.

Ich werde den Thread nun schließen.

Hab noch eine etwas kompliziertere Anfrage vielleicht kannst du oder jemand mir einen Tipp für die Herangehensweise geben

Trigger - Changelog über mehrere Tabellen

warface

Mitglied

Biber3

Erfahrenes Mitglied

warface

Mitglied

Biber3

Erfahrenes Mitglied

warface

Mitglied

warface

Mitglied

Biber3

Erfahrenes Mitglied

warface

Mitglied

Biber3

Erfahrenes Mitglied

warface

Mitglied

Neue Beiträge