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.

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

Username or Email

Passwort

Angemeldet bleiben