Ich habe eine SQL Azure-Tabelle und ich habe die neue Temporal Table-Funktion aktiviert (Neu in SQL Server 2016 und SQL Azure v12). Diese Funktion erstellt eine weitere Tabelle, um alle Änderungen an der Primärtabelle zu verfolgen (ich habe einen Link zu den Dokumenten über temporale Tabellen am Ende meiner Frage eingefügt). Sie können die spezielle Abfragesprache verwenden, um diesen Verlauf zu erhalten. Beachten Sie die FOR SYSTEM_TIME ALL in der folgenden Abfrage:
%Vor%Die Ergebnismenge sieht folgendermaßen aus:
%Vor%Verwendung von SYSTEM_TIME FOR ALL Die temporale Tabelle gibt den aktuellen Datensatz aus der primären Tabelle zurück, der die erste ist, und die verbleibenden Datensätze sind frühere Versionen dieses Datensatzes, die in der Verfolgungstabelle gespeichert sind. (Sie können die Spalten validFrom und ValidTo sehen, offensichtlich die Zeit, zu der der Datensatz der aktuelle Datensatz war). In diesem Fall heißt die Verfolgungstabelle, die die historischen Datensätze enthält, KrisisShifts_ShiftTrade_History
Ich möchte eine Abfrage erstellen, die nur die an jedem historischen Punkt vorgenommenen Änderungen hervorhebt. Beachten Sie, dass der zweite Datensatz eine andere StatusID aufweist und dass der dritte Datensatz ein anderes TradeDate
aufweistIch möchte eine Ergebnismenge wie folgt erstellen (ich stelle mir vor, dass ich den ersten oder aktuellen Datensatz ignorieren werde, weil er offensichtlich nicht definiert ist):
GEWÜNSCHTES ERGEBNIS :
%Vor%Ich bin mir nicht sicher, wie ich das erreichen soll. Oder ich bin offen für eine andere Lösung. Ich möchte die Änderungen für jeden Datensatz im Vergleich zum ursprünglichen Datensatz schnell anzeigen können.
Ich habe versucht, die Ergebnisse zu entfernen, um sie zu vergleichen, aber ich konnte das nicht zum Laufen bringen, weil die Schicht-ID für jede Zeile gleich ist. Ich würde gerne mehr Arbeit hier zeigen, aber ich bin wirklich fest.
EDIT 1:
Ich konnte die Änderungen für nur eine Spalte in der folgenden Abfrage mithilfe von lag () isolieren. Ich könnte diese Abfrage mit einer ähnlichen für jede Spalte verknüpfen, die ich verfolgen möchte, dies ist jedoch eine Menge Arbeit und muss für jede Tabelle erstellt werden. Gibt es eine Möglichkeit, dies dynamisch zu tun, damit es die Spalten automatisch erkennt?
Status-ID-Änderungsverlaufsabfrage: (Ich isoliere die Datensätze nur zum Testen auf eine shiftId von 27)
%Vor%Ergebnisse der Abfrage:
%Vor%END EDIT 1:
Kann jemand mir helfen, nur die geänderten Daten in Spalten aus dem vorherigen Datensatz für jede shiftId in der temporalen Tabellenergebnismenge zu isolieren?
Vielen Dank im Voraus
BEARBEITEN # 2:
Im Folgenden finden Sie eine Liste aller Spalten, die ich aus dieser Tabelle "auf Änderungen beobachten" möchte:
[TradeDate] [StatusID] [LastActionDate] [AllowedRankID] [EigentümerbenutzerID] [Eigentümer E-Mail] [Besitzerstandort-ID] [EigentümerRankID] [Eigentümer-Mitarbeiter-ID] [WorkerUserID] [ArbeiterEmail] [WorkerLocationID] [WorkerRankID] [WorkerPlatoonID] [WorkerEmployeeID] [IsPartialShift] [Detail] [LastModifiedByUserID] [Archiviert] [Aktualisiertes Datum]
END EDIT 2:
Ich habe ein neues Tag für temporale Tabellen erstellt, da es keins gibt. Das Folgende hat die Beschreibung von ihnen, wenn jemand mit mehr Reputation es zu den Details des Tags hinzufügen möchte.
Sie können auch CROSS APPLY bis UNPIVOT verwenden.
Es ist zu beachten, dass sich die ValidFrom und ValidTo auf die Gültigkeit der Zeilenversion selbst beziehen, nicht unbedingt auf den Spaltenwert. Ich glaube, das ist, was Sie verlangen, aber das kann verwirrend sein.
Wenn Sie die Gültigkeit auf Spaltenebene möchten, können Sie ( Demo )
verwenden %Vor%Methode
Würden vorschlagen, eine gespeicherte Prozedur zu verwenden, die die Zeilen mithilfe eines Cursors durchläuft und die Ergebnisse in einer temporären Tabelle aufbaut. (Da es hier eine überschaubare Anzahl von Spalten gibt, würde ich vorschlagen, die Vergleiche für jeden Spaltenwert manuell durchzuführen, anstatt es dynamisch zu versuchen, da letzteres komplexer wäre.)
Demo
Rextester-Demo: Ссылка
Gespeicherte Prozedur SQL
%Vor%Hinweis: Das obige enthält nur die Spalten, die in dem Beispiel in der Frage enthalten waren. Die Liste der Spalten, die bei der letzten Änderung geändert werden konnten, war breiter als diese, aber die anderen konnten natürlich auf die gleiche Weise hinzugefügt werden.
Dies wird sicherlich nicht der beste Weg sein, erfüllt aber die Anforderung
%Vor%Gibt es eine Möglichkeit, dies dynamisch zu tun, so dass es die Spalten erkennt automatisch?
- Sehr interessante Frage.
- Denken Sie über Ihr gewünschtes Ergebnis nach - die Spalte "Value" sollte Werte verschiedener Typen enthalten (int, dezimal, date, binary, varchar, ...). Sie müssen also Werte in varchar konvertieren oder sqlvariant oder binary verwenden. Dann müssen Sie an einem Punkt den Werttyp erkennen und für die verschiedenen Zeilen anders verarbeiten
- Um Werte zu erhalten, können Sie versuchen, UNPIVOT zu verwenden:
%Vor%Dann ähnlich UNPIVOT vorherige Werte
... und verknüpfen Sie die Ergebnisse als
%Vor%Das ist nur eine Idee und ich hoffe, es wird Ihnen helfen
Versuchen Sie nicht, die temporale Tabellenfunktion zu verwenden :). Versuchen Sie Trigger, um Änderungen zu überprüfen - es ist viel einfacher und viel kürzer.
Erstellen Sie ein Bild Ihrer Tabelle mit den Spalten timestamp und dml type (row_id, s__dml_dt, s__dml_type + alle Spalten aus der Quellentabelle) für alle dml-Typen (i, u, d).
%Vor%Nun können Sie nach dem Einfügen / Löschen / Aktualisieren alle Ihre historischen Werte überprüfen. Wenn Sie ein geschwenktes Ergebnis wünschen, können Sie einfach eine Ansicht mit Pivot für dbo.KrisisShifts_ShiftTrade_logtable erstellen.
Script, um alle Tabellen in der Datenbank zu protokollieren (es werden Tabellen mit Präfix r _ erstellt).
%Vor%Wie viel Speicherplatz haben Sie?
Das letzte Mal, als ich so etwas gemacht habe, haben wir neue Zeilen für jede geänderte Spalte in eine separate Änderungsprotokolltabelle eingefügt. Wir haben es mit clientseitiger Logik gemacht, aber Sie könnten den gleichen Effekt mit einem Trigger erzielen.
Dies nimmt viel Platz in Anspruch und verlangsamt Ihre Schreibvorgänge, bietet Ihnen jedoch schnellen Lesezugriff auf das Änderungsprotokoll.
P.S. Wir hatten keine allgemeine Lösung, also haben wir es nur für die eine Tabelle getan, die UI-Unterstützung benötigte. Alles andere verwendete pseudo-temporale Tabellen. (Alte Version von SQL Server.)
Tags und Links sql sql-server tsql azure-sql-database temporal-tables