DB2-Historientabellen-Trigger erstellen

Question

Ich möchte eine Historientabelle erstellen, um Feldänderungen in einer Reihe von Tabellen in DB2 zu verfolgen.

Ich weiß, dass die Historie in der Regel durch das Kopieren der gesamten Tabellenstruktur und die Vergabe eines Namens mit Suffix erfolgt (z. B. user --> user_history). Dann können Sie einen ziemlich einfachen Trigger verwenden, um den alten Datensatz bei einem UPDATE in die History-Tabelle zu kopieren.

Für meine Anwendung würde dies jedoch zu viel Platz beanspruchen. Es scheint (zumindest mir) keine gute Idee zu sein, jedes Mal, wenn sich ein Feld ändert, einen ganzen Datensatz in eine andere Tabelle zu kopieren. Also dachte ich, ich könnte eine allgemeine "Verlaufstabelle" haben, die einzelne Feldänderungen nachverfolgen würde:

CREATE TABLE history
(
    history_id LONG GENERATED ALWAYS AS IDENTITY,
    record_id INTEGER NOT NULL,
    table_name VARCHAR(32) NOT NULL,
    field_name VARCHAR(64) NOT NULL,
    field_value VARCHAR(1024),
    change_time TIMESTAMP,
    PRIMARY KEY (history_id)
);

OK, also jede Tabelle, die ich verfolgen möchte, hat ein einzelnes, automatisch generiertes id-Feld als Primärschlüssel, das in das Feld "record_id" gesetzt wird. Und die maximale VARCHAR-Größe in den Tabellen beträgt 1024. Wenn sich ein Nicht-VARCHAR-Feld ändert, muss es natürlich in ein VARCHAR-Feld konvertiert werden, bevor der Datensatz in die Verlaufstabelle eingefügt wird.

Nun, das könnte eine völlig bescheuerte Art sein, Dinge zu tun (hey, lassen Sie mich wissen, warum, wenn es so ist), aber ich denke, es ist eine gute Möglichkeit, Änderungen zu verfolgen, die selten abgerufen werden müssen und für eine beträchtliche Zeitspanne gespeichert werden müssen.

Wie auch immer, ich brauche Hilfe beim Schreiben des Triggers zum Hinzufügen von Datensätzen zur Verlaufstabelle bei einer Aktualisierung. Nehmen wir zum Beispiel eine hypothetische Benutzertabelle:

CREATE TABLE user
(
   user_id INTEGER GENERATED ALWAYS AS IDENTITY,
   username VARCHAR(32) NOT NULL,
   first_name VARCHAR(64) NOT NULL,
   last_name VARCHAR(64) NOT NULL,
   email_address VARCHAR(256) NOT NULL
   PRIMARY KEY(user_id)
);

Kann mir also jemand mit einem Trigger helfen, der bei einer Aktualisierung der Benutzertabelle die Änderungen in die Verlaufstabelle einfügt? Ich vermute, dass eine prozedurale SQL-Anweisung verwendet werden muss, um die Felder des alten Datensatzes in einer Schleife zu durchsuchen, sie mit den Feldern des neuen Datensatzes zu vergleichen und dann, wenn sie nicht übereinstimmen, einen neuen Eintrag in die Verlaufstabelle einzufügen.

Es wäre besser, für jede Tabelle unabhängig von ihren Feldern dieselbe Triggeraktion SQL zu verwenden, wenn dies möglich ist.

Merci !

Answer 1

Ich halte dies für keine gute Idee, da Sie bei einer großen Tabelle, in der sich mehr als ein Wert ändert, noch mehr Overhead pro Wert erzeugen. Aber das hängt von Ihrer Anwendung ab.

Darüber hinaus sollten Sie den praktischen Wert einer solchen Geschichtstabelle bedenken. Sie müssen eine Menge Zeilen zusammenstellen, um auch nur einen Hauch von Kontext zu dem geänderten Wert zu erhalten, und Sie müssen eine weitere Anwendung programmieren, die genau diese komplexe Verlaufslogik für einen Endbenutzer übernimmt. Und für einen DB-Administrator wäre es mühsam, Werte aus der Historie wiederherzustellen.

