Datensatzhistorie mit dem SQL Server 2000

combit GmbH
Untere Laube 30
78462 Konstanz
Whitepaper
Produkt: combit Relationship Manager
Datensatzhistorie mit dem SQL Server 2000 und 2005
 Copyright combit GmbH, Alle Rechte vorbehalten.
Produkt: combit Relationship Manager
Datensatzhistorie mit dem SQL Server 2000 und 2005
-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 und 2005
-3-
Erstellen einer automatischen Datensatz-Änderungshistorie
Einleitung
Mit dem Microsoft SQL Server 2000 und 2005 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’.
Der Trigger benötigt cRM 2007.005 oder höher!
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 und 2005
-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 und 2005
@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 (needs V2007.005 or above!)
SELECT @CurrentUser =
case PATINDEX('%@combit%', "program_name")
when 0 then 'UNKNOWN'
else LEFT("program_name", PATINDEX('%@combit%', "program_name")-1)
end
FROM master..sysprocesses AS p WHERE 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
 Copyright combit GmbH, Alle Rechte vorbehalten.
Produkt: combit Relationship Manager
Datensatzhistorie mit dem SQL Server 2000 und 2005
(Benutzer, Datum, Feld, RefRecordID, InhaltAlt, InhaltNeu)
@CurrentDateTime, @fieldname,
@CurrentPK,
-6-
VALUES
(@crmusername,
@ContentsOld , @ContentsNew)
end
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.