Mit der Einführung von System Versioned Temporal Tables wurde für die Programmierer ein Weg geschaffen, um eigene Historisierungslösungen ad acta zu legen. In grauer Vorzeit verwendete man entweder Trigger oder Stored Procedures für die Entwicklung einer eigenen Historisierungslösung. Die Möglichkeiten dieser Lösungen waren beschränkt und unter Umständen sehr fehleranfällig. Viele Entwickler haben den Wunsch, im Datensatz den Benutzer zu speichern, der zuletzt Änderungen vorgenommen hat. Üblicherweise kann das nur mit Hilfe eines UPDATE-Triggers geschehen. Das AFTER-Trigger erhebliche Probleme in System Versioned Temporal Tables verursachen können, habe ich im Artikel “Temporal Tables – Verwendung von Triggern” bereits beschrieben. INSTEAD OF Trigger sind in System Versioned Temporal Tables nicht erlaubt. Was also tun? Dieser Artikel beschreibt eine Lösung, in der INSTEAD OF-Trigger dennoch zum Erfolg führen.

Testumgebung

Für die nächsten Beispiele wird erneut die Tabelle [dbo].[Customer] in leicht abgewandelter Form verwendet. Die Tabelle besitzt ein Attribut mit dem Namen [UpdateUser]. Dieses Attribut soll bei jeder Aktualisierung automatisch mit dem Namen des Bearbeiters aktualisiert werden.

-- Create a dedicated schema for the history data
IF SCHEMA_ID(N'history') IS NULL
    EXEC sp_executesql N'CREATE SCHEMA [history] AUTHORIZATION dbo;';
    GO

-- Create the System Versioned Temporal Table
CREATE TABLE dbo.Customers
(
    Id         INT          NOT NULL    IDENTITY (1, 1),
    Name       VARCHAR(100) NOT NULL,
    Street     VARCHAR(100) NOT NULL,
    ZIP        CHAR(5)      NOT NULL,
    City       VARCHAR(100) NOT NULL,
    UpdateUser SYSNAME      NOT NULL DEFAULT (ORIGINAL_LOGIN()),
    ValidFrom  DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT ('2016-01-01T00:00:00'),
    ValidTo    DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL DEFAULT ('9999-12-31T23:59:59'),

    CONSTRAINT pk_Customers_ID PRIMARY KEY CLUSTERED (Id),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
GO

-- Create the history table
CREATE TABLE history.Customers
(
    Id         INT          NOT NULL    IDENTITY (1, 1),
    Name       VARCHAR(100) NOT NULL,
    Street     VARCHAR(100) NOT NULL,
    ZIP        CHAR(5)      NOT NULL,
    City       VARCHAR(100) NOT NULL,
    UpdateUser SYSNAME      NOT NULL,
    ValidFrom  DATETIME2(0) NOT NULL,
    ValidTo    DATETIME2(0) NOT NULL
);
GO

INSTEAD OF -Trigger sind weder bei der aktuellen noch bei der Verlaufstabelle zulässig, um zu verhindern, dass die DML-Logik ungültig wird. INSTEAD OF-Trigger setzen die Standardaktionen der auslösenden Anweisung (INSERT, UPDATE oder DELETE) außer Kraft. Ein INSTEAD OF-Trigger kann definiert werden, der eine Fehler- oder Wertüberprüfung für eine oder mehrere Spalten ausführt und anschließend weitere Aktionen ausführt, bevor der Datensatz eingefügt wird.
INSTEAD OF-Trigger können sowohl für Tabellen als auch für Sichten definiert werden.

Die Möglichkeit, mit System Versioned Temporal Tables und INSTEAD OF Triggern zu arbeiten, besteht darin, nicht direkt auf die Tabelle zuzugreifen sondern mit Hilfe einer View die Daten der zugrunde liegenden Tabelle zu bearbeiten!

-- Erstellen einer View für Tabelle dbo.Customers
CREATE OR ALTER VIEW dbo.v_Customers
AS
    SELECT Id, Name, Street, ZIP, City, UpdateUser
    FROM dbo.Customers;
GO

-- Erstellen eines INSTEAD OF Triggers für die View
CREATE OR ALTER TRIGGER dbo.trg_v_Customers_Update
ON dbo.v_Customers
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE C
    SET    C.Name = I.Name,
           C.Street = I.Street,
           C.ZIP = I.ZIP,
           C.City = I.City,
           C.UpdateUser = SUSER_SNAME()
    FROM   dbo.Customers AS C INNER JOIN inserted AS I
           ON (C.Id = I.Id)

    SET NOCOUNT OFF;
END
GO

Sobald die View angelegt und der Trigger implementiert wurde, kann auch “dynamisch” der Eintrag für das Attribut [UpdateUser] gesetzt werden.

UPDATE dbo.v_Customers
SET    Name = 'db Berater GmbH',
       Street = 'Bahnstr. 33',
       ZIP = '64390',
       City = 'Erzhausen'
WHERE  Id = 10;
GO

Herzlichen Dank fürs Lesen!

Bisher veröffentlichte Artikel zu System Versioned Temporal Tables

Shares
Share This