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
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