Trigger sind eine beliebte Technologie, um Geschäftsregeln auf Ebene von Tabellen zu implementieren. Durch die Verwendung von Triggern kann z. B. für die bearbeiteten Datensätze immer der Name und das Datum des letzten Anwenders gespeichert werden, der den Datensatz manipuliert hat. Von relativ einfachen bis zu komplexen Regelwerken sind Trigger in Datenbanken von vielen Entwicklern eine gerne adaptierte Technologie. So “elegant” die Verwendung von Triggern für viele Entwickler sein mag – im Zusammenhang mit “System Versioned Temporal Tables” sollten sie auf keinen Fall verwendet werden. Der folgende Artikel zeigt einen klassischen Anwendungsfall, der bei Implementierung von “System Versioned Temporal Tables” eklatante Nachteile in sich birgt.

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
 
IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
    DROP TABLE dbo.Customers;
    GO
 
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 TABLE history.Customers
(
    Id         INT          NOT NULL,
    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
 
ALTER TABLE dbo.Customers
SET
    (
        SYSTEM_VERSIONING = ON
        (HISTORY_TABLE = History.Customers)
    );
GO

Für die Speicherung der historischen Daten wird die Tabelle [history].[Customers] bereitgestellt. Zusätzlich wird für die Tabelle [dbo].[Customers] ein Trigger entwickelt, der nach der Aktualisierung die betroffenen Datensätze mit dem Namen des Bearbeiters kennzeichnet/aktualisiert.

CREATE TRIGGER dbo.trg_Customers_Update
ON dbo.Customers
FOR UPDATE
AS
    SET NOCOUNT ON;
 
    -- Update the [UpdateUser] with information about
    -- the user!
    UPDATE c
    SET    UpdateUser = 'Donald Duck'
    FROM   dbo.Customers AS C INNER JOIN inserted AS I
           ON (C.Id = I.Id)
 
    SET NOCOUNT OFF;
GO

Szenario

Das folgende Beispiel zeigt, wie Microsoft SQL Server den implementierten Trigger auf [dbo].[Customer] verarbeitet. Dazu wird in einer expliziten Transaktion zunächst der Datensatz mit der ID = 10 aktualisiert.

-- now the first record will be updated
BEGIN TRANSACTION;
GO
 
    UPDATE dbo.Customers
    SET    Name = 'db Berater GmbH'
    WHERE  Id = 10;
    GO
 
    SELECT DTL.resource_type,
           T.object_name,
           DTL.resource_description,
           DTL.request_type,
           DTL.request_mode,
           DTL.request_status
    FROM   sys.dm_tran_locks AS DTL
           OUTER APPLY
           (
               SELECT s.name + N'.' + t.name    AS object_name
               FROM   sys.schemas AS S INNER JOIN sys.tables AS T
                      ON (S.schema_id = T.schema_id)
               WHERE  T.object_id = DTL.resource_associated_entity_id
           ) AS T
    WHERE  DTL.request_session_id = @@SPID
           AND DTL.resource_type != N'DATABASE';
    GO
 
COMMIT TRANSACTION;
GO

Der zweite Teil der obigen Abfrage ermittelt die innerhalb der Transaktion gesetzten Sperren. Dadurch wird erkennbar, welche Objekte durch die Transaktion verwendet/blockiert werden.

In Zeile 9 ist eine exklusive Schlüsselsperre erkennbar. Hierbei handelt es sich um den Datensatz in [dbo].[Customers] der für die Aktualisierung gesperrt wurde. Wesentlich interessanter jedoch ist die RID-Sperre (Rowlocator ID in einem HEAP) für zwei (!) Datenzeilen. Die exklusiven Sperren auf die Ressource 1:2264:2 und 1:2264:3 weisen darauf hin, dass ein Datensatz auf Datenseite 2264 in Slot 2 und ebenfalls in Slot 3 gesperrt wurden. Die Datenseite gehört zur Tabelle [history].[Customers]. Zwei Slots = zwei Datensätze. Microsoft SQL Server verarbeitet in der History Tabelle also zwei Datensätze! Einen noch genaueren Einblick in die Transaktion offenbart ein Blick in das Transaktionsprotokoll:

SELECT  Operation,
        Context,
        AllocUnitName,
        [Page ID],
        [Slot ID]
FROM    sys.fn_dblog(NULL, NULL)
WHERE   [Transaction ID] IN
        (
            SELECT  [Transaction ID]
            FROM    sys.fn_dblog(NULL, NULL)
            WHERE   [Transaction Name] = N'user_transaction'
        )
ORDER BY
        [Current LSN] ASC;
GO

Die Transaktionsschritte sind chronologisch aufgeführt. Nachdem in Zeile 1 die Transaktion beginnt, wird zunächst ein Datensatz in [dbo].[Customers] geändert [LOP_MODIFY_ROW]. Diese Änderung führt zu einem neuen Eintrag in die Tabelle [history].[Customers] und wird durch die Operation [LOP_INSERT_ROWS] gekennzeichnet. Dieser Prozess wird automatisch durch die implementierte Technologie von “System Versioned Temporal Tables” initiiert. Nachdem der ALTER Datensatz in die History Tabelle eingetragen wurde, wird der benutzerdefinierte Trigger für UPDATE-Ereignisse gestartet und modifiziert den bereits geänderten Datensatz erneut [LOP_MODIFYING_COLUMNS] und erneut schlägt die Technologie von “System Versioned Temporal Tables” zu und trägt den vorherigen Datensatz in die History Tabelle ein. Zum Schluss wird die Transaktion geschlossen [LOP_COMMIT_XACT]. Ein Blick auf die gespeicherten Daten aus beiden Tabellen visualisiert die zuvor beschriebenen Prozessschritte:

;WITH T
AS
(
    SELECT  C.*
    FROM    dbo.Customers AS C
    WHERE   Id = 10
 
    UNION ALL
 
    SELECT  C.*
    FROM    history.Customers AS C
    WHERE   C.Id = 10
)
SELECT * FROM T
ORDER BY ValidFrom DESC;
GO

In Zeile 3 befindet sich der ursprüngliche Datensatz, dessen Name zunächst geändert wurde. Durch die Aktualisierung jedoch wurde dieser Datensatz erneut in die History Tabelle gespeichert und in Zeile 1 steht der letztendlich in [dbo].[Customers] gespeicherte Datensatz!

Lösungen?

Viele Datenbankanwendungen verwenden Trigger und die Entwickler haben viel Businesslogik in diese Trigger implementiert. Eine “einfache” Portierung der Triggerlogik in andere Prozesskanäle ist nicht schnell realisierbar. Welche Möglichkeiten bestehen also, dieses Dilemma zu lösen?

Verwendung von INSTEAD OF Trigger

Eine Idee wäre, den UPDATE-Prozess im Vorfeld abzufangen, die Daten zu manipulieren und dann in einem Update-Statement einzutragen. Während das oben beschriebene Problemszenario mit AFTER-Triggern arbeitet, sollte eine INSTEAD OF-Lösung den gewünschten Erfolg bringen. AFTER-Trigger werden abgefeuert, wenn der Datensatz bereits aktualisiert wurde (innerhalb der Transaktion) und somit die Änderungen bereits in das Transaktionsprotokoll eingetragen wurden. Ein INSTEAD OF Trigger wird ausgeführt, BEVOR die Datenmanipulation stattfindet. Für das Eintragen/Aktualisieren von Daten ist dann der Trigger selbst verantwortlich.

 CREATE TRIGGER dbo.trg_Customers_Update
ON dbo.Customers
INSTEAD OF UPDATE
AS
    SET NOCOUNT ON;
 
    -- Update the [UpdateUser] with information about
    -- the user!
    UPDATE C
    SET    C.Name = I.Name,
           C.Street = I.Street,
           C.ZIP = I.ZIP,
           C.City = I.City,
           C.UpdateUser = 'Donald Duck'
    FROM   dbo.Customers AS C INNER JOIN inserted AS I
           ON (C.Id = I.Id)
 
    SET NOCOUNT OFF;
GO

Das obige Codebeispiel zeigt, dass der vollständige UPDATE-Prozess durch den Trigger verwaltet wird. Versucht man jedoch, den Trigger zu implementieren, stößt man unweigerlich an die Einschränkungen von “System Versioned Temporal Tables”.

System Versioned Temporal Tables erlauben keine INSTEAD OF Trigger. Diese Restriktion ist zwingend, da Temporal Tables gemäß ANSI 2011 Standard implementiert wurden. Würde ein INSTEAD OF Trigger zugelassen werden, bestünde die Möglichkeit, die Daten in der Tabelle [deleted] zu manipulieren und anschließend diese Daten in die History Tabelle zu leiten.

Verwendung von Stored Procedures

Aus mehreren Gründen empfiehlt sich die Verwendung von Stored Procedures. Sie fordert jedoch ein Umdenken bei den Entwicklern. Statt adhoc-Abfragen in der Applikation zu generieren, die DML-Operationen an die Datenbank senden, wäre die Verwendung von Stored Procedures von mehreren Vorteilen geprägt:

Abstraktionsschicht zwischen Anwendung und Daten
Implementierung von Geschäftslogik als Business-Schicht
Restriktion des Zugriffs auf die Daten

-- Implementation of logic as stored procedure
CREATE PROC dbo.proc_update_customer
    @Id     INT,
    @Name   VARCHAR(100),
    @Street VARCHAR(100),
    @ZIP    CHAR(5),
    @City   VARCHAR(100)
AS
    SET NOCOUNT ON;
 
    -- now the record will be updated with all
    -- information
    UPDATE  dbo.Customers
    SET     Name       = @Name,
            Street     = @Street,
            ZIP        = @ZIP,
            City       = @City,
            -- implementation of UpdateUser
            UpdateUser = ORIGINAL_LOGIN()
    WHERE   Id = @Id;
 
    SET NOCOUNT OFF;
GO

Zusammenfassung

Die Verwendung von Triggern in System Versioned Temporal Tables sollte auf jeden Fall vermieden werden. Durch Trigger, die nachträglich betroffene Datensätze manipulieren, wird ein nicht unerheblicher Overhead an Daten in der History Tabelle generiert. Statt auf die Implementierung von Triggern zu setzen, sollte die Verwendung von Stored Procedures in Betracht gezogen werden. Sie bietet neben der Eliminierung der zuvor genannten Nachteile von Triggern auch Sicherheitsaspekte, um die Daten der Tabellen nicht unmittelbar zu manipulieren.

Bisher veröffentlichte Artikel zu System Versioned Temporal Tables

Herzlichen Dank fürs Lesen!

Temporal Tables – Verwendung von Triggern