<\/p>\n
Wer seine Access-Tabellen von einem Access-Backend in ein SQL Server-Backend übertragen hat, dürfte zunächst keinen Unterschied beim Zugriff auf die Daten bemerken. Spannend wird es, wenn Sie unter Access jedoch die sogenannten Datenmakros verwendet haben, um automatisch auf Änderungen in den Daten zu reagieren und beispielsweise Kopien geänderter oder gelöschter Datensätze in einer Archivtabelle angelegt haben. Beim Migrieren nach SQL Server werden zwar auch die Archivtabellen erstellt, aber die Datenmakros bleiben außen vor. Damit keine Daten verloren gehen, zeigen wir in diesem Beitrag, wie Sie die Tabellen mit Triggern ausstatten, um die gewünschten Daten zu archivieren.<\/b><\/p>\n
Voraussetzungen<\/b><\/p>\n
Für den Zugriff auf den SQL Server und speziell für das Ändern des Datenmodells, zu dem auch die Trigger gehören, kann man das SQL Server Management Studio verwenden. Wir nutzen zwar gern die Tools, die wir im Beitrag SQL Server-Tools <\/b>vorgestellt haben (siehe www.access-im-unternehmen.de\/1061<\/b>) – aber im Rahmen des vorliegenden Beitrags wollen wir die Aufgabe zunächst im SQL Server Management Studio erledigen. Später erfahren Sie, wie wir dann doch noch unsere Tools zum Anlegen von Triggern und Archivtabellen nutzen können.<\/p>\n
Ziel des Beitrags<\/b><\/p>\n
Das Ziel ist, für die Tabellen, für die es notwendig ist, Trigger zu definieren, die beim Löschen oder Ändern von Datensätzen einer Tabelle die zuletzt verwendete Version des jeweiligen Datensatzes zu archivieren. Wenn Sie also einen Datensatz löschen, soll dieser vor dem Löschen in eine als Archivtabelle angelegte Kopie der Tabelle mit dem Originaldatensatz kopiert werden. Wenn ein Datensatz geändert wird, soll ebenfalls eine Kopie des Originaldatensatzes in der Archivtabelle gespeichert werden.<\/p>\n
In beiden Fällen stellt sich die Frage: Welche Daten speichern wir zusätzlich zu den zu archivierenden Daten in der Archivtabelle und wie sieht diese dann aus Wir wollen die Kopien geänderter Datensätze auf jeden Fall den neuen Versionen der Datensätze zuordnen können. Daher sollte die Tabelle mit den archivierten Datensätzen ein Feld zum Aufnehmen des Primärschlüsselwertes des Originaldatensatzes enthalten. Außerdem wollen wir noch speichern, wann der Datensatz geändert oder gelöscht wurde und natürlich, ob er nun geändert oder gelöscht wurde. Da es mehrere Versionen des gleichen Datensatzes in der Archivtabelle geben kann, können wir also nicht das Primärschlüsselwert des Originaldatensatzes als Primärschlüsselwert des archivierten Datensatzes verwenden, sondern müssen für die Archivtabelle ein eigenes Primärschlüsselfeld vorsehen.<\/p>\n
Wir benötigen also alle Felder der Originaltabelle, ein neues Primärschlüsselfeld sowie jeweils ein Feld für die Angabe des Datums und eines für die Aktion, welche für das Archivieren des Datensatzes verantwortlich ist – also Löschen oder Bearbeiten.<\/p>\n
Das Primärschlüsselfeld wollen wir immer ArchivID <\/b>nennen und die beiden anderen Felder sollen Archivdatum <\/b>und Archivart <\/b>heißen.<\/p>\n
Aber ist das die beste Lösung Wir können alternativ auch zwei Felder hinzufügen, die Loeschdatum <\/b>und Aenderungsdatum <\/b>heißen und je nach Vorgang das eine oder andere Feld füllen. Wir entscheiden uns für die zuletzt vorgeschlagene Variante und wollen diese beiden Felder zu den Archivtabellen hinzufügen. Die Archivtabellen müssen wir natürlich auch noch entsprechend benennen. In diesem Fall wollen wir einfach den Namen der zu archivierenden Tabelle nutzen und dieser das Suffix _Archiv <\/b>anhängen. Für die Tabelle tblKunden <\/b>entsteht so beispielsweise die Tabelle tblKunden_Archiv<\/b>.<\/p>\n
Archivtabelle im SQL Server Management Studio anlegen<\/b><\/p>\n
In Visual Studio legen wir die gewünschte Tabelle am schnellsten wie folgt an:<\/p>\n
![\"Archivtabelle](\"..\/fileadmin\/_temp_\/2019_06\/pic_1211_001.png\")
<\/p>\n
Bild 1: Archivtabelle anlegen<\/span><\/b><\/p>\n<\/ul>\n Daraufhin erscheint ein neues Abfragefenster, das den SQL-Code zum Erstellen der Tabelle anzeigt, hier tblKunden<\/b>. Hier nehmen Sie einige Änderungen vor. Die erste ist, dass wir den Tabellennamen in tblKunden_Archiv <\/b>ändern. Danach fügen wir das Feld ArchivID <\/b>als neuen Primärschlüsselwert hinzu und gestalten das Feld KundeID <\/b>als normales Integer<\/b>-Feld. Außerdem legen wir am Ende noch die beiden Felder Loeschdatum <\/b>und Aenderungsdatum <\/b>an, jeweils mit dem Datentyp DATETIME<\/b>. Außerdem ändern wir noch den Namen des Primärschlüsselfeldes für den CONSTRAINT<\/b>-Abschnitt. Der SQL-Code sieht dann in gekürzter Form wie folgt aus:<\/p>\n <\/p>\n Nur diesen Teil wollen wir dann auch ausführen. Löschen Sie also den Rest des automatisch erzeugten Skriptes oder markieren Sie nur diesen Teil und führen Sie diesen mit F5 <\/b>aus.<\/p>\n Nach einer Aktualisierung des Knotens Tabellen <\/b>im SQL Server Management Studio erscheint die Tabelle tblKunden_Archiv <\/b>dann in der Liste der Tabellen und der Entwurf sieht wie in Bild 2 aus.<\/p>\n Bild 2: Entwurf der neuen Archivtabelle<\/span><\/b><\/p>\n Trigger im SQL Server Management Studio anlegen<\/b><\/p>\n Nun wollen wir die notwendigen Trigger für die Tabelle tblKunden <\/b>anlegen, die dafür sorgen, dass ein zu löschender oder zu ändernder Datensatz vor dem jeweiligen Vorgang in die Tabelle tblKunden_Archiv <\/b>gesichert wird. Wenn man das Kontextmenü der Tabelle tblKunden <\/b>durchsieht, findet man dort keine Möglichkeit, einen Trigger anzulegen. Allerdings gibt es unterhalb des Elements tblKunden <\/b>ein weiteres Element namens Trigger<\/b>, dessen Kontextmenü den Eintrag Neuer Trigger… <\/b>liefert (siehe Bild 3).<\/p>\n Bild 3: Anlegen eines neuen Triggers<\/span><\/b><\/p>\n Trigger beim Aktualisieren<\/b><\/p>\n Dies öffnet ein neues Abfragefenster mit einer Vorlage für einen neuen Trigger. Den hier abgebildeten Code ersetzen wir durch die von uns gewünschte Version. Dabei wollen wir in einer INSERT INTO<\/b>-Abfrage die Inhalte aller Felder des aktualisierten Datensatzes der Tabelle tblKunden <\/b>in die entsprechenden Felder der Tabelle tblKunden_Archiv <\/b>schreiben. Außerdem soll das Feld Aenderungsdatum <\/b>mit dem aktuellen Datum plus Uhrzeit gefüllt werden. Dazu ändern wir die CREATE TRIGGER<\/b>-Anweisung wie folgt ab:<\/p>\n Führen Sie diese Anweisung dann mit F5 <\/b>aus, erscheint eine Erfolgsmeldung im Bereich Meldungen<\/b>. Außerdem finden Sie nach der Aktualisierung des Elements Trigger <\/b>einen neuen Eintrag unterhalb dieses Elements vor – siehe Bild 4. Bevor wir diesen ausprobieren, eine kurze Erläuterung des Triggers. Wir haben diesen mit dem Schlüsselwort AFTER UPDATE <\/b>ausgestattet, damit dieser nach Aktualisierungen an einem oder mehreren Datensätzen ausgelöst wird. Um zu diesem Zeitpunkt noch auf die Werte der Felder vor der Änderung des Datensatzes zugreifen zu können, verwenden wir im SELECT<\/b>-Abschnitt den Alias deleted <\/b>statt des Tabellennamens. Dem Feld Aenderungsdatum <\/b>weisen wir das Ergebnis der eingebauten T-SQL-Funktion GetDate <\/b>zu.<\/p>\n Bild 4: Der neue Trigger im Objekt-Explorer<\/span><\/b><\/p>\n Um den Trigger zu testen, wählen Sie aus dem Kontextmenü der Tabelle tblKunden <\/b>den Eintrag Erste 200 Datensätze bearbeiten <\/b>aus. Dann ändern wir beispielsweise den Wert des Feldes Firma <\/b>von Alfreds Futterkiste <\/b>auf Andrés Futterkiste<\/b>.<\/p>\n Öffnen wir danach die Tabelle tblKunden_Archiv<\/b> auf die gleiche Weise, erhalten wir den neuen Datensatz aus Bild 5. Hier ist im Feld Firma <\/b>noch der alte Wert enthalten. Außerdem wurde im Feld Aenderungsdatum <\/b>das aktuelle Datum plus Uhrzeit eingetragen.<\/p>\n Bild 5: Ein automatisch archivierter Datensatz<\/span><\/b><\/p>\n Trigger beim Löschen<\/b><\/p>\n Nun brauchen wir noch einen passenden Trigger für das Löschen von Datensätzen. Diesen legen wir auf die gleiche Art an. Das SQL-Skript, um diesen Trigger zu erstellen, lautet wie folgt:<\/p>\n Gegenüber dem UPDATE<\/b>-Trigger brauchen wir nur die folgenden Stellen zu ändern: den Namen des zu erstellenden Triggers (hier tblKunden_Delete<\/b>) sowie die Aktion, bei welcher der Trigger ausgelöst werden soll (AFTER DELETE<\/b>). Außerdem soll der Wert der T-SQL-Funktion GetDate <\/b>in das Feld Loeschdatum <\/b>eingetragen werden und nicht in Aenderungsdatum<\/b>.<\/p>\n Nach dem Anlegen des Triggers mit der Taste F5 <\/b>können wir diesen direkt testen. Wenn wir einen Datensatz löschen und anschließend wieder die Tabelle tblKunden_Archiv <\/b>öffnen, finden wir dort einen neuen Datensatz vor, bei dem diesmal das Feld Loeschdatum <\/b>mit einem Datum gefüllt ist (siehe Bild 6).<\/p>\n Bild 6: Archivierte Version eines gelöschten Datensatzes<\/span><\/b><\/p>\n DELETE und UPDATE in einem Trigger <\/b><\/p>\n Nun wollen wir möglichst wenige Objekte erzeugen. Dazu wollen wir prüfen, ob wir den DELETE<\/b>– und den UPDATE<\/b>-Trigger auch in einem Trigger unterbringen können.<\/p>\n Einen Trigger zu erzeugen, der auf beide Ereignisse reagiert, ist kein Problem – dazu müssen wir hinter das Schlüsselwort AFTER <\/b>nur die beiden Schlüsselwörter DELETE <\/b>und UPDATE <\/b>durch ein Komma voneinander getrennt angeben.<\/p>\n Außerdem müssen wir noch prüfen, ob der Trigger durch einen DELETE<\/b>– oder durch einen UPDATE<\/b>-Vorgang ausgelöst wurde. Um das herauszufinden, verwenden wir eine IF…ELSE<\/b>-Bedingung im Trigger. Damit prüfen wir, ob die Tabelle inserted <\/b>einen Datensatz enthält oder nicht. Das ist nur der Fall, wenn der Benutzer einen Datensatz geändert hat – falls nicht, ist die Tabelle inserted <\/b>leer. Nur die Tabelle delete <\/b>enthält in beiden Fällen einen Datensatz.<\/p>\n Die Anweisung zum Erstellen des Trigges können wir nun wie in Listing 1 definieren. Existiert der Datensatz, den die Abfrage SELECT 1 FROM inserted <\/b>liefert, führen wir die INSERT INTO<\/b>-Abfrage für das Ändern eines Datensatzes durch. Liefert die Abfrage keinen Datensatz, ist die EXISTS<\/b>-Funktion falsch und der ELSE<\/b>-Teil wird ausgeführt. In diesem Fall führen wir die INSERT INTO<\/b>-Abfrage für das Löschen eines Datensatzes aus.<\/p>\n Listing 1: Trigger zum Archivieren von Daten nach DELETE- oder UPDATE-Vorgängen<\/span><\/b><\/p>\n Der Unterschied besteht lediglich in dem Feld, das wir mit dem Wert der Funktion GetDate <\/b>füllen. Dafür eine solch lange Abfrage zwei Mal zu schreiben, ist fehleranfällig – vor allem, wenn sich der Entwurf der Tabelle ändert. Dann müssen wir die Änderungen im Trigger gleich an mehreren Stellen durchführen. Wir können auch erst die INSERT INTO<\/b>-Anweisung ausführen, ohne die Felder Loeschdatum <\/b>oder Aenderungsdatum <\/b>zu füllen und das dann nachholen:<\/p>\n Zusammenfassung und Ausblick<\/b><\/p>\n Wenn Sie wie in diesem Beitrag beschrieben eine Archivtabelle anlegen und der Originaltabelle zwei Trigger hinzufügen, um die Originaldatensätze nach dem Löschen oder Ändern in der Archivtabelle zu speichern, verlieren Sie nach dem Ändern oder Löschen keine Daten mehr. Diese können Sie dann auch auf Basis der gesicherten Daten wiederherstellen.<\/p>\n In einem weiteren Beitrag namens Datenhistorie-Trigger schnell anlegen <\/b>(www.access-im-unternehmen.de\/****<\/b>) erfahren Sie, wie Sie die hier vorgestellten Archivtabellen und Trigger per Mausklick anlegen können.<\/p>\n","protected":false},"excerpt":{"rendered":" Wer seine Access-Tabellen von einem Access-Backend in ein SQL Server-Backend übertragen hat, dürfte zunächst keinen Unterschied beim Zugriff auf die Daten bemerken. Spannend wird es, wenn Sie unter Access jedoch die sogenannten Datenmakros verwendet haben, um automatisch auf Änderungen in den Daten zu reagieren und beispielsweise Kopien geänderter oder gelöschter Datensätze in einer Archivtabelle angelegt haben. Beim Migrieren nach SQL Server werden zwar auch die Archivtabellen erstellt, aber die Datenmakros bleiben außen vor. Damit keine Daten verloren gehen, zeigen wir in diesem Beitrag, wie Sie die Tabellen mit Triggern ausstatten, um die gewünschten Daten zu archivieren.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[662019,66062019,44000022],"tags":[],"class_list":["post-55001211","post","type-post","status-publish","format-standard","hentry","category-662019","category-66062019","category-SQL_Server_und_Co"],"yoast_head":"\nCREATE TABLE [dbo].[tblKunden_Archiv](\r\n [ArchivID] [int] IDENTITY(1,1) NOT NULL,\r\n [KundeID] [int] NULL,\r\n [KundenCode] [nvarchar](5) NULL,\r\n [Firma] [nvarchar](40) NOT NULL,\r\n [AnredeID] [int] NULL,\r\n [Vorname] [nvarchar](255) NULL,\r\n [Nachname] [nvarchar](255) NULL,\r\n [Position] [nvarchar](30) NULL,\r\n [PLZ] [nvarchar](10) NULL,\r\n [Strasse] [nvarchar](255) NULL,\r\n [Ort] [nvarchar](15) NULL,\r\n [Region] [nvarchar](15) NULL,\r\n [Land] [nvarchar](15) NULL,\r\n [Telefon] [nvarchar](24) NULL,\r\n [Telefax] [nvarchar](24) NULL,\r\n [Loeschdatum] [datetime] NULL,\r\n [Aenderungsdatum] [datetime] NULL\r\n CONSTRAINT [tblKunden_Archiv$PrimaryKey] PRIMARY KEY CLUSTERED \r\n(\r\n [ArchivID] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]<\/pre>\n
![\"Entwurf](\"..\/fileadmin\/_temp_\/2019_06\/pic_1211_002.png\")
<\/p>\n![\"Anlegen](\"..\/fileadmin\/_temp_\/2019_06\/pic_1211_003.png\")
<\/p>\nCREATE TRIGGER dbo.tblKunden_Update \r\n ON dbo.tblKunden \r\n AFTER UPDATE\r\nAS \r\nBEGIN\r\nSET NOCOUNT ON;\r\nINSERT INTO dbo.tblKunden_Archiv(\r\n [KundeID], [KundenCode], [Firma], [AnredeID], \r\n [Vorname], [Nachname], [Position], [PLZ], [Strasse], \r\n [Ort], [Region], [Land], [Telefon], [Telefax], \r\n [Aenderungsdatum]\r\n)\r\nSELECT [KundeID], [KundenCode], [Firma], [AnredeID], \r\n [Vorname], [Nachname], [Position], [PLZ], [Strasse], \r\n [Ort], [Region], [Land], [Telefon], [Telefax], \r\n GetDate() FROM deleted;\r\nEND<\/pre>\n
![\"Der](\"..\/fileadmin\/_temp_\/2019_06\/pic_1211_004.png\")
<\/p>\n![\"Ein](\"..\/fileadmin\/_temp_\/2019_06\/pic_1211_005.png\")
<\/p>\nCREATE TRIGGER [dbo].[tblKunden_Delete] \r\n ON [dbo].[tblKunden] \r\n AFTER DELETE\r\nAS \r\nBEGIN\r\n SET NOCOUNT ON;\r\n INSERT INTO dbo.tblKunden_Archiv([KundeID], \r\n [KundenCode], [Firma], [AnredeID], [Vorname], \r\n [Nachname], [Position], [PLZ], [Strasse], [Ort], \r\n [Region], [Land], [Telefon], [Telefax], \r\n [Loeschdatum]) \r\n SELECT [KundeID], [KundenCode], [Firma], [AnredeID], \r\n [Vorname], [Nachname], [Position], [PLZ], \r\n [Strasse], [Ort], [Region], [Land], [Telefon], \r\n [Telefax], GetDate() FROM deleted;\r\nEND<\/pre>\n
![\"Archivierte](\"..\/fileadmin\/_temp_\/2019_06\/pic_1211_006.png\")
<\/p>\nCREATE TRIGGER [dbo].[tblKunden_DeleteUpdate] \r\n ON [dbo].[tblKunden] \r\n AFTER DELETE, UPDATE\r\nAS \r\nBEGIN\r\n SET NOCOUNT ON;\r\n IF EXISTS(SELECT 1 FROM inserted)\r\n INSERT INTO dbo.tblKunden_Archiv([KundeID], [KundenCode], [Firma], [AnredeID], [Vorname], [Nachname], \r\n [Position], [PLZ], [Strasse], [Ort], [Region], [Land], [Telefon], [Telefax], [Aenderungsdatum]) \r\n SELECT [KundeID], [KundenCode], [Firma], [AnredeID], [Vorname], [Nachname], [Position], [PLZ], [Strasse], \r\n [Ort], [Region], [Land], [Telefon], [Telefax], GetDate() FROM deleted;\r\n ELSE\r\n INSERT INTO dbo.tblKunden_Archiv([KundeID], [KundenCode], [Firma], [AnredeID], [Vorname], [Nachname], \r\n [Position], [PLZ], [Strasse], [Ort], [Region], [Land], [Telefon], [Telefax], [Loeschdatum]) \r\n SELECT [KundeID], [KundenCode], [Firma], [AnredeID], [Vorname], [Nachname], [Position], [PLZ], [Strasse], \r\n [Ort], [Region], [Land], [Telefon], [Telefax], GetDate() FROM deleted;\r\nEND<\/pre>\n
INSERT INTO dbo.tblKunden_Archiv([KundeID], [KundenCode], ...) SELECT [KundeID], [KundenCode], ... FROM deleted;\r\nIF EXISTS(SELECT 1 FROM inserted)\r\n UPDATE dbo.tblKunden_Archiv SET Aenderungsdatum = GETDATE() \r\n WHERE tblKunden_Archiv.ArchivID = @@IDENTITY\r\nELSE\r\n UPDATE dbo.tblKunden_Archiv \r\n SET Loeschdatum = GETDATE() \r\n WHERE tblKunden_Archiv.ArchivID = @@IDENTITY<\/pre>\n