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!