Es mag etwas hart klingen, aber das ist nicht die Absicht. Ein erfahrener Programmierer in unserem Geschäft hatte eine ähnliche Idee mit dem Tabellenjournalismus. Er hat es zum Laufen gebracht, aber es hat Speicherplatz gefressen, als gäbe es kein Morgen.

Denken Sie einmal darüber nach, was Ihr Geschichtstisch eigentlich leisten soll.

Answer 2

Haben Sie daran gedacht, dies in zwei Schritten zu tun? Implementieren Sie einen einfachen Trigger, der die ursprüngliche und die geänderte Version der gesamten Zeile aufzeichnet. Dann schreiben Sie ein separates Programm, das einmal am Tag läuft, um die geänderten Felder zu extrahieren, wie Sie es oben beschrieben haben.

Das macht den Auslöser einfacher, sicherer und schneller, und Sie haben mehr Möglichkeiten, wie Sie die Nachbearbeitung durchführen können.

Answer 3

In unserer SQL Server-Datenbank machen wir etwas Ähnliches, aber die Prüftabellen sind für jede einzelne geprüfte Tabelle (eine zentrale Tabelle wäre riesig, da unsere Datenbank viele, viele Gigabyte groß ist).

Achten Sie darauf, dass Sie auch festhalten, wer die Änderung vorgenommen hat. Sie sollten auch den alten und den neuen Wert zusammen aufzeichnen (so können Sie die Daten bei Bedarf leichter zurücksetzen) und die Art der Änderung (Einfügen, Aktualisieren, Löschen). Sie erwähnen nicht, dass Sie Löschungen aus der Tabelle aufzeichnen, aber wir finden, dass dies zu den Dingen gehört, für die wir die Tabelle am häufigsten verwenden.

Wir verwenden dynamisches SQl, um den Code für die Erstellung der Prüftabellen zu generieren (indem wir die Tabelle verwenden, in der die Systeminformationen gespeichert sind), und alle Prüftabellen haben genau dieselbe Struktur (was es einfacher macht, Daten wieder herauszuholen).

Wenn Sie den Code zum Speichern der Daten in Ihrer Verlaufstabelle erstellen, erstellen Sie auch den Code zum Wiederherstellen der Daten, falls dies erforderlich ist. Das spart eine Menge Zeit, wenn etwas wiederhergestellt werden muss und Sie von der Geschäftsleitung unter Druck gesetzt werden, es jetzt zu erledigen.

Ich weiß nicht, ob Sie vorhatten, Daten aus Ihrer Verlaufstabelle wiederherzustellen, aber wenn Sie es einmal getan haben, kann ich Ihnen garantieren, dass die Geschäftsleitung es auf diese Weise nutzen wird.

Answer 4

CREATE TABLE HIST.TB_HISTORY ( 
    HIST_ID     BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1, NO CACHE) NOT NULL,
    HIST_COLUMNNAME     VARCHAR(128) NOT NULL,
    HIST_OLDVALUE       VARCHAR(255),
    HIST_NEWVALUE       VARCHAR(255),
    HIST_CHANGEDDATE    TIMESTAMP NOT NULL
    PRIMARY KEY(HIST_SAFTYNO)
)
GO

CREATE TRIGGER COMMON.TG_BANKCODE AFTER
UPDATE OF FRD_BANKCODE ON COMMON.TB_MAINTENANCE
REFERENCING OLD AS oldcol NEW AS newcol FOR EACH ROW MODE DB2SQL
WHEN(COALESCE(newcol.FRD_BANKCODE,'#null#') <> COALESCE(oldcol.FRD_BANKCODE,'#null#'))
BEGIN ATOMIC

    CALL FB_CHECKING.SP_FRAUDHISTORY_ON_DATACHANGED(
                newcol.FRD_FRAUDID,
                'FRD_BANKCODE',
                oldcol.FRD_BANKCODE,
                newcol.FRD_BANKCODE,
                newcol.FRD_UPDATEDBY
    );--

    INSERT INTO FB_CHECKING.TB_FRAUDMAINHISTORY(        
        HIST_COLUMNNAME, 
        HIST_OLDVALUE, 
        HIST_NEWVALUE, 
        HIST_CHANGEDDATE