SQL Datensätzen versionsverwalten

heimchen

Hi,

ich habe eine Idee, weiß aber nicht so recht, wie ich sie umsetzen soll...
Ich habe in einer SQL-Tabelle viele verschiedene Datensätze gespeichert. Wenn ich einen davon ändere, soll dieser jedoch nicht überschrieben werden, sondern ein neue Eintrag als Kopie mit der gemachten Änderung erstellt und eine Spalte "Version" hochgezählt werden. So dass ich die Änderungen immer nachvollziehen kann.
Wie setzte ich sowas am elegantesten um?

asc

heimchen schrieb:

Ich habe in einer SQL-Tabelle viele verschiedene Datensätze gespeichert. Wenn ich einen davon ändere, soll dieser jedoch nicht überschrieben werden, sondern ein neue Eintrag als Kopie mit der gemachten Änderung erstellt und eine Spalte "Version" hochgezählt werden. So dass ich die Änderungen immer nachvollziehen kann.

Grundsätzlich stellt sich mir hier die Frage wie du üblicherweise auf die Werte zugreifst: Ist die Historie immer wichtig, oder nur informativ?

Ich würde hierfür immer eine zweite Tabelle anlegen, die neben der Id noch eine Version und einen Status hält. Falls du mit einem System wie den SQL Server (oder jeder anderen Triggergesteuerten Datenbank) hantierst, kannst du das ganze im Hintergrund mittels Triggern lösen.

Beispiel für den SQL Server (Wobei mir eine "exakte" Versionsnummer unwichtig ist, und ich hier mit dem SQL-Server-Datentyp rowversion arbeite - Entsprechendes lässt sich auch mit Triggern herleiten):

-- Vereinfachter Auszug aus SQL-Script
-- (Ohne Gewähr und sicherlich Verbesserungswürdig)

-- Datentabelle
CREATE TABLE [TTabelle1]
(
  [Id]                  int            NOT NULL  IDENTITY(1, 1),
  [Wert1]               nvarchar(100)  NOT NULL,
  [Version]             rowversion     NOT NULL
)
GO

ALTER TABLE [TTabelle1]
ADD CONSTRAINT [PK__TTabelle1]
PRIMARY KEY ([Id])
GO

-- Historientabelle
CREATE TABLE [HTabelle1]
(
  [Id]                  int            NOT NULL,
  [Wert1]               nvarchar(100)  NOT NULL,
  [HistorieVersion]     binary(8)      NOT NULL,
  [HistorieBenutzer]    sysname        NOT NULL  DEFAULT (original_login()),
  [HistorieZeitstempel] datetime       NOT NULL  DEFAULT GETDATE(),
  [HistorieAktion]      char(1)        NOT NULL
)
GO

ALTER TABLE [HTabelle1]
ADD CONSTRAINT [PK__HTabelle1]
PRIMARY KEY ([Id], [HistorieVersion], [HistorieAktion])
GO

-- CTabelle1_Insert
CREATE TRIGGER [CTabelle1_Insert]
ON [TTabelle1]
AFTER INSERT
AS
BEGIN
    INSERT INTO [HTabelle1]
    ( [Id], [Wert1], [HistorieVersion], [HistorieAktion] )
    SELECT [Id], [Wert1], [Version], 'I'
    FROM inserted
END
GO

-- CTabelle1_Update
CREATE TRIGGER [CTabelle1_Update]
ON [TTabelle1]
AFTER UPDATE
AS
BEGIN
    INSERT INTO [HTabelle1]
    ( [Id], [Wert1], [HistorieVersion], [HistorieAktion] )
    SELECT [Id], [Wert1], [Version], 'U'
    FROM inserted
END
GO

-- CTabelle1_Delete
CREATE TRIGGER [CTabelle1_Delete]
ON [TTabelle1]
AFTER DELETE
AS
BEGIN
    INSERT INTO [HTabelle1]
    ( [Id], [Wert1], [HistorieVersion], [HistorieAktion] )
    SELECT [Id], [Wert1], [Version], 'D'
    FROM deleted
END
GO

Die Trigger sorgen dafür das mit jedem Einfügen/Ändern/Löschen in der Datentabelle ein Eintrag in der Historientabelle generiert wird (Wobei bei meinen ablauf beim Delete die gleiche HistorieVersion gesetzt wird, wie von der Änderung davor, deshalb ist die Aktion zusätzlich Teil des Schlüssels).

heimchen

Das mit den Triggern scheint mir ne coole Sache zu sein. Probier ich mal aus...

heimchen

Kann ich eigentlich auch die neuen Werte ändern, bevor sie eingetragen werden?
Verwende ja eher die umgekehrte Richtung, also die Werte.Tabelle hat eine Spalte HistoryID. Dann würde ich im Trigger einen neuen History-Eintrag erstellen und die ID dann in die HistoryID-Spalte des neuen Datensatzes eintragen.
Aber mit inseretd geht das irgendwie nicht...

Ich benutze jetzt AFTER INSERT, und ändere den eingefügten Datensatz. Dabei laufe ich aber Gefahr, dass wen zwei Nutzer gleichzeitig die gleiche ID eintragen (man ist das unwahrscheinlich), ich die Änderung des anderen Nutzers auch mit meiner Versionsnummer belege.

asc

heimchen schrieb:

Verwende ja eher die umgekehrte Richtung, also die Werte.Tabelle hat eine Spalte HistoryID.

Ich habe nicht ohne Grund bei mir die Abhängigkeiten zwischen Historie und normaler Tabelle gänzlich entfernt. Zumal das die Möglichkeit gibt die Historie auch irgendwann zu löschen (z.B. lösche alle Einträge die älter als 2 Monate sind), und die Historie bei uns optional ist (Nicht jeder nimmt die Datenmengen in Kauf, und bei unseren Einzelplatzlizenzen kommt eine kleinere DB zum Einsatz...).

Ich kenne aber auch nicht deine Ziele mit den Versionsständen usw.