kasbaba Ответов: 1

Воссоздать данные по состоянию на #дату#

У меня есть набор данных с 50000 строками данных. Данные состоят из 112 столбцов.
У меня есть триггер обновления данных, чтобы создать журнал аудита, когда что-то меняется.
Таблица аудита содержит следующие столбцы:

CREATE TABLE [dbo].[L_Audit](
	[TableName] [varchar](50) NOT NULL,
	[FieldName] [varchar](50) NOT NULL,
	[ID] [varchar](50) NOT NULL,
	[OldValue] [varchar](max) NULL,
	[NewValue] [varchar](max) NULL,
	[ChangedBy] [varchar](50) NOT NULL,
	[ChangedOn] [varchar](50) NOT NULL)

--TableName - which table triggered the update
--fieldName - which field was updated
--ID - unique identifier of the data that was changed


Вопрос в том, как я могу создать точку во временной позиции ?

Что я уже пробовал:

Пытался использовать соединения, но проблема в том, что я не могу определить последнее обновленное значение, то есть значение min(ChangedOn). Другая проблема заключается в том, что я не хочу рекурсивно искать.

Select Top 10 [ID],[Supplier],OldValue,CASE WHEN fieldname='Supplier' THEN OldValue ELSE [Supplier] END as [C_Supplier] from [dbo].BaseData BD
LEFT JOIN L_Audit ON
L_Audit.ID=I.[ID]
where TableName='BaseData'

Richard Deeming 

[ChangedOn] [varchar](50)

Если это означает дату и время, когда значение было изменено, то вы должны использовать правильный тип дата+время - я бы предложил datetime2 или datetimeoffset.

kasbaba

Это исправлено. Спасибо, что указал. Хотя я использовал CAST в запросе.

Rob Philpott

Немного смущен. Итак, у вас есть традиционная "живая" таблица, которая показывает последние значения для этих 50 000 записей, а также Таблица аудита ключа/значения, которая хранит всю историю другой таблицы? И я предполагаю, что вы хотите сделать запрос "точка во времени", где вы можете увидеть, как выглядела бы таблица в этот момент в прошлом? И когда вы добавляете новую строку в основную таблицу, создает ли она 112 строк в таблице аудита?

kasbaba

Привет, Роб - да. То, что я хочу сделать, - это запрос, который может показать мне данные о моменте времени с помощью аудита (таблицы журнала) и таблицы базовых данных.

И нет, он не добавляет 112 строк. Строка вставляется только тогда, когда что-то изменилось. Таким образом, у нас нет никаких записей в таблице журнала для вновь созданных элементов. Только если они изменяются в течение своего жизненного цикла, можно ожидать, что они увидят их в таблице журнала.

Надеюсь, это имеет смысл.

1 Ответов

Рейтинг:
2

CHill60

Вы не указали, какую версию SQL Server вы используете, но в SQL 2016 есть "временные таблицы", предназначенные для воссоздания данных в определенное время - см. SQL 2016 – временные таблицы – что это такое и как вы их настраиваете? – Блог главного полевого инженера SQL Server[^]

Если вы используете более ранние версии SQL Server, то почему бы не сохранить последнюю обновленную дату и время в самой таблице, а не в отдельной таблице аудита?

Кроме того, захватите последнюю запись аудита для каждого элемента во временной таблице или CTE, например(NB непроверен и может содержать опечатки) 

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ChangedOn DESC) AS rn
   FROM L_Audit where TableName='BaseData'
)
select [ID],[Supplier],CTE.OldValue,
CASE WHEN fieldname='Supplier' THEN CTE.OldValue ELSE [Supplier] END as [C_Supplier] from [dbo].BaseData BD
LEFT JOIN CTE ON L_Audit.ID=CTE.[ID]
WHERE rn = 1

kasbaba

Спасибо CHill60 за ответ. Я использую SQL 2008 R2. Я не хочу использовать последнее обновление datetime, так как это вызовет массовую переработку, и я где-то читал, что эта методология не должна использоваться на больших наборах данных.

В любом случае, я попробую фрагмент SQL, который вы предоставили, и дам вам знать, если он работает / не работает.

Большое спасибо

Maciej Los

5ed!