Datensatzhistorie mit dem SQL Server 2000
Werbung
combit GmbH Untere Laube 30 78462 Konstanz Whitepaper Produkt: combit Relationship Manager Datensatzhistorie mit dem SQL Server 2000 © Copyright combit GmbH, Alle Rechte vorbehalten. Produkt: combit Relationship Manager Datensatzhistorie mit dem SQL Server 2000 -2- Inhalt Erstellen einer automatischen Datensatz-Änderungshistorie 3 Einleitung 3 Einschränkungen 3 Realisierung der Ablagetabelle 3 Struktur der Tabelle UpdateTriggerResult SQL Script der Tabelle UpdateTriggerResult Der Trigger Anlegen eines Triggers Definition des Triggers Notwendige Anpassungen © Copyright combit GmbH, Alle Rechte vorbehalten. 3 3 4 4 4 6 Produkt: combit Relationship Manager Datensatzhistorie mit dem SQL Server 2000 -3- Erstellen einer automatischen Datensatz-Änderungshistorie Einleitung Mit dem Microsoft SQL Server 2000 haben Sie die Möglichkeit, Datensatzänderungen automatisch protokollieren zu lassen. Hierzu werden sogenannte Trigger verwendet. Ein Trigger ist eine besondere Art von gespeicherter Prozedur, die nicht direkt von einem Benutzer aufgerufen wird. Beim Erstellen des Triggers wird festgelegt, dass der Trigger auszuführen ist, wenn eine bestimmte Art von Datenänderung an einer bestimmten Tabelle oder Spalte vorgenommen wird. Einschränkungen Da der hier gezeigte Trigger auch die jeweiligen Inhalte (Alt/Neu) der betroffenen Felder protokolliert, gelten folgende Einschränkungen: Tabellen mit Feldern vom Typ text, ntext, image können nicht mit diesem Trigger überwacht werden. Er muss hierfür ein wenig angepasst werden. Lesen Sie hierzu bitte den Abschnitt ’Notwendige Anpassungen’. Realisierung der Ablagetabelle Zur Speicherung der Historie wird eine Tabelle in der gleichen Datenbank erstellt, in die die jeweiligen Änderungsinformationen automatisch von dem Trigger eingetragen werden. Struktur der Tabelle UpdateTriggerResult Feldname SQL Datentyp Benutzer varchar(20) Datum datetime Feld varchar(64) ID uniqueidentifier RefRecordID uniqueidentifier InhaltAlt InhaltNeu Inhalt cRM Benutzername Serverdatum + Zeit Betroffener Feldname ID dieses Datensatzes ID des geänderten Datensatzes Inhalt vor der Änderung Inhalt nach der Änderung varchar(1000) varchar(1000) SQL Script der Tabelle UpdateTriggerResult CREATE TABLE [dbo].[UpdateTriggerResult] ( [Benutzer] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL , [Datum] [datetime] NOT NULL , [Feld] [varchar] (64) COLLATE Latin1_General_CI_AS NOT NULL , [ID] uniqueidentifier ROWGUIDCOL NOT NULL , [RefRecordID] [uniqueidentifier] NOT NULL , [InhaltAlt] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL , [InhaltNeu] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY] GO © Copyright combit GmbH, Alle Rechte vorbehalten. Produkt: combit Relationship Manager Datensatzhistorie mit dem SQL Server 2000 -4- Der Trigger Anlegen eines Triggers Im Enterprise Manager des SQL Servers haben Sie die Möglichkeit, für eine bestimmte Tabelle einen Trigger zu erstellen. Wechseln Sie hierzu über Tabelle bearbeiten in den Eigenschaftsdialog der Tabelle. Hier haben Sie über das Kontextmenü Aufgabe > Trigger verwalten Zugriff auf einen weiteren Konfigurationsdialog: Definition des Triggers Der hier gezeigte Trigger fügt für jede Feldänderung einen neuen Datensatz in der Tabelle UpdateTriggerResult an. /* This trigger records the history of field changes */ CREATE TRIGGER [UpdateHistory] ON [dbo].[CRM] FOR UPDATE AS declare @bit int, @field int, @maxfield int, @char int, @fieldname varchar(128), @TableName varchar(128), @CurrentDateTime datetime, @ContentsNew varchar(1000), @ContentsOld varchar(1000), @crmusername CHAR(20), @sql nvarchar(200), © Copyright combit GmbH, Alle Rechte vorbehalten. Produkt: combit Relationship Manager Datensatzhistorie mit dem SQL Server 2000 @CurrentPK uniqueidentifier -5- -- make sure this variable has the right type -- set the current tablename here select @TableName = 'CRM' select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName select @CurrentDateTime = getdate() -- get current primary key -> make sure the variable has the right type select @CurrentPK = [RECORDID] from deleted -- create temporary tables select * into #del FROM deleted select * into #ins FROM inserted -- get the current cRM Login name select @crmusername = [User] FROM combit_CRM_System..cmbt_LoginInfo AS z INNER JOIN master..sysprocesses AS p ON p.net_address = REPLACE(z.MACAddress, ':', '') AND p.spid = @@SPID if @crmusername IS NULL SET @crmusername = '' -- iterate all changed fields while @field < @maxfield begin select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field select @bit = (@field - 1 )% 8 + 1 select @bit = power(2,@bit - 1) select @char = ((@field - 1) / 8) + 1 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 begin select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field -- get the new content of that field select @sql = N'select @ContentsNew = ' + @fieldname + N' FROM #ins' EXEC sp_executesql @sql, N'@ContentsNew varchar(1000) OUTPUT', @ContentsNew OUTPUT -- get the old content of that field select @sql = N'select @ContentsOld = ' + @fieldname + N' FROM #del' EXEC sp_executesql @sql, N'@ContentsOld varchar(1000) OUTPUT', @ContentsOld OUTPUT -- save the change information INSERT INTO UpdateTriggerResult (Benutzer, Datum, Feld, RefRecordID, InhaltAlt, InhaltNeu) @CurrentDateTime, @fieldname, @CurrentPK, end © Copyright combit GmbH, Alle Rechte vorbehalten. @ContentsOld , @ContentsNew) VALUES (@crmusername, Produkt: combit Relationship Manager Datensatzhistorie mit dem SQL Server 2000 -6- end Notwendige Anpassungen Damit der Trigger auch mit Ihren Tabellen funktioniert, sind einige Anpassungen notwendig: 1. Sollte der Datentyp Ihres Primärschlüssels anders sein, so passen Sie dies bitte in folgender Zeile an: @CurrentPK uniqueidentifier 2. Anpassung des Feldnamens für den Primärschlüssel select @CurrentPK = [RECORDID] from deleted 3. Anpassung des Tabellennamens select @TableName = 'CRM' 4. Wenn Sie die Inhalte der Felder nicht protokollieren möchten, oder den Trigger in einer Tabelle mit text, ntext und image Feldern verwenden wollen, löschen Sie bitte folgende Zeilen: select * into #del FROM deleted select * into #ins FROM inserted und select @sql = N'select @ContentsNew = ' + @fieldname + N' FROM #ins' EXEC sp_executesql @sql, N'@ContentsNew varchar(1000) OUTPUT', @ContentsNew OUTPUT select @sql = N'select @ContentsOld = ' + @fieldname + N' FROM #del' EXEC sp_executesql @sql, N'@ContentsOld varchar(1000) OUTPUT', @ContentsOld OUTPUT Hinweis: combit macht keine Angaben zu einer bestimmten Eignung obiger Informationen. Irrtümer und Fehler bleiben ausdrücklich vorbehalten, die Angaben erfolgen ohne Gewähr und enthalten keine Zusicherung. Die Informationen können z.T. auch ein Versuch sein, Ihnen bei einer Aufgabenstellung zu helfen, selbst wenn das Produkt eigentlich nicht für diesen speziellen Zweck vorgesehen wurde. © Copyright combit GmbH, Alle Rechte vorbehalten.
