1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen

Trigger - Changelog über mehrere Tabellen

Dieses Thema im Forum "Relationale Datenbanksysteme" wurde erstellt von warface, 13. September 2017.

  1. warface

    warface Mitglied

    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 :)
     
  2. Biber3

    Biber3 Erfahrenes Mitglied

    Moin warface,

    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 gefällt das.
  3. warface

    warface Mitglied

    @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 :)
     
  4. Biber3

    Biber3 Erfahrenes Mitglied

    Na gut,

    als kleines Beispiel würde ich es so skizzieren.

    Code (SQL):
    1. CREATE OR REPLACE TRIGGER DeinSchema.ArtikelBez_TR_aue
    2.    after UPDATE OF BEZEICHNUNG
    3.       ON DeinSchema.ArtikelBez_TR
    4.       FOR each ROW
    5.       -- enable/disable
    6. BEGIN
    7.    INSERT INTO DeinSchema.Changelog( ArtikelNummer, Artikelvariante, ChangeTs, ChangedField, InTable, NewValue, Oldvalue)
    8.    VALUES ( :NEW.ArtikelNummer, NEW.Artikelvariante, systimestamp, "BEZEICHNUNG", "ArtikelBez_TR", :NEW.bezeichnung, :OLD.bezeichnung)
    9. 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 gefällt das.
  5. warface

    warface Mitglied

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

    warface Mitglied

    @Biber3 nochmals viele Dank für deinen Tipp
    ich hab das jetzt wie folgt umgesetzt.
    Werde wohl für jede Tabelle einen Trigger machen.
    Code (SQL):
    1. CREATE OR REPLACE TRIGGER demodb.customer_teile_logger
    2. AFTER UPDATE OF bild, ean, kzes, ts, zeichnr
    3. ON demodb.g040
    4. FOR EACH ROW
    5.  
    6. BEGIN
    7.  
    8. ------------------------
    9. --------- Bild ----------
    10. ------------------------
    11.     IF (:NEW.bild <> :OLD.bild) THEN
    12.         INSERT INTO demodb.customer_teile_log (
    13.             "Changenr",
    14.             fi_nr,
    15.             identnr,
    16.             var,
    17.             "ChangeTime",
    18.             "ChangedField",
    19.             "ChangedTable",
    20.             "NewValue",
    21.             "OldValue",
    22.             "ChangedBy")
    23.         VALUES (
    24.             (SELECT MAX("Changenr")+1 FROM demodb.customer_teile_log),
    25.             :NEW.fi_nr,
    26.             :NEW.identnr,
    27.             :NEW.var,
    28.             systimestamp,
    29.             'bild',
    30.             'g040',
    31.             :NEW.bild,
    32.             :OLD.bild,
    33.             :NEW.useraen);
    34.     END IF;
    35.    
    36. ------------------------
    37. --------- EAN ----------
    38. ------------------------
    39.     IF (:NEW.ean <> :OLD.ean) THEN
    40.         INSERT INTO demodb.customer_teile_log (
    41.             "Changenr",
    42.             fi_nr,
    43.             identnr,
    44.             var,
    45.             "ChangeTime",
    46.             "ChangedField",
    47.             "ChangedTable",
    48.             "NewValue",
    49.             "OldValue",
    50.             "ChangedBy")
    51.         VALUES (
    52.             (SELECT MAX("Changenr")+1 FROM demodb.customer_teile_log),
    53.             :NEW.fi_nr,
    54.             :NEW.identnr,
    55.             :NEW.var,
    56.             systimestamp,
    57.             'ean',
    58.             'g040',
    59.             :NEW.ean,
    60.             :OLD.ean,
    61.             :NEW.useraen);
    62.     END IF;
    63.  
    64. ------------------------
    65. ----- Status(kzes)------
    66. ------------------------
    67.     IF (:NEW.kzes <> :OLD.kzes) THEN
    68.         INSERT INTO demodb.customer_teile_log (
    69.             "Changenr",
    70.             fi_nr,
    71.             identnr,
    72.             var,
    73.             "ChangeTime",
    74.             "ChangedField",
    75.             "ChangedTable",
    76.             "NewValue",
    77.             "OldValue",
    78.             "ChangedBy")
    79.         VALUES (
    80.             (SELECT MAX("Changenr")+1 FROM demodb.customer_teile_log),
    81.             :NEW.fi_nr,
    82.             :NEW.identnr,
    83.             :NEW.var,
    84.             systimestamp,
    85.             'kzes',
    86.             'g040',
    87.             :NEW.kzes,
    88.             :OLD.kzes,
    89.             :NEW.useraen);
    90.     END IF;
    91.  
    92. ------------------------
    93. --- Teilestatus (TS) ---
    94. ------------------------
    95.     IF (:NEW.ts <> :OLD.ts) THEN
    96.         INSERT INTO demodb.customer_teile_log (
    97.             "Changenr",
    98.             fi_nr,
    99.             identnr,
    100.             var,
    101.             "ChangeTime",
    102.             "ChangedField",
    103.             "ChangedTable",
    104.             "NewValue",
    105.             "OldValue",
    106.             "ChangedBy")
    107.         VALUES (
    108.             (SELECT MAX("Changenr")+1 FROM demodb.customer_teile_log),
    109.             :NEW.fi_nr,
    110.             :NEW.identnr,
    111.             :NEW.var,
    112.             systimestamp,
    113.             'ts',
    114.             'g040',
    115.             :NEW.ts,
    116.             :OLD.ts,
    117.             :NEW.useraen);
    118.     END IF;
    119.  
    120. ------------------------
    121. ------- ZEICHNR---------
    122. ------------------------
    123.     IF (:NEW.zeichnr <> :OLD.zeichnr) THEN
    124.         INSERT INTO demodb.customer_teile_log (
    125.             "Changenr",
    126.             fi_nr,
    127.             identnr,
    128.             var,
    129.             "ChangeTime",
    130.             "ChangedField",
    131.             "ChangedTable",
    132.             "NewValue",
    133.             "OldValue",
    134.             "ChangedBy")
    135.         VALUES (
    136.             (SELECT MAX("Changenr")+1 FROM demodb.customer_teile_log),
    137.             :NEW.fi_nr,
    138.             :NEW.identnr,
    139.             :NEW.var,
    140.             systimestamp,
    141.             'zeichnr',
    142.             'g040',
    143.             :NEW.zeichnr,
    144.             :OLD.zeichnr,
    145.             :NEW.useraen);
    146.     END IF;
    147. END;
     
  7. Biber3

    Biber3 Erfahrenes Mitglied

    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
     
    Zuletzt bearbeitet: 19. September 2017 um 13:38 Uhr
    warface gefällt das.
  8. warface

    warface Mitglied

    @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.
     
  9. Biber3

    Biber3 Erfahrenes Mitglied

    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
     
    Zuletzt bearbeitet: 19. September 2017 um 18:31 Uhr
    warface gefällt das.
  10. warface

    warface Mitglied

    @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 :)
     
Die Seite wird geladen...