Änderungshistory in Microsoft SQL Datenbanken. Audit Trails mit Triggern.

Einleitung
Zur Überwachung von Änderungen an Datensätzen in Microsoft SQL Datenbanken sind Trigger unersetzlich. Mit dem Zugriff auf die speziellen Tabellen „inserted“ für in einer Operation eingefügte Datensätze und „deleted“ für gelöschte hat man darüber hinaus alle Möglichkeiten offen um Datensatzveränderungen lokalisieren und loggen zu können.

Als einfachsten Weg um Datensatzänderungen nachvollziehbar zu machen können sogenannte Shadow-Tables angesehen werden. Diese Tabellen sind einfache 1:1-Kopien der Original-Tabellen, die um ein paar Informations-Spalten erweitert sind. Solche Spalten könnten zum Beispiel informative Werte wie „Änderungsdatum“ oder Ähnliches sein.

Eine 1:1-Kopie mit den Daten vor und nach der Änderung zu befüllen ist auch ohne Mühe machbar. Hier reicht ja einfach ein INSERT-Statement a la

INSERT INTO dbo.tbl_Test_Shadow 
SELECT 
*, GETDATE() AS AuditDate 
FROM 
inserted

Hier muss man vielleicht noch ein wenig erweitern um mit eventuellen Autowerten klarzukommen. Ein wenig Anpassung ist ja in den meisten Fällen nicht nur nötig sondern auch hilfreich.

Der Nachteil ist, dass bei dieser Alternative der Änderungshistorie schnell mal ein größeres INSERT-Statement zustande kommen kann und vor allem hat es das gravierende Problem, dass eine Auswertung nach geänderten Werten schwierig ist beziehungsweise nur mit einem hohen Gewicht realisierbar ist, da hier Datensätze miteinander verglichen werden müssten um herauszufinden welcher explizite Wert sich denn nun geändert hat.

Die Ánforderung
Sinnvoll wäre es also, wenn man diesen Trigger dahin ausbauen könnte, dass man direkt mitschreibt welcher Wert oder welche Werte sich bei einer Operation verändert haben oder betroffen waren.
Das ist mit viel Zeilen natürlich machbar mit direkten Vergleichen, aber das wird schon dann wieder komplizierter, wenn man bedenkt dass wir in SQL ja Mengenorientiert unterwegs sind und damit kann in der inserted oder der deleted-Table ja auch mehr als ein Datensatz stehen. Da wird ein Vergleich dann schon mal unübersichtlicher. Vor allem macht das spätestens dann keinen Spass mehr, wenn man so einen Trigger für mehr als fünf Tabellen einrichten muss, denn das ist viel Schreibarbeit und weit entfernt von einer optimalen Lösung für faule Entwickler.

Die Idee
Der im Folgenden vorgestellte Ansatz geht deswegen einen anderen – dynamischeren – Weg. Es soll eine Funktionalität entstehen, die Tabellenunabhängig ist, aber dennoch die Tabellenänderungen im Einzelnen per Trigger verfolgt. Da wir hier nun nicht mehr alles an Daten mitloggen sondern nur noch die Daten die sich tatsächlich geändert haben, spreche ich im Folgenden auch nicht mehr von Shadow-Tables sondern von Audit-Tables, da ein echter Changelog ensteht.
Ob man dabei den Weg wählt für jede zu überwachende Tabelle eine eigene Audit-Tabelle anzulegen oder ob man eine globale Audit-Tabelle nimmt, sei dem Leser dabei selber überlassen. Vorgestellt wird die Variante mit je einer Audit-Tabelle pro zu überwachender Tabelle.
Zudem werde ich hier nur den AFTER UPDATE-Trigger vorstellen da dieser der komplexeste Trigger ist und AFTER INSERT und AFTER DELETE sich hiervon ableiten.

Die Voraussetzungen
Unsere Voraussetzung ist, das jede zu überwachende Tabelle eine rowguid besitzt. Das ist aber kein Muss, wie wir unten sehen werden. Zudem hat – da wir jede Tabelle einzeln auditieren – jede Tabelle eine Audit-Table die immer die Form hat.

CREATE TABLE dbo.tbl_Test_Audit
(
	id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	Operation NVARCHAR(20) NOT NULL,
	SourceUser NVARCHAR(255) NOT NULL,
	AuditDate DATETIME NOT NULL
	rowguid uniqueidentifier NOT NULL
	ColumnName NVARCHAR(255) NOT NULL,
	DeletedValue NVARCHAR(2000) NOT NULL,
	InsertedValue NVARCHAR(2000) NOT NULL
)

Wie die zu überwachenden Tabellen aussehen, ist uns erstmal relativ egal.

Die Umsetzung
Wir werden den Trigger hier Abschnittsweise durchgehen, da der Ablauf ziemlich Straight-Forward ist. Ein kompletter Download des Codes ist am Ende des Artikels möglich.

CREATE TRIGGER dbo.udt_Test_Audit_Update ON dbo_scm.tbl_Test
AFTER UPDATE
AS

Wir wollen dass unser Trigger für alle Tabellen mit möglichst wenig Anpassungen funktioniert, also holen wir uns aus den sysobjects/ syscolumns die Tabellendefinition der zu überwachenden Tabelle um diese dann Spalte für Spalte durchzugehen, zu vergleichen und im Falle von Ungleichheiten diese in der Audit-Table abzuspeichern.
Ich habe mich in dem Ansatz dafür entschieden die Spaltennamen zuerst aus der sysobjects/ syscolumns semikolon-separiert in einem String abzulegen um diese später Schritt für Schritt durchzugehen. Gegenüber anderen Alternativen spart man sich damit auf jeden Fall einen CURSOR, den ich bei Triggern prinzipiell vermeiden würde.
Hierfür brauchen wir die Variablen ColumnName, für die aktuelle Spalte und ColumnList für die semikolon-separierte Liste.

Zudem geben wir in diesem Beispiel den Tabellennamen der zu überwachenden Tabelle in der Variablen TableName explizit an.

	--alle columns von inserted durchgehen
	DECLARE @TableName AS NVARCHAR(255)
	SET @TableName = 'tbl_Test'

	DECLARE @ColumnName AS NVARCHAR(255)
	DECLARE @Hostname AS NVARCHAR(255)
	DECLARE @ColumnList AS NVARCHAR(2000)
	DECLARE @Column AS NVARCHAR(255)
	DECLARE @SQL AS NVARCHAR(2000)

Mit HOST_NAME() erhalten wir den Namen der SQL-Instanz die die Operation „zu verantworten“ hat. Das ist inbesondere in Replikationsszenarien interessant, da hier dann die Information verfügbar ist, welches Replikationsteilnehmer, also welcher Datenbank-User, die Änderung vorgenommen hat. Damit sind alle Änderungen und Datenbankoperationen direkt auf einen User zurückzuführen.
Der Grund warum ich hier den Hostnamen vorinitialisiere statt die HOST_NAME()-Function weiter unten einfach auzurufen ist, dass mit der unten definierten dynamischen SQL-Ausführung der Benutzerkontext wechselt und damit ist unter Umständen der HOST_NAME() innerhalb des Triggers ein anderer als im sp_executesql-Kontext. Zudem ist es nur guter Programmierstil wenn eine Funktion, die in einem Scope mehrmals aufgerufen wird über eine Variable gesteuert wird.

	SET @ColumnName = ''
	SET @Hostname = HOST_NAME()
	SET @ColumnList = ''
	SET @Column = ''

Wir überführen die Tabellen inserted und deleted in Temporäre Tabellen, da diese speziellen Tabellen dynamischem SQL nicht zur Verfügung stehen. Das hat – wie oben schon erwähnt – damit zu tun, das mit Aufruf von dynamischem SQL – sp_executesql – ein neuer Scope beginnt und damit der Aufruf aus einem neuen Kontext heraus geschieht, in dem diese Tabellen dann (nachvollziehbarerweise) nicht mehr erreichbar sind. Um das zu vermeiden und die Daten dennoch in einem dynamisch generierten SQL-Statement vorliegen zu haben, nutzen wir temporäre Tabellen für diese beiden Tabellen, die nur für die Dauer der Ausführung dieses Triggers existieren.

	SELECT * INTO #Inserted FROM inserted
	SELECT * INTO #Deleted FROM deleted

Jetzt brauchen wir noch die Tabellendefinition der überwachten Tabelle. Hierfür gehen wir die syscolumns (in Verbindung mit der sysobjects um auf eine bestimmte Tabelle zu filtern) durch und schreiben die Spaltennamen semikolon-separiert in eine Variable. Um die gleiche Spaltenordnung wie in den Tabellen zu erhalten, sortieren wir auch noch nach colid.

	--generate list of columns
	SELECT 
		@ColumnList = @ColumnList + c.name +';' 
	FROM 
		syscolumns c 
		INNER JOIN sysobjects o ON o.id = c.id 
	WHERE 
		o.name = @TableName
	ORDER BY 
		colid

Das letzte „;“ wird abgeschnitten.

	SET @ColumnList = SUBSTRING(@ColumnList, 1, DATALENGTH(@ColumnList)-1)

Dann gehen wir diese Liste Element für Element durch. Solange wie noch irgendwas in der Liste drin steht.

	--step through list of columns
	WHILE DATALENGTH(@ColumnList) > 0
		BEGIN

Wir extrahieren einen Spaltennamen, also den Eintrag in der ColumnList von Zeichen 1 bis zum ersten „;“. Weiter unten dann werden wir dieses Element aus der Liste löschen und so die Liste Schritt für Schritt durchgehen.

			SET @ColumnName = SUBSTRING(@ColumnList, 1, CHARINDEX(';', @ColumnList) - 1)

Dann bauen wir ein dynamischen SQL-String zusammen, der eventuelle Änderungen in die Audit-Tabel schreibt. Ich habe hier drei zusätzliche Informationen mitgeschrieben:
1. Operation: enthält Informationen darüber was für eine Operation einer Änderung zugrunde liegt, also „Update“, „Insert“ oder „Delete“
2. SourceUser: enthält den Usernamen wie oben erklärt, also faktisch HOST_NAME()
3. AuditDate ist einfach eine Zeitstempel der mit GETDATE() erfasst wird.

In dem aktuellen Fall war es so, dass die zu überwachenden Tabellen mit einer rowguid ausgestattet waren, was ich hierbei auch mal als Voraussetzung definiere, denn hierdurch lässt sich ein Vergleich einfacher und übersichtlicher erstellen. Man kann statt einer für alle Tabellen existenten rowguid natürlich auch id nehmen oder einen beliebigen anderen in allen Tabellen vorhandene Spaltennamen mit einer eindeutigen Kennung der Datensätze. Auch kann man die obige Abfrage der Tabellendefinition dahingehend erweitern, dass man sich die Primary-Keys ausgeben lässt. Dann kann man auch für alle Tabellen unterschiedliche Primärschlüsselspalten in das dynamische SQL „fummeln“.

Nun werden wir noch die temporären Tabellen #Inserted und #Deleted über die rowguid (oder einen anderen eindeutigen Schlüssel) miteinander JOINen und mit der WHERE-Bedingung nur die Datensätze rausfiltern in der die Inhalte der aktuell untersuchten Spalte unterschiedlich sind. Da wir ja alle Spalten der Ausgangstabelle durchgehen, untersuchen wir mit dieser Loop die komplette Tabelle Spaltenweise nach Änderungen ab. Wir sind mit dem in dem dynamischen SQL generierten Statement auch Mengenorientiert, das heißt dass wir selbstverständlich auch Änderungen korrekt mitschreiben die im Batch ausgeführt wurden und demzufolge mehrere Änderungen gleichzeitig pro Datensatz oder auch an mehreren Datensätzen enthalten.

			SET @SQL = ''
			SET @SQL = @SQL + 'INSERT INTO dbo_scm.' + @TableName + '_Audit '
			SET @SQL = @SQL + '( '
			SET @SQL = @SQL + 'Operation, '
			SET @SQL = @SQL + 'SourceUser, '
			SET @SQL = @SQL + 'AuditDate, '
			SET @SQL = @SQL + 'rowguid, '
			SET @SQL = @SQL + 'ColumnName, '
			SET @SQL = @SQL + 'DeletedValue, '
			SET @SQL = @SQL + 'InsertedValue '
			SET @SQL = @SQL + ') '
			SET @SQL = @SQL + 'SELECT '
			SET @SQL = @SQL + '''Update'', '
			SET @SQL = @SQL + '''' + @Hostname + ''', '
			SET @SQL = @SQL + 'GETDATE(), '
			SET @SQL = @SQL + '#Inserted.rowguid, '

Vielleicht fragen Sie sich, warum der ursprüngliche Wert und der neue Wert als NVARCHAR konvertiert wird. Wir legen die Änderungen als reine Strings ab und beachten in diesem Beispiel keine Datentypen. Das hat damit zu tun, dass eine Audit-Table Informationen bereitstellt und somit in diesem Fall keine explizite Typisierung braucht. Auch wenn das schnell herstellbar ist (über die sys-Tabellen kommt man auch an die Datentypen) habe ich darauf verzichtet. Die Konvertierung zu NVARCHAR wird daher gemacht um keine Typisierungsfehler heraufzubeschwören. Spätestens bei Typ money würde dieses Statement ohne die Konvertierung aussteigen.

			SET @SQL = @SQL + '''' + @ColumnName + ''', '
			SET @SQL = @SQL + 'CONVERT(NVARCHAR(255), #Deleted.[' + @ColumnName + ']), '
			SET @SQL = @SQL + 'CONVERT(NVARCHAR(255), #Inserted.[' + @ColumnName + ']) '
			SET @SQL = @SQL + 'FROM '
			SET @SQL = @SQL + '#Inserted '
			SET @SQL = @SQL + 'INNER JOIN #Deleted ON #Inserted.rowguid = #Deleted.rowguid '
			SET @SQL = @SQL + 'WHERE '
			SET @SQL = @SQL + 'NOT #Inserted.[' + @ColumnName + '] = #Deleted.[' + @ColumnName + ']'

Statement ausführen.

			EXEC sp_executesql @SQL

Und Spalte aus der Liste entfernen.

			SET @ColumnList = SUBSTRING(@ColumnList, CHARINDEX(';', @ColumnList) + 1, DATALENGTH(@ColumnList))
		END

Fertig. Ich finde es eine schöne Lösung und vor allem gibt es einem alle Informationen die man für einen Audit von Tabellen braucht. Es lässt sich natürlich auch beliebig erweitern. Das Grundgerüst ist aber meines Erachtens schon recht mächtig und damit sicherlich auch als Ausgangspunkt für weitere Anpassungen bestens geeignet.

Auswertung
Übrigens, auch hinsichtlich einer Auswertung oder Darstellung der Datensätze hat diese Art der Audit-Tabellen einen Vorteil. Wenn man bsw. ein Access-Frontend über eine ADP realisiert, dann kann man die Audit-Tabellen direkt und ohne viel Umschweife als Continuous Form anzeigen lassen und Access bringt schon alle Bordmittel mit um sämtliche Audit-Trails nachzugehen: Filtern nach User, Operation, Datum, Tabellen, Spalten, Werten uns so weiter.

Hier geht’s zum Download des Scripts.