Updates Nachverfolgung

Wir alle kennen die Problematik nach einem Update auf Tabellen herauszufinden, welche Inhalte sich geändert haben.

Hier bietet der SQL Server eine Funktionalität, mit der man rein Datenbankseitig in die Lage versetzt wird, solche Änderungen automatisiert zu verfolgen. Mit einem Trigger auf einzelne Spalten einer Tabelle lässt sich dies realisieren.

Dazu legen wir eine Tabelle „Adresse“ an mit wesentlichen Attributen zu einer Person an. Zusätzlich enthält die Tabelle eine Spalte für ein Änderungsdatum und einer Spalte in der der Name der geänderten Spalte bei einem Update gespeichert wird.

CREATE TABLE [dbo].[Adresse](
    [ID_Adresse] [int] NULL,
    [Name] [varchar](50) NULL,
    [Vorname] [varchar](50) NULL,
    [Strasse] [varchar](50) NULL,
    [PLZ] [char](5) NULL,
    [Ort] [varchar](50) NULL,
    [Aender_Datum] [datetime] NULL,
    [Aender_Spalte] [varchar](50) NULL
) ON [PRIMARY]
Create TRIGGER [dbo].[Update_Check_Adresse] 
   ON  [dbo].[Adresse]
   AFTER UPDATE
AS 
BEGIN
    -- Erste Spalte ist 1 in der Bitmaske, Zweite 2, Dritte 4, Vierte 8 usw. bis erstes Byte voll
    -- Spalte 3,4,5,6,7,8 ergibt dann Dezimal 252 -> > 0 bedeutet eine der Spalten = 252 wäre alle Spalten
    -- wird dann mit Substring ausgewertet, zweites Byte ist dann Substring(,2,1)

    SET NOCOUNT ON;
    If  (Substring(columns_Updated(),1,1) & 62 > 0) 
    update Adresse set [Aender_Datum] = Getdate(),
    [Aender_Spalte] = 
    case 
    when columns_Updated() & 2 = 2 then 'Name' 
    when columns_Updated() & 4 = 4 then 'Vorname' 
    when columns_Updated() & 8 = 8 then 'Strasse' 
    when columns_Updated() & 16 = 16 then 'PLZ' 
    when columns_Updated() & 32 = 32 then 'Ort'  ELSE 'Other' END
    --hiermit findet man raus, welche ID geupdated wurde (die wird in die Deleted-Tabelle geschrieben)
    where ID_Adresse in (Select ID_Adresse from deleted) 

END

Der Trigger auf die Tabelle Adresse basiert im Wesentlichen auf der SQL Server Funktion columns_updated() und der internen SQL Server Tabelle deleted.

Die Funktion liefert eine varbinary Bitmaske zurück, in der das jeweilige Bit der veränderten Spalten auf 1 gesetzt ist. Der SQL Server speichert bei einem Update die ursprünglichen Datensätze in eine interne Tabelle deleted. Sobald die Änderung des Datensatzes erfolgreich durchgeführt wurde, wird die Tabelle verworfen und ein Commit ausgelöst.

Mit diesen beiden Funktionalitäten kann man nun ermitteln, welche Spalte verändert wurde, und diese Information an die betreffende Zeile anfügen.

/****** Object:  Table [dbo].[Adresse]    Script Date: 08/22/2013 13:02:07 ******/
INSERT [dbo].[Adresse] ([ID_Adresse], [Name], [Vorname], [Strasse], [PLZ], [Ort], [Aender_Datum], [Aender_Spalte]) VALUES (1, N'Maier', N'Hans', N'Berliner Str.', N'12345', N'Musterstadt', NULL, NULL)
INSERT [dbo].[Adresse] ([ID_Adresse], [Name], [Vorname], [Strasse], [PLZ], [Ort], [Aender_Datum], [Aender_Spalte]) VALUES (2, N'Müller', N'Otto', N'Frankfurter Ring', N'67892', N'Frankfurt', NULL, NULL)
INSERT [dbo].[Adresse] ([ID_Adresse], [Name], [Vorname], [Strasse], [PLZ], [Ort], [Aender_Datum], [Aender_Spalte]) VALUES (3, N'Schmitt', N'Peter', N'Odeonsplatz', N'81234', N'München', NULL, NULL)

UPDATE [dbo].[Adresse] SET [Strasse] = 'Musterstrasse' WHERE [ID_Adresse] = 1

Als Ergebnis liefert der SQL Server zurück:

Updates_Nachverfolgung

Weitere spannende Einsatzmöglichkeiten und Ausbaustufen wären beispielsweise Logbücher, also Tabellen in denen die gesamte Änderungshistorie von Tabelleninhalten abgebildet werden kann.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Captcha * Time limit is exhausted. Please reload CAPTCHA.