top of page

Temporal Tables (System-Versioned) Daten-Änderungsverfolgung / tracking data changes

>= 2016

Mittwoch, 9. April 2025

Deutsch

Hintergrund

Voraussetzungen

1. Tabelle vorbereiten

2. Tabelle als System-Versioned aktivieren

3. Änderungen automatisch verfolgen

4. Benutzeränderung zusätzlich protokollieren



Deutsch


Hintergrund

"Temporal Tables (System-Versioned)" speichern automatisch den zeitlichen Verlauf von Daten.

Jede Änderung erzeugt eine neue Version in einer Historientabelle.


Vorteile:

  • Historisierung "out-of-the-box"!

  • Einfache Abfragen über Zeiträume!


Nachteile:

  • Höherer Speicherverbrauch!

  • Weniger flexibel bei komplexen Logikanforderungen!

  • ⚠️Die Funktion protokolliert ausschließlich die eigentlichen Datenänderungen , aber nicht den Benutzer , der die Änderung durchgeführt hat - dies muss man selber implementieren!


Voraussetzungen

  • MS-SQL Server 2016 oder höher!

  • Die zu überwachende Tabelle muss einen Primary Key haben!

  • Man muss zwei zusätzliche Spalten: ValidFrom und ValidTo (für Zeitverlauf) anlegen!


Tipp: Für weitere Optionen einer Änderungsverfolgung siehe nachfolgende Übersicht: https://www.officium-inservio.com/ms-sql/changetrackingoverview



1. Tabelle vorbereiten

Falls passende Datenfelder wie z.B. "ValidFrom" und "ValidTo" noch nicht existieren , muss man diese anlegen.

ALTER TABLE dbo.MyTable ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN 
CONSTRAINT DF_MyTable_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
CONSTRAINT DF_MyTable_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

Die Namen der Felder sind nicht fest vorgeschrieben!

Alternativ könnte man auch so vorgehen (Beispiel 2):

ALTER TABLE dbo.MyTable ADD SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_MyTable_SysStart DEFAULT SYSUTCDATETIME(),    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN 
CONSTRAINT DF_MyTable_SysEnd DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

Das Wichtigste:

  • Es müssen beide Spalten mit GENERATED ALWAYS AS ROW START/END definiert werden!

  • Die Spalten müssen korrekt in PERIOD FOR SYSTEM_TIME (...) angegeben werden!

  • Die Namen dürfen beliebig sein , solange sie sich an SQL-Namenskonventionen halten!


2. Tabelle als System-Versioned aktivieren

Zuerst die gewünschte Historientabelle angeben oder automatisch generieren lassen.

Darin werden die historischen Daten gespeichert.

ALTER TABLE dbo.MyTable
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History));

Alternativ ohne eigenen Tabellennamen:

SET (SYSTEM_VERSIONING = ON);

Dann wird automatisch eine Historientabelle mit generischem Namen erstellt.


3. Änderungen automatisch verfolgen

Mit Abschluss des vorherigen Schritts speichert MS-SQL Server automatisch jede Änderung UPDATE/DELETE in der Historientabelle dbo.MyTable_History.


INSERT:

Die neue Zeile erscheint in der aktuellen Tabelle. Es gibt keinen Eintrag in der Historientabelle, da es keine "alte Version" gibt.


UPDATE:

Die "alte Version" der Zeile wird in die Historientabelle geschrieben, bevor die aktuelle Tabelle aktualisiert wird.


DELETE:

Bevor die Zeile gelöscht wird, schreibt SQL Server die letzte Version in die Historientabelle. Somit ist der letzte Zustand archiviert. Um zu sehen , ob ein Datensatz gelöscht wurde , ist ein Nachschauen in der Live-Tabelle möglich.

Außerdem wie folgt:

SELECT * FROM dbo.MyTable_History
WHERE ValidTo < '9999-12-31 23:59:59.9999999';

Diese Zeilen befinden sich nur in der History -> also gelöscht im Original.



4. Benutzeränderung zusätzlich protokollieren

Da "Temporal Tables" keine Benutzerinformationen mitloggen , muss man dieses Thema selber adressieren.


Ansatz 1:

Man kann sich ein entsprechendes zusätzliches Datenfeld für die Speicherung des Benutzers in der zu überwachenden Original-Tabelle anlegen.

Beispiel:

ALTER TABLE dbo.MyTable ADD ModifiedBy NVARCHAR(128) NULL;

Um nun den Benutzernamen der Änderung bereitzustellen , sind verschiedene Wege denkbar.

Nachfolgend ein Beispiel aus dem Sage 100-Umfeld.

Dort gibt es eine SQL-Funktion , die möglichst kontextbezogen den Benutzernamen bereitstellt , der aktuell kontextbezogen angemeldet ist.

SELECT [dbo].[fnGetPrincipalName]()

Ein Hinterlegen der o.g. SQL-Funktion als Standardwert des Datenfeldes ist jedoch keine vollständig funktionierende Lösung.

ALTER TABLE dbo.MyTable ADD ModifiedBy NVARCHAR(128)     CONSTRAINT DF_MyTable_ModifiedBy DEFAULT ([dbo].[fnGetPrincipalName]());

⚠️Der Default-Wert greift natürlich nur bei INSERTS, nicht bei Updates!

Hier hilft dann ein simpler SQL-Trigger auf der SQL-Tabelle:

CREATE TRIGGER trg_MyTable
ON dbo.MyTable
AFTER UPDATE
AS
BEGIN

    UPDATE dbo.MyTable
    SET ModifiedBy = [dbo].[fnGetPrincipalName]()
    FROM dbo.MyTable
    INNER JOIN inserted ON dbo.MyTable.PrimaryKey = inserted.PrimaryKey;

END

Alternativ kann man sich im Sage 100-Umfeld in Sage 100-Tabellen , die über Sage AppDesigner Masken bearbeitet werden , auch dort entsprechende Felder integrieren und den Benutzernamen anwendungsseitig über ein Makro befüllen - mit entsprechenden Vorteilen/Nachteilen!

Der gravierenste Nachteil ist, dass diese Variante natürlich keinerlei SQL-Änderungen berücksichtigt.


Ansatz 2:

⚠️Die zuvor beschrieben Variante mit einem eigenen Datenfeld in der Original-Tabelle und dem Update Trigger auf dieselbe , soeben veränderte Original-Tabelle hat zwei entscheidende Nachteile:


  1. Bei (nahezu) gleichzeitigen Änderungen derselben Zeile durch mehrere Benutzer "gewinnt" natürlich immer der letzte Benutzer, dessen Transaktion erfolgreich committed wurde!

  2. Manche Applikationen haben Probleme damit , wenn der soeben neu gespeicherte Datensatz zeitnah durch einen SQL-Trigger sofort noch einmal verändert wird. Applikationen, die den Datensatz nach dem Speichern vollständig neu laden , sollten damit kein Problem haben.


Unter Umständen stören die zuvor genannten Nachteile und dies ist nicht das gewünschte Verhalten!

Es gibt verschiedene Lösungsansätze , um mit dieser Situation umzugehen.


"Locking" in Anwendung:

Man kann sich in eigenen Tabellen ein RowVersion-Feld hinzufügen (Timestamp-Typ). Dies ist ohnehin generell empfehlenswert.

Konsequenz:

Wenn sich die Version geändert hat, wurde der Datensatz zwischenzeitlich geändert und dies könnte man anwendungsseitig abfangen und dann die erneute/"zeitgleiche" Änderung verhindern. Ebenso ist es wichtig , nach dem Speichern eines Datensatzes den letzten Stand von der Datenbank erneut abzurufen.

Dies macht die Sage 100 in vielen Fällen bereits so!


Eigene SQL-Tabelle für Fortschreiben der Benutzerhistorie:


Ein weiterer möglicher Ansatz , der viele Probleme löst und lückenlos die Benutzernamen dokumentiert , ist das Protokollieren der Benutzer in einer eigenen zusätzlichen Tabelle.


Der o.g. SQL-Trigger wäre entsprechend anzupassen, dass sowohl bei INSERT/UPDATE/DELETE der Status in der eigenen SQL-Tabelle festgehalten wird , indem pro Datum/Uhrzeit der Benutzer mit der Verknüpfung zum betreffenden Datensatz fortgeschrieben wird.

D.h. , eine separate(!) Audit-Tabelle nur mit den Primärschlüssel(n) , Zeitstempel , Änderungsart (INSERT/UPDATE/DELETE) und dem Benutzernamen.


Dadurch wird jede Veränderung chronologisch lückenlos protokolliert.

Diese Variante ist in der Praxis zwar etwas aufwändiger einzurichten , bietet aber die höchste Sicherheit einer lückenlosen Dokumentation der Änderungen.

Zusätzlich sind auch die Änderungen auf Datenebene durch die _Historie-Tabelle protokolliert!













bottom of page