Datenhistorie-Trigger schnell anlegen

Im Beitrag „Datenhistorie per Trigger“ haben wir gezeigt, welche Anweisungen nötig sind, um eine Archivtabelle anzulegen und der Originaltabelle einen Trigger hinzuzufügen, der beim Ändern oder Löschen eines Datensatzes die vorherige Version in einer Archivtabelle speichert. Im vorliegenden Beitrag wollen wir eine VBA-Prozedur entwickeln, mit der Sie zu einer Tabelle mit einem einfachen Aufruf die Archivtabelle und den Trigger in einem Rutsch anlegen können. Damit sichern Sie Ihre Daten bei Änderungen auch für mehrere Tabellen ganz schnell ab.

Voraussetzungen

In diesem Beitrag kommen die bereits im Beitrag Datenhistorie per Trigger (siehe www.access-im-unternehmen.de/1211) erwähnten Access-SQL-Tools zum Einsatz. Diese haben wir wiederum bereits im Beitrag SQL Server-Tools vorgestellt (siehe www.access-im-unternehmen.de/1061).

Ziel des Beitrags

Das Ziel ist es nun, eine Prozedur zu entwickeln, der Sie nur den Namen der Tabelle übergeben, für welche eine Archivtabelle erstellt werden soll und die einen Trigger erhalten soll, der beim Ändern oder Löschen von Datensätzen aus dieser Tabelle die vorherige Version des Datensatzes in der Archivtabelle speichert.

Dazu haben wir zunächst alle Elemente der SQL Server-Tools in die Beispieldatenbank integriert.

Prozedur zum Erstellen der Archivtabelle

Im oben genannten Beitrag Datenhistorie per Trigger haben wir eine Archivtabelle mit einer SQL-Anweisung wie der folgenden erstellt:

CREATE TABLE [dbo].[tblKunden_Archiv](
     [ArchivID] [int] IDENTITY(1,1) NOT NULL,
     [KundeID] [int] NULL,
     ...
     [Loeschdatum] [datetime] NULL,
     [Aenderungsdatum] [datetime] NULL
CONSTRAINT [tblKunden_Archiv$PrimaryKey] PRIMARY KEY CLUSTERED 
(
    [ArchivID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Diese Anweisung wollen wir nun parametrisieren, damit wir diese für beliebige Tabellennamen erstellen können. Dazu benötigen wir etwas Wissen über die Tabelle, für die wir die Archivtabelle anlegen wollen. Da wir nicht davon ausgehen können, dass es eine per ODBC erstellte Verknüpfung auf diese Tabelle gibt, wollen wir die Felder und ihre Eigenschaften direkt vom SQL Server beziehen. Um die Feldnamen und die Datentypen zu ermitteln, können wir die folgende Abfrage nutzen, die wir direkt an den SQL Server absetzen:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tblKunden'

Im SQL Server sieht das Ergebnis für die Tabelle tblKunden wie in Bild 1 aus.

Ausgabe der Abfrage der Eigenschaften der Felder einer Tabelle

Bild 1: Ausgabe der Abfrage der Eigenschaften der Felder einer Tabelle

Funktion zum Einlesen der Feldliste für CREATE TABLE

Wir benötigen eine Funktion, mit der wir die in der Archivtabelle anzulegenden Felder ermitteln.

Diese heißt FeldlisteZusammenstellen und sieht wie in Listing 1 aus.

Public Function FeldlisteZusammenstellen(strTabelle As String, strVerbindungszeichenfolge As String) As String
     Dim rst As DAO.Recordset
     Dim strFeldliste As String
     Set rst = mdlToolsSQLServer.SQLRecordset("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" _
         & strTabelle & "'", strVerbindungszeichenfolge)
     strFeldliste = "    [ArchivID] [int] IDENTITY(1,1) NOT NULL," & vbCrLf
     Do While Not rst.EOF
         If Not rst!DATA_TYPE = "Timestamp" Then
             strFeldliste = strFeldliste & "    [" & rst!COLUMN_NAME & "]"
             strFeldliste = strFeldliste & " [" & rst!DATA_TYPE & "]"
             If Not IsNull(rst!Character_maximum_Length) Then
                 If Not rst!Character_maximum_Length = -1 Then
                     strFeldliste = strFeldliste & "(" & rst!Character_maximum_Length & ")"
                 Else
                     strFeldliste = strFeldliste & "(max)"
                 End If
             End If
             If rst!IS_NULLABLE = "YES" Then
                 strFeldliste = strFeldliste & " NULL"
             Else
                 strFeldliste = strFeldliste & " NOT NULL"
             End If
             strFeldliste = strFeldliste & "," & vbCrLf
         End If
         rst.MoveNext
     Loop
     strFeldliste = strFeldliste & "    [Loeschdatum] [datetime] NULL," & vbCrLf
     strFeldliste = strFeldliste & "    [Aenderungsdatum] [datetime] NULL"
     FeldlisteZusammenstellen = strFeldliste
End Function

Listing 1: Funktion zum Ermitteln der Feldliste

Die Funktion nimmt den Namen der zu untersuchenden Tabelle sowie die Verbindungszeichenfolge der SQL Server-Datenbank als Parameter entgegen und liefert einen String als Ergebnis zurück. Die Funktion verwendet die Funktion SQLRecordset des Moduls mdlToolsSQLServer, um die Datensätze der Tabelle INFORMATION_SCHEMA.COLUMNS zurückzuliefern.

Dabei wollen wir nach dem Feld TABLE_NAME filtern, das die Datensätze auf diejenigen einschränken soll, deren Tabellenname dem Wert des Parameters strTabelle entspricht. Auch diese Funktion nimmt wieder die Verbindungszeichenfolge als Parameter entgegen.

Danach stellt die Funktion FeldlisteZusammenstellen in der Variablen strFeldliste die Liste der anzulegenden Felder für die Archivtabelle zusammen. Bevor wir die Datensätze des Recordsets rst durchlaufen, fügen wir dort bereits als ersten Eintrag den folgenden hinzu:

[ArchivID] [int] IDENTITY(1,1) NOT NULL,

Dies entspricht dem Primärschlüsselfeld für die Archivtabelle. Das Komma am Ende deutet bereits an, dass nun weitere Felder folgen. Diese stellen wir in einer Do While-Schleife über alle Datensätze der Tabelle zusammen. Ein Feld wird nur hinzugefügt, wenn es nicht den Datentyp Timestamp hat, was wir in einer If…Then-Bedingung prüfen.

Ist das nicht der Fall, berücksichtigen wir als Erstes den Inhalt des Feldes COLUMN_NAME und damit den Feldnamen des anzulegenden Feldes, den wir in eckige Klammern einfassen.

Dann folgt der Datentyp, den wir aus dem Feld DATA_TYPE des Recordsets entnehmen und den wir durch ein Leerzeichen vom Feldnamen getrennt ebenfalls in eckige Klammern einfassen. Dann prüfen wir, ob das Feld CHAR-ACTER_MAXIMUM_LENGTH einen Wert enthält. Dieses liefert die Anzahl der Zeichen für Textfelder. Liegt ein Wert vor, fügen wir diesen in runde Klammern eingefasst zum Ausdruck in strFeldListe ein. Aber es kann auch sein, dass der Wert -1 beträgt. Dieser tritt auf, wenn der eigentliche Wert max lautet, was auf ein langes Textfeld hinweist. In diesem Fall wollen wir nicht -1, sondern max in runden Klammern zur Felddefinition hinzufügen.

Schließlich folgt das Feld ISNULLABLE, das mit den Werten YES und NO angibt, ob das Feld Nullwerte aufnehmen darf oder nicht. Im Falle von YES fügen wir NULL zur Felddefinition hinzu, anderenfalls NOT NULL. Anschließend hängen wir noch ein abschließendes Komma an die Beschreibung dieses Feldes an und gehen dann in die nächste Runde der Do While-Schleife.

Sind alle Felder durchlaufen, fehlen noch die beiden Felder Loeschdatum und Aenderungsdatum, die beide mit dem Datentyp datetime angelegt werden sollen und Nullwerte enthalten dürfen.

Jeder Felddefinition fügen wir außerdem noch einen Zeilenumbruch hinzu, sodass das Ergebnis der Funktion beispielsweise für die Tabelle tblKunden wie folgt aussieht:

    [ArchivID] [int] IDENTITY(1,1) NOT NULL,
     [KundeID] [int] NOT NULL,
     [KundenCode] [nvarchar](5) NULL,
     [Firma] [nvarchar](40) NOT NULL,
     [AnredeID] [int] NULL,
     [Vorname] [nvarchar](255) NULL,
     [Nachname] [nvarchar](255) NULL,
     [Position] [nvarchar](30) NULL,
     [PLZ] [nvarchar](10) NULL,
     [Strasse] [nvarchar](255) NULL,
     [Ort] [nvarchar](15) NULL,
     [Region] [nvarchar](15) NULL,
     [Land] [nvarchar](15) NULL,
     [Telefon] [nvarchar](24) NULL,
     [Telefax] [nvarchar](24) NULL,
     [Loeschdatum] [datetime] NULL,
     [Aenderungsdatum] [datetime] NULL

Diese Zeichenkette wird dann als Ergebnis der Funktion zurückgegeben. Den Aufruf der Funktion zum Testen können Sie so gestalten:

Public Sub FieldlisteCreate_Test()
     Dim strVerbindungszeichenfolge As String
     strVerbindungszeichenfolge =  VerbindungszeichenfolgeNachID(10)
     Debug.Print FieldlistCreate("tblKunden",  strVerbindungszeichenfolge)
End Sub

Hier nutzen wir die Funktion VerbindungszeichenfolgeNachID, um die Verbindungszeichenfolge zu ermitteln. Die SQL Server-Tools enthalten eine Tabelle namens tblVerbindungszeichenfolgen, welche die Informationen für die Verbindungszeichenfolgen speichert.

Mit dem Formular frmVerbindungszeichenfolgen können Sie die Verbindungszeichenfolge zusammenstellen und auch testen. Merken Sie sich die ID der dabei gespeicherten Verbindungszeichenfolge und übergeben Sie diese der Funktion VerbindungszeichenfolgeNachID als Parameter.

Funktion CreateTableZusammenstellen

Die Funktion aus Listing 2 stellt die aus der Funktion FeldlisteZusammenstellen ermittelten Felder mit einigen weiteren Zeilen zu der kompletten CREATE TABLE-Anweisung zusammen. Diese Funktion erwartet die gleichen Parameter wie die Funktion FeldlisteZusammenstellen.

Public Function CreateTableZusammenstellen(strTabelle As String, strVerbindungszeichenfolge As String) As String
     Dim strCreateTable As String
     strCreateTable = "CREATE TABLE [dbo].[" & strTabelle & "_Archiv](" & vbCrLf
     strCreateTable = strCreateTable & FeldlisteZusammenstellen(strTabelle, strVerbindungszeichenfolge) & vbCrLf
     strCreateTable = strCreateTable & "CONSTRAINT [" & strTabelle & "_Archiv$PrimaryKey] PRIMARY KEY CLUSTERED" _
         & vbCrLf
     strCreateTable = strCreateTable & "(" & vbCrLf
     strCreateTable = strCreateTable & "    [ArchivID] Asc" & vbCrLf
     strCreateTable = strCreateTable & ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, " _
         & "ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" & vbCrLf
     strCreateTable = strCreateTable & ") ON [PRIMARY]"
     CreateTableZusammenstellen = strCreateTable
End Function

Listing 2: Funktion zum Ermitteln der Definition der Archivtabelle

Archivtabelle erstellen

Mit dem Ergebnis können wir nun bereits die Archivtabelle erstellen. Während wir die hier zusammengestellte CREATE TABLE-Anweisung normalerweise im SQL Server Management Studio ausführen würden, wollen wir dies auch von Access aus erledigen. Dazu liefern die SQL Server Tools auch eine Funktion. Diese heißt SQLAktionsabfrageOhneErgebnis und erwartet die auszuführende Abfrage sowie die Verbindungszeichenfolge als Parameter und einen optionalen Parameter für die Rückgabe einer eventuellen Fehlermeldung.

Diese Funktion rufen wir von einer anderen Funktion namens ArchivtabelleErstellen auf, die Sie in Listing 3 finden. Diese ermittelt zunächst die Verbindungszeichenfolge und mit der Funktion CreateTableZusammenstellen die SQL-Anweisung zum Erstellen der Archivtabelle. Dann ruft sie die Funktion SQLAktionsabfrageOhneErgebnis auf und speichert das Ergebnis in der Variablen lngErgebnis. Außerdem verwendet sie als Rückgabeparameter die Variable strFehlermeldung. Liefert SQLAktionsabfrageOhneErgebnis einen Wert ungleich 0, können Sie der Variablen strFehlermeldung den Text der Meldung des aufgetretenen Fehlers entnehmen.

Public Function ArchivtabelleErstellen() As Boolean
     Dim strTabelle As String
     Dim strCreateTable As String
     Dim strVerbindungszeichenfolge As String
     Dim strFehlermeldung As String
     Dim lngErgebnis As Long
     strTabelle = "tblKunden"
     strVerbindungszeichenfolge = VerbindungszeichenfolgeNachID(10)
     strCreateTable = CREATETABLEZusammenstellen(strTabelle, strVerbindungszeichenfolge)
     lngErgebnis = SQLAktionsabfrageOhneErgebnis(strCreateTable, strVerbindungszeichenfolge, strFehlermeldung)
     If Not lngErgebnis = 0 Then
         Debug.Print lngErgebnis, strFehlermeldung
     Else
         ArchivtabelleErstellen = True
     End If
End Function

Listing 3: Funktion zum Anlegen der Archivtabelle

Ob das Ausführen der Abfrage erfolgreich war, prüfen wir in einer If…Then-Bedingung, in der wir gegebenenfalls die Fehlermeldung im Direktfenster ausgeben. Außerdem wird der Rückgabewert der Funktion ArchivtabelleErstellen nur dann auf True eingestellt, wenn kein Fehler aufgetreten ist. Ein Fehler tritt beispielsweise auf, wenn wir eine Archivtabelle erstellen wollen, die bereits vorhanden ist. Die Meldung lautet dann:

[Microsoft][SQL Server Native Client 11.0][SQL Server]There is already an object named 'tblKunden_Archiv' in the database.

Prozedur zum Erstellen des Triggers

Nun wollen wir die Funktion zum Erstellen des Triggers zusammenstellen. Der Trigger soll, am Beispiel der Tabelle tblKunden, gekürzt wie folgt aussehen:

CREATE TRIGGER [dbo].[tblKunden_DeleteUpdate] 
     ON  [dbo].[tblKunden] 
     AFTER DELETE, UPDATE
AS 
BEGIN
     SET NOCOUNT ON;
     INSERT INTO dbo.tblKunden_Archiv([KundeID], 
         [KundenCode], ...) SELECT [KundeID], 
         [KundenCode], ... FROM deleted;
     IF EXISTS(SELECT 1 FROM inserted)
         UPDATE dbo.tblKunden_Archiv 
             SET Aenderungsdatum = GETDATE() 
             WHERE tblKunden_Archiv.ArchivID = @@IDENTITY
     ELSE
         UPDATE dbo.tblKunden_Archiv 
             SET Loeschdatum = GETDATE() 
             WHERE tblKunden_Archiv.ArchivID = @@IDENTITY
END

Dazu benötigen wir zunächst einmal wieder eine Liste aller Felder der Tabelle. Diese lesen wir mithilfe der Funktion FeldlisteTriggerZusammenstellen aus Listing 4 ein. Die Funktion arbeitet grundsätzlich wie die bereits weiter oben vorgestellte Funktion FeldlisteZusammenstellen, mit der wir die Felddefinitionen für die CREATE TABLE-Anweisung zusammengestellt haben. In diesem Fall stellen wir allerdings lediglich eine kommaseparierte Liste aller Felder der per Parameter übergebenen Tabelle zusammen. Die Feldnamen sollen dabei wieder in eckige Klammern eingefasst werden. Nach dem Durchlaufen aller Datensätze des Recordsets mit den Feldern der zu untersuchenden Tabelle, bei dem wir jedem Feld ein Komma vorangestellt haben, wird das Komma vor dem ersten Feld entfernt. Das Ergebnis dieser Funktion lautet am Beispiel der Tabelle tblKunden wie folgt:

Public Function FeldlisteTriggerZusammenstellen(strTabelle As String, strVerbindungszeichenfolge As String) As String
     Dim rst As DAO.Recordset
     Dim strFeldliste As String
     Set rst = mdlToolsSQLServer.SQLRecordset("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" _
         & strTabelle & "'", strVerbindungszeichenfolge)
     Do While Not rst.EOF
         If Not rst!DATA_TYPE = "timestamp" Then
             strFeldliste = strFeldliste & ", [" & rst!COLUMN_NAME & "]"
         End If
         rst.MoveNext
     Loop
     strFeldliste = Mid(strFeldliste, 3)
     FeldlisteTriggerZusammenstellen = strFeldliste
End Function

Listing 4: Funktion zum Zusammenstellen der Feldliste für den Trigger

[KundeID], [KundenCode], [Firma], [AnredeID], [Vorname], [Nachname], [Position], [PLZ], [Strasse], [Ort], [Region], [Land], [Telefon], [Telefax]

Diese Funktion können Sie mit der folgenden Prozedur testen:

Public Function FeldlisteTriggerZusammenstellen_Test()
     Dim strVerbindungszeichenfolge As String
     strVerbindungszeichenfolge =  VerbindungszeichenfolgeNachID(10)
     Debug.Print FeldlisteTriggerZusammenstellen( "tblKunden", strVerbindungszeichenfolge)
End Function

Funktion zum Zusammenstellen der CREATE TRIGGER-Anweisung

Die Funktion CREATETRIGGERZusammenstellen aus Listing 5 stellt die CREATE TRIGGER-Anweisung zusammen. Dabei erwartet sie wie die anderen Funktionen den Namen der zu verwendenden Tabelle sowie die Verbindungszeichenfolge als Parameter.

Public Function CREATETRIGGERZusammenstellen(strTabelle As String, strVerbindungszeichenfolge As String)
     Dim strCreateTrigger As String
     Dim strFeldliste As String
     strFeldliste = FeldlisteTriggerZusammenstellen(strTabelle, strVerbindungszeichenfolge)
     strCreateTrigger = "CREATE TRIGGER [dbo].[" & strTabelle & "_DeleteUpdate]" & vbCrLf
     strCreateTrigger = strCreateTrigger & "    ON  [dbo].[" & strTabelle & "]" & vbCrLf
     strCreateTrigger = strCreateTrigger & "    AFTER Delete, Update" & vbCrLf
     strCreateTrigger = strCreateTrigger & "AS" & vbCrLf
     strCreateTrigger = strCreateTrigger & "BEGIN" & vbCrLf
     strCreateTrigger = strCreateTrigger & "    SET NOCOUNT ON;" & vbCrLf
     strCreateTrigger = strCreateTrigger & "    INSERT INTO dbo." & strTabelle & "_Archiv(" & strFeldliste _
         & ") SELECT " & strFeldliste & " FROM deleted;" & vbCrLf
     strCreateTrigger = strCreateTrigger & "    IF EXISTS(SELECT 1 FROM inserted)" & vbCrLf
     strCreateTrigger = strCreateTrigger & "        UPDATE dbo." & strTabelle & "_Archiv SET Aenderungsdatum = " _
         & "GETDATE() WHERE " & strTabelle & "_Archiv.ArchivID = @@IDENTITY" & vbCrLf
     strCreateTrigger = strCreateTrigger & "    Else" & vbCrLf
     strCreateTrigger = strCreateTrigger & "        UPDATE dbo." & strTabelle & "_Archiv SET Loeschdatum = " _
         & "GETDATE() WHERE " & strTabelle & "_Archiv.ArchivID = @@IDENTITY" & vbCrLf
     strCreateTrigger = strCreateTrigger & "End" & vbCrLf
     CREATETRIGGERZusammenstellen = strCreateTrigger
End Function

Listing 5: Funktion zum Zusammenstellen der Abfrage zum Zusammenstellen der CREATE TRIGGER-Anweisung

Dann ermittelt sie zunächst mit der Funktion Feldliste-TriggerZusammenstellen die Feldliste für den Trigger.

Dann stellt sie in der Variablen strCreateTrigger die übrigen Zeilen des Triggers zusammen. Dabei fügt sie an einigen Stellen den Namen der Tabelle aus strTabelle ein.

Den Trigger erstellen wir dann mit der Funktion aus Listing 6. Diese arbeitet ähnlich wie die Funktion zum Anlegen der Archivtabelle und liefert wieder eine Fehlermeldung, wenn das Anlegen des Triggers nicht funktioniert hat.

Public Function TriggerErstellen()
     Dim strTabelle As String
     Dim strCreateTrigger As String
     Dim strVerbindungszeichenfolge As String
     Dim strFehlermeldung As String
     Dim lngErgebnis As Long
     strTabelle = "tblKunden"
     strVerbindungszeichenfolge = VerbindungszeichenfolgeNachID(10)
     strCreateTrigger = CREATETRIGGERZusammenstellen(strTabelle, strVerbindungszeichenfolge)
     lngErgebnis = SQLAktionsabfrageOhneErgebnis(strCreateTrigger, strVerbindungszeichenfolge, strFehlermeldung)
     If Not lngErgebnis = 0 Then
         Debug.Print lngErgebnis, strFehlermeldung
     Else
         TriggerErstellen = True
     End If
End Function

Listing 6: Funktion zum Anlegen des Triggers, der beim Aktualisieren oder Löschen eines Datensatzes ausgelöst werden soll.

Das kann beispielsweise geschehen, wenn bereits ein gleichnamiger Trigger vorhanden ist. Sollte dies bei Ihnen vorkommen, können Sie folgendes Skript im Formular frmSQLBefehle ausführen, um die vorhandenen Trigger aufzulisten:

SELECT  
     name,
     is_instead_of_trigger
FROM 
     sys.triggers  
WHERE 
     type = 'TR';

Mit der DROP-Anweisung können Sie den Trigger dann löschen:

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo

Schreibe einen Kommentar