Tweet
Hallo zusammen,
für diverse Tabellen einer Datenbank sollen Datensätze in einer History-Tabelle erzeugt werden (der Datenbankserver ist ein SQL 2000 Server).
Nun gibt es ja die praktischen Trigger und man kann mit der temporären Tabelle "inserted" im Trigger die eingefügten Daten auslesen und in eine identisch aufgebaute History-Tabelle schreiben.
Das wäre aber viel zu aufwendig, da sich die Struktur der Tabellen bisweilen verändert und außerdem jede Tabelle, die eine History benötigt, eine strukturidentische Tabelle für die Aufnahme der History-Daten benötigen würde. Vom Trigger her ist dies natürlich die einfachste und performanteste Lösung.
Ich habe mir dazu die folgende Lösung ausgedacht (die auch funktioniert):
1. Erstellen einer universellen History-Tabelle, die für jede Zeile ein Feld der Tabelle speichert, deren geänderte Werte man sichern möchte. Die Struktur sieht so aus:
2. Ein INSERT-Trigger über der Tabelle (hier "tblPlan") sieht dann so aus:
Dieser Trigger funktioniert insofern universal, als man für eine weitere Tabelle diesen Trigger lediglich kopieren und den Namen der Tabelle in der Variablen "@TableName" sowie ggf. den Spaltennamen für "n_lastchange_userid" ändern muß.
Der Trigger berücksichtigt ebenfalls mehrere gleichzeitige INSERTs, etwa über einen INSERT ...SELECT.
Wie gesagt, der Trigger funktioniert. Natürlich wird abhängig von der Anzahl der Spalten in der Tabelle, die getriggert werden soll, die Anzahl Zeilen in der History-Tabelle auf die Dauer schnell recht groß, so daß sicherlich ein Aufräumen der History irgendwann nötig wird.
Was ich gerne wissen würde ist, ob jemand eine Idee hat, wie man die Performance dieses Triggers irgendwie steigern kann. Beim Speichern eines einzelnen Datensatzes im Formular (Frontend Access) ist das kein Problem, da muß man lediglich ein kleines bißchen länger warten als sonst.
Fügt man allerdings mit einem INSERT..SELECT größere Mengen Daten in die Tabelle ein, geht die Performance sehr schnell in die Knie, was ja auch nicht wundert, da hier doch eine Reihe Operationen erledigt werden, die von Haus aus ja schon nicht als performant gelten, wie die Verwendung von CURSOR und dynamischem SQL.
Mir ist lediglich keine bessere Variante eingefallen, bin für Ideen aber immer dankbar.
Gruß
Christian
PS.: Der Code oben darf selbstverständlich beliebig in eigene Entwicklungen eingebaut werden.
für diverse Tabellen einer Datenbank sollen Datensätze in einer History-Tabelle erzeugt werden (der Datenbankserver ist ein SQL 2000 Server).
Nun gibt es ja die praktischen Trigger und man kann mit der temporären Tabelle "inserted" im Trigger die eingefügten Daten auslesen und in eine identisch aufgebaute History-Tabelle schreiben.
Das wäre aber viel zu aufwendig, da sich die Struktur der Tabellen bisweilen verändert und außerdem jede Tabelle, die eine History benötigt, eine strukturidentische Tabelle für die Aufnahme der History-Daten benötigen würde. Vom Trigger her ist dies natürlich die einfachste und performanteste Lösung.
Ich habe mir dazu die folgende Lösung ausgedacht (die auch funktioniert):
1. Erstellen einer universellen History-Tabelle, die für jede Zeile ein Feld der Tabelle speichert, deren geänderte Werte man sichern möchte. Die Struktur sieht so aus:
Code:
HF_DateTime smalldatetime HF_UserID int HF_TableName varchar(80) HF_FieldName varchar(100) HF_FieldValue ntext, NULL allowed HF_Action char(6) HF_ChangeID int t_timestamp timestamp
Code:
-- =============================================
-- Author: Christian Coppes
-- Create date: 16.07.2009
-- Description: History Trigger für "tblPlan"
-- =============================================
ALTER TRIGGER [dbo].[tblPlanTrigger_Insert]
ON [dbo].[tblPlan]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- @Cmd muß als nvarchar definiert werden
DECLARE @UserID AS int,
@IDField AS int,
@ColumnName AS nvarchar(100),
@FieldValue AS nvarchar(4000),
@Cmd AS nvarchar(500),
@TableName AS nvarchar(7),
@CurrentDate AS smalldatetime
SET @TableName='tblPlan'
SET @CurrentDate=GetDate()
-- Es müssen alle Spalten ausgeklammert werden, die Timestamp-Werte
-- oder vom Typ ntext/text/image sind
DECLARE TableColumnname CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND DATA_TYPE<>'text'
AND DATA_TYPE<>'ntext'
AND DATA_TYPE<>'image'
AND DATA_TYPE<>'timestamp'
ORDER BY COLUMN_NAME
-- ein zweiter Cursor ist nötig, da die Tabelle "inserted" mehr als einen
-- Eintrag enthalten kann, wenn beispielsweise ein INSERT INTO tblPlan SELECT...
-- durchgeführt wird.
DECLARE TableInserted CURSOR FOR
SELECT IdentityCol, n_lastchange_userid FROM inserted
OPEN TableInserted
-- "IDField" enthält den Wert des ID-Feldes, um die eingefügte Zeile in der
-- Tabelle im EXECUTE-SELECT-Befehl wiederzufinden.
-- @UserID stammt aus einer Spalte "n_lastchange_userid", die vom Frontend aus
-- die ID des Benutzers enthält, der den Datensatz angelegt hat.
-- Hier kann nicht der SQL User verwendet werden, da die Anwendung über eine
-- eigene Benutzerverwaltung verfügt.
FETCH NEXT FROM TableInserted INTO @IDField,@UserID
WHILE @@FETCH_STATUS<>-1
BEGIN
OPEN TableColumnname
FETCH NEXT FROM TableColumnname INTO @ColumnName
WHILE @@FETCH_STATUS<>-1
BEGIN
-- da der Datensatz bereits vor der Triggerausführung eingefügt
-- wurde, kann das dynamische SQL auf die echte Tabelle zurückgreifen.
-- Im EXECUTE-Gültigkeitsbereich ist die temporäre Tabelle "inserted"
-- nicht verfügbar!
SET @Cmd='SET @ColValue=CONVERT(nvarchar(4000),' +
'(SELECT ' + CAST(@ColumnName AS nvarchar(100)) +
' FROM ' + @TableName +
' WHERE IdentityCol=' + CAST(@IDField AS nvarchar(50)) + '))'
-- nur mit sp_executesql ist es möglich, Variablen aus der
-- EXECUTE-Umgebung wieder zurückzubekommen.
EXECUTE sp_executesql @Cmd,
N'@ColValue nvarchar(4000) out',
@FieldValue out;
-- für jedes Feld in der Tabelle wird eine Zeile in die Tabelle
-- "HistoryFields" geschrieben mit dem entsprechenden Feldinhalt
INSERT dbo.tblHistoryFields
(HF_DateTime,HF_UserID,HF_TableName,HF_FieldName,HF_FieldValue,HF_Action,HF_ChangeID)
VALUES (@CurrentDate,@UserID,@TableName,@ColumnName,@FieldValue,'INSERT',@IDField);
FETCH NEXT FROM TableColumnname INTO @ColumnName
END -- while
CLOSE TableColumnname
FETCH NEXT FROM TableInserted INTO @IDField,@UserID
END -- while
DEALLOCATE TableColumnname
CLOSE TableInserted
DEALLOCATE TableInserted
END
Der Trigger berücksichtigt ebenfalls mehrere gleichzeitige INSERTs, etwa über einen INSERT ...SELECT.
Wie gesagt, der Trigger funktioniert. Natürlich wird abhängig von der Anzahl der Spalten in der Tabelle, die getriggert werden soll, die Anzahl Zeilen in der History-Tabelle auf die Dauer schnell recht groß, so daß sicherlich ein Aufräumen der History irgendwann nötig wird.
Was ich gerne wissen würde ist, ob jemand eine Idee hat, wie man die Performance dieses Triggers irgendwie steigern kann. Beim Speichern eines einzelnen Datensatzes im Formular (Frontend Access) ist das kein Problem, da muß man lediglich ein kleines bißchen länger warten als sonst.
Fügt man allerdings mit einem INSERT..SELECT größere Mengen Daten in die Tabelle ein, geht die Performance sehr schnell in die Knie, was ja auch nicht wundert, da hier doch eine Reihe Operationen erledigt werden, die von Haus aus ja schon nicht als performant gelten, wie die Verwendung von CURSOR und dynamischem SQL.
Mir ist lediglich keine bessere Variante eingefallen, bin für Ideen aber immer dankbar.
Gruß
Christian
PS.: Der Code oben darf selbstverständlich beliebig in eigene Entwicklungen eingebaut werden.
Comment