ÜBERWACHUNG VON SQL-TABELLEN ÄNDERUNGEN MITTELS TRIGGER

Bei Transaktionsdatenbanken ist oftmals eine Anforderung, die Änderungen zu überwachen um zu protokollieren, wer welche Änderungen vorgenommen hat. Ich habe im Laufe der letzten Jahre/Jahrzehnte viele Methoden ausprobiert und die hier dargestellte Version ist – nach derzeitiger Meinung – die beste Methode. Basis ist die hier dargestellte Methode. Ich habe ein paar Kleinigkeiten adaptiert, um sie genereller anwenden zu können.

Ziel ist eine Tabelle, in der die Änderungen protokolliert werden. Es handelt sich Tabellen, die CDC (Change Data Capture) erfassen. Alle Änderungen der Datenbank werden in eine gemeinsame Tabelle geschrieben. Es wäre auch möglich für jede Tabelle eine eigene CDC-Tabelle zu führen. Ich finde eine Tabelle für die gesamte DB als zweckdienlicher.

CREATE TABLE [dbo].[AuditLog] (
    id bigint NOT NULL,
    Table2Audit nvarchar(350) NOT NULL,
    OldRowData nvarchar(max) CHECK(ISJSON(OldRowData) = 1),
    NewRowData nvarchar(max) CHECK(ISJSON(NewRowData) = 1),
    DmlType varchar(10) NOT NULL CHECK (DmlType IN ('INSERT', 'UPDATE', 'DELETE')),
    DmlTimestamp datetime NOT NULL,
    DmlCreatedBy varchar(255) NOT NULL,
    TrxTimestamp datetime NOT NULL,
    PRIMARY KEY (Id, DmlType, DmlTimestamp)
)

Was wir jetzt noch brauchen, sind 3 Trigger auf jeder Tabelle, die die Änderungen wegloggen.

INSERT
UPDATE
DELTE

Bei den nachfolgenden SQL-Schnippsel sind die gelben Elemente durch die Informationen der jeweiligen Tabelle zu ersetzen.

INSERT TRIGGER

CREATE TRIGGER [dbo].[TR_Datentabelle_insert] ON [dbo].[Datentabelle]
FOR INSERT AS
BEGIN
    DECLARE @loggedUser varchar(255) = CAST(ORIGINAL_LOGIN() AS varchar(255))
    DECLARE @Table2Audit as nvarchar(355) = '[dbo].[Datentabelle]'
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
     
    INSERT INTO AuditLog (
        id,
	[Table2Audit],
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Inserted),
	@Table2Audit,
        null,
        (SELECT * FROM Inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        'INSERT',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

UPDATE TRIGGER

CREATE TRIGGER [dbo].[TR_Datentabelle_update] ON [dbo].[Datentabelle]
FOR UPDATE AS
BEGIN
    DECLARE @loggedUser varchar(255) = CAST(ORIGINAL_LOGIN() AS varchar(255))
    DECLARE @Table2Audit as nvarchar(355) = '[dbo].[Datentabelle]'
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()

	INSERT INTO AuditLog (
        id,
	[Table2Audit],
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Inserted),
	@Table2Audit,
        (SELECT * FROM Deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        (SELECT * FROM Inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        'UPDATE',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

DELTE TRIGGER

CREATE TRIGGER [dbo].[TR_Datentabelle_delete] ON [dbo].[Datentabelle]
FOR DELETE AS
BEGIN
    DECLARE @loggedUser as varchar(255) = CAST(ORIGINAL_LOGIN() AS varchar(255))
    DECLARE @Table2Audit as nvarchar(355) = '[dbo].[Datentabelle]'
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
     
    INSERT INTO AuditLog (
        id,
	[Table2Audit],
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Deleted),
	@Table2Audit,
        (SELECT * FROM Deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        null,
        'DELETE',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END