Как посмотреть кто изменял таблицу в sql

Журналирование изменений данных в таблице на Transact-SQL

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

И для начала хотелось бы сказать, что все примеры мы будем рассматривать на базе данных MSSql 2008.

Для чего нужно журналировать изменения данных?

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

Задача.

Необходимо отслеживать изменения при выполнении одной процедуры, и записывать их в отдельную таблицу. В которой необходимо хранить следующие данные: кто обновлял или добавлял данные, когда, какие данные были до этого и какие данные стали.

Решение.

В эту процедуру необходимо встроить процесс журналирования, а именно написать процедуру для получения данных в виде строки, всех полей одной записи таблицы, по уникальному идентификатору (при правильном планирование структуры базы данных такой идентификатор всегда создается) с динамическим изменением количества полей (так как в дальнейшем может осуществляться добавление колонок в таблицу), для того чтобы отслеживать данные, какие были и какие стали. Создать таблицу для журналирования. Вставлять в нее данные по всем изменениям, путем простого insert. Написать функцию, которая парсила строку с данными для оперативного и удобного просмотра этих самых данных. Вот и все.

Теперь давайте приступать к реализации поставленной задачи. Код процедуры, в которую я встроил процесс журналирования я приводить не стану, так как это можно сделать практически на примере любых процедур, приведу лишь пример о том, как встроить этот процесс в процедуру. Более подробно мы остановимся на написание процедуры, которая объединяет все поля таблицы в одно текстовое поля с разделителем полей точка запятая (Вы можете использовать и другой) и функции, которая парсит это дела обратно для просмотра. И в процессе всего этого, в продолжение наших статей посвященных SQL, мы узнаем много чего интересного, например, каким образом можно использовать системные таблицы или как написать процедуру на SQL. Также хотелось бы отметить, что для лучшего освоения и понимания всего ниже перечисленного, для начала необходимо ознакомится с основами SQL, и в помощь Вам могу посоветовать прочитать статьи об основах оператора Select, и также ознакомиться с примерами использования строковых функций SQL.

Создаем таблицу для аудита на SQL.

CREATE TABLE [audit]( [id] [bigint] IDENTITY(1,1) NOT NULL, [table_name] [varchar](50) NOT NULL, [oper] [varchar](15) NOT NULL, [record_old] [varchar](500) NULL, [record_new] [varchar](500) NULL, [username] [varchar](50) NOT NULL, [dt_in] [datetime] NULL, CONSTRAINT [PK_audit] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO

Заметка! Если Вы не знаете, что делает вышеуказанная инструкция, рекомендую посмотреть мой видеокурс «T-SQL. Путь программиста от новичка к профессионалу. Уровень 1 – Новичок», который предназначен для начинающих. В нем подробно рассмотрены все базовые конструкции языка T-SQL.

• Id – идентификатор записи, первичный ключ с идентификацией;
• table_name – название таблицы, в которой происходит изменения;
• oper – операция по изменению, в нашем случае update или insert;
• record_old – старые значения;
• record_new – новые значения;
• username – имя пользователя кто изменял;
• dt_in – дата и время когда изменяли.

Пишем процедуру по объединению всех полей таблицы.

Я, честно говоря, долго искал готовое решение этой задачи, т.е. какую-нибудь функцию или процедуру, но так и не нашел, пришлось писать самому и вот что у меня получилось, код я как всегда прокомментировал.

CREATE PROCEDURE [sp_row_to_string] (@table varchar(30), @id varchar(30), @out nvarchar (500) output) AS --объявляем переменные declare @cnt int declare @vr int declare @pquery nvarchar(3000) declare @tmp nvarchar(3000) declare @query nvarchar(3000) declare @params nvarchar(3000) --задаем начальное значение set @params = N'@tmp nvarchar(3000) output' set @pquery = '' set @vr = 1 --узнаем сколько всего полей в таблице --название, которой нам передали во входящем параметре --используя для этого системное представление sys.columns select @cnt = COUNT(*) from sys.columns where object_id = object_id(@table) --для формирования запроса по именам полей запускаем цикл --выходим из него тогда когда счетчик станет больше или равен количеству полей в таблице while @cnt>=@vr begin --формируем строку запроса --т.е. конкатенируем все поля из таблицы, разделяя точкой с запятой --и для обработки возможных ошибок используем дополнительные встроенные функции --coalesce, rtrim и cast select @pquery = @pquery +'coalesce(rtrim(cast('+ name +'as varchar(200))),'''')'+'+'+''';''' + case when @cnt<>@vr then ' + ' else '' end from sys.columns where object_id = object_id(@table) and column_id=@vr set @vr = @vr+1 end set @query = N'select @tmp='+ @pquery + ' from ' + @table + ' where kod =' + @id +'' --выполняем сформированный запрос и вернем результат через выходной параметр out exec sp_executesql @query, @params, @tmp=@out output GO

Данная процедура имеет два входящих параметра это @table – название таблицы, @id – поле, выполняющее роль идентификатора в этой таблицы, для того чтобы мы могли найти нужную нам строку и с конкатенировать все поля, также один выходящий параметр для получения уже объединенных полей, т.е. строку.

Проверить результат ее работы можно выполнив в Management Studio запрос:

declare @rezult nvarchar(500) exec sp_row_to_string 'table', '11111', @rezult output select @rezult

где, как Вы поняли table это название таблицы, 11111 уникальный идентификатор строки в этой таблице, а @rezult это наша внешняя переменная в которую мы получим результат и выведем ее на экран, в данном случае.

Интегрируем написанную процедуру с процедурой для пользователей.

CREATE PROCEDURE [sp_temp_proc] (@var int) AS BEGIN --объявляете все нужные Вам переменные --и наши declare @record_new nvarchar(500) declare @record_old nvarchar(500) declare @tmpkod nvarchar(15) --узнаем нужный нам идентификатор select @tmpkod=id from table where col= @var --запомним все данные, которые были exec [sp_row_to_string] 'table', @tmpkod, @record_old output --ЗДЕСЬ ПИШИТЕ КОД ДЛЯ ВЫПОЛНЕНИЯ ДЕЙСТВИЙ НАД ДАННЫМИ --затем смотрим, что изменилось и записываем в переменную exec [sp_row_to_string] 'table', @tmpkod, @record_new output --записываем в таблицу аудит insert into audit (table_name, oper, record_old, record_new, username, dt_in) values ('table', 'update', @record_old, @record_new, SUSER_NAME(), GETDATE() ) end GO

Как Вы поняли, что интегрировать ее достаточно просто, можно также дописать ее на операцию insert и в таблице audit будут храниться данные, как до обновления, так и после.

Пишем функцию для разбора с конкатенированной строки с разделителем

CREATE FUNCTION [fun_substr_part]( @sym char(1), --символ разделителя @str varchar(500), --строка с разделителями @num int)--позиция, т.е. номер колонки RETURNS VARCHAR(500) AS BEGIN --переменные DECLARE @pos INT, @cnt INT, @rezult VARCHAR(500) SET @pos = 1 SET @str = @str + ',' --запускаем цикл по строке --будем вырезать содержимое по разделителю --до порядкового номера указанного во входящем параметре WHILE (@num > 0) BEGIN SET @cnt = CHARINDEX(@sym, @str, @pos) - @pos IF @cnt >= 0 BEGIN SET @rezult = SUBSTRING(@str, @pos, @cnt) SET @pos = CHARINDEX(@sym, @str, @pos) + 1 END ELSE BEGIN SET @rezult = '' SET @num = 0 END SET @num = @num - 1 END RETURN(@rezult) END GO

Смысл ее очень прост, мы просто циклом бежим по строке, ориентируясь на номера, позиций символов разделителя, до того порядкового номера, который мы указали при вызове функции, т.е. номер столбца.

Проверить ее можно вот так:

select fun_substr_part(';',record_old,1) as old, fun_substr_part(';',record_new,1) as new from audit where fun_substr_part(';',record_old,1)<>fun_substr_part(';',record_new,1)

И тем самым Вы получите все изменения по данному столбцу, конечно дополнив запрос еще нужными Вам полями.

И на сегодня это все! Надеюсь, представленная выше информация, будет Вам полезна. Удачи! До новых встреч на сайте «Программирование для начинающих».

Протоколирование действий пользователей и версионность записей

Одной из частых задач при проектировании базы данных является протоколирования действий пользователей и разработка механизма версионности записей. Существует много различных методов решения этой задачи. В статье рассмотрены наиболее часто используемые при этом методы и проблемы, с которыми можно столкнуться. Для начала надо определиться с терминологией, иначе можно запутаться – у каждого может быть свой взгляд на то, что такое история изменений. Под историей изменений я имею в виду историю изменения записи таблицы базы данных. Помещенные в историю значения являются статическими и уже не могут изменяться (версии значений). Версионность обычно возникает из требования к системе предоставлять состояния записи на моменты в прошлом, протоколирование нужно для выяснения обстоятельств неправильного ввода информации.

Например, в расчете зарплаты есть понятие ставки, которая может изменяться ежедневно. Зарплата считается по актуальным для какой-то даты значениям ставок. Например, если ставка действует с 15 числа, и меняется раньше того дня, когда она должна применяться, то достаточно просто отметить, что запись была изменена (например, пользователем DBUser 10-го апреля в 14:15). Если потребуется узнать, кто последний изменял запись, можно быстро получить ответ. Но если пользователь захочет поменять ставку после того дня, когда она начала применяться и по ней пошли расчеты зарплаты, то нужно создавать новую версию записи. Обычно процесс создания новых версий для пользователя является прозрачным, т.е. пользователь ничего не знает про версии и работает, как будто никаких версий нет, ему показывается лишь одна запись по состоянию на какую-то дату, обычно последнюю. Если я запущу расчет зарплаты за прошлый месяц с точки зрения данных прошлого месяца, то мне и будет все посчитано так, как было в прошлом месяце, цифра в цифру. Если же расчет будет запущен с точки зрения данных текущего месяца, то у меня получатся другие результаты. Это я и называю историей (версионностью значений данных) — атрибуты объекта, взятые во времени, никогда не противоречат остальным данным системы за период их действия.

Метод последнего изменения

Для многих задач достаточно отслеживать, кто и когда последний раз изменял запись. Например, есть таблица “Карточка основного средства”. При внесении изменения в эту таблицу хочется знать, кто последний менял запись. В этом случае нужно добавить во все таблицы два поля: автора и время изменения . Если требуется также знать, кто удалял запись, необходимо добавить поле-признак удаления, например IsDeleted bit default 0, где значение 0 означает, что запись не удалена, 1 – что запись логически удалена. При таком подходе, вместо удаления записи физически командой delete, требуется логическое удаление записей. При выборках необходимо фильтровать по значению этого поля, при удалениях записей – просто изменить значение поля признака логического удаления. Наличие поля IsDeleted очень полезно при возникновении вопроса, куда делась запись – можно быстро и легко посмотреть, кто же ее удалил. Плюсы и минусы этого метода очевидны – требуется мало дополнительного места, просто в реализации. При таком подходе можно получить последнего пользователя, внесшего изменения, что во многих случаях является достаточным для выяснения обстоятельств неверного ввода. Однако метод не позволяет получить информацию о состоянии до последнего изменения. Кроме того, периодически нужно чистить такие таблицы, физически удаляя записи, помеченные как удаленные – например, все, помеченные, как удаленные, более месяца назад. Если операция удаления выполняется часто, то лучше переносить удаленные записи в другие таблицы, чтобы не засорять исходные удаленными записями.

Метод “таблиц-двойников”

Метод заключается в том, что для каждой основной таблицы создается дублирующая таблица, включающая все поля из исходной таблицы, а также поле-тип операции над записью. При необходимости добавляются дополнительные служебные поля. Данный подход можно назвать методом таблиц-двойников или таблиц истории. При изменениях основной записи в таблице истории создается копия измененной записи с отметкой, кто и когда ее изменил. На рис. 1 показан пример взаимодействия основной таблицы и таблицы-двойника.

Рис. 1. Взаимодействие основной таблицы и таблицы-двойника

В примере показана таблица Deterioration (износ), содержащая первичный ключ с названием RegistrationUnitID, и связанная по нему с таблицей истории Deterioration_History. В таблице истории, кроме полей исходной таблицы, содержится поле OperationTypeID. В это поле заносится тип операции – создание записи, изменение или удаление.

Иногда достаточно, чтобы сохранялись не все состояния записи, а только часть изменений. Например, какой-то пользователь несколько раз поменял одну и ту же запись. Нужно ли нам сохранять все эти изменения? Это зависит от требований к программе. В приведенном выше примере со ставкой зарплаты версии изменений необходимо создавать только если ставка измененяется после того как она начала применяться. В похожих случаях имеет смысл применение комбинированного подхода. В тех случаях, когда при изменении записи требуется сохранять предыдущее состояние, необходимо создавать новые версии записей. Для создания новой версии нужно скопировать измененную запись в таблицу-двойник. Когда новая версия не нужна — просто изменять основную запись. В этом случае при изменении основной записи можно записывать в нее же, кто и когда ее изменил. В любой возможной реализации такого подхода несложно сделать включение/выключение протоколирования, если база начнет переполняться.

Метод таблиц-двойников хорошо применять, если изменения основной таблицы происходят не слишком часто. Иначе нужно применять другие подходы, потому что таблицы истории будут быстро расти. Метод обеспечивает возможность получения данных на любой момент времени, что необходимо для версионности записей. Однако, если в таблице есть группа часто меняющихся атрибутов, можно вынести протоколирование изменений этих атрибутов в отдельную таблицу. Таким образом, протоколирование изменений будет разделено на 2 части: запись частых и запись редких изменений. Такой подход позволяет без существенных трудозатрат добавлять протоколирование операций и версионность записей для любой таблицы. Метод прост в реализации, несложно включается практически в любое приложение, даже если оно не было изначально рассчитано на работу с историческими данными. Еще один, и довольно существенный плюс такого подхода – простота написания запросов для получения состояния записи на любой момент времени.

Допустим, что для приведенных на рис. 1 таблиц потребуется получить состояние записи в таблице Deterioration, имеющей значение первичного ключа RegistrationUnitID=’ ’ на 4 апреля 2004 года, 14:30. Для этого в таблице Deteriration_History нужно найти все записи, что были изменены до этого момента, и среди них найти ту запись, которая была изменена последней. Это и есть состояние записи на заданный момент. Полностью запрос:

select * from Deterioration_History dethist where RegistrationUnitID=''and DateModified'4/4/2004 14:30'andnotexists(select dh.* from Deterioration_History dh where dh.RegistrationUnitID=''and dh.DateModified'4/4/2004 14:30'and dh.DateModified>dethist.DateModified )

В таблице Deterioration_History имеется кластерный индекс по полю RegistrationUnitID, являющемуся первичным ключом в основной таблице, что обеспечивает высокую скорость работы запросов к таблице истории.

Нужно отметить, что производительность, если и снижается, то ненамного. Это связано с тем, что исторические и актуальные данные находятся в разных таблицах, а большинство запросов выполняется к актуальным данным. Таким образом, производительность снижается только за счет некоторого количества обращений к таблицам изменений. Минус, похоже, только один, но существенный – требуется большой объем дискового пространства для хранения таблиц-двойников. Однако этот недостаток можно существенно уменьшить, если хранить не всю историю изменений, а только за некоторый интервал времени, например, за месяц. Если все-таки требуется, чтобы сохранялась информация обо всех изменениях, то необходимо оценить расходы дисковой памяти — возможно, не все так плохо, как кажется. Например, в проекте, где я сейчас занимаюсь базой данных, по расчетам размер базы достигнет 1 Тб только через 5 лет эксплуатации. Значительную часть этого объема будут составлять таблицы изменений. В проекте применен описанный здесь подход – для небольшой части таблиц ведутся версии записей, для основной же части таблиц ведется простая фиксация того, кто и когда изменил запись.

Метод единой таблицы аудита

Довольно часто применяемый метод протоколирования – создание одной таблицы аудита. Пример такой таблицы показан на рис 2. Поля записи имеют следующий смысл:

• UserName – имя пользователя, внесшего изменения.
• OperationDateTime – дата/время изменения.
• TableName – название таблицы, куда было внесено изменение.
• OperationType – тип операции (создание/изменение/удаление).
• OperationData – сведения об операции, необходимые для понимания того, что было изменено.
• PrimaryKeyValue – значение первичного ключа записи, необходимое для идентификации измененной записи.

Здесь я предполагаю, что ключ не составной и имеет тип int. Если имеются составные первичные ключи, или первичный ключ имеет другой тип, несложно в эту схему внести соответствующие изменения. В поле OperationData вносится информация об изменении данных, необходимая для понимания сути изменений. Минимальной информацией является сам факт изменения записи. А можно вносить все измененные поля вместе с их значениями.

Рис. 2. Единая таблица аудита.

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

В одной из реализаций, с которыми мне пришлось столкнуться, при любой операции с таблицами, за которыми ведется наблюдение, в таблицу аудита вносилась запись. В поле «сведения об операции» при создании новой записи заносились все значения полей в формате “название поле=’значение’”, через запятую. Далее, при изменении, в таком же формате вносились значения измененных полей, а при удалении туда помещалось в том же формате все содержимое записи. Сделано все это было на триггерах, приходилось анализировать каждую запись: смотреть, что в ней поменялось, готовить строку для сохранения, сохранять. Разумеется, в случае групповых операций все это работает очень медленно. Например, возникла задача обновить расписание полетов. Оно находилось в двух таблицах, на каждой из которых был включен триггер, ведущий протоколирование. Обновление при включенном протоколировании выполнялось около 5 минут, и несколько секунд с отключенными триггерами – это было на базе Oracle 8.1.7. Думаю, что и на других БД, в том числе SQL Server, такой подход тоже будет медленным. Кроме низкой производительности, такой подход имеет еще один существенный недостаток: очень сложно получить состояние записи на какой-то момент времени. Такой метод хорошо подходит для протоколирования изменений, но не для версионности. Как-то передо мной была поставлена задача по восстановлению состояния записи на произвольный момент в БД, использующей такую схему. Задача не из невозможных, но довольно сложная. Версии записей в такой схеме получаются долго — много вычислений. Был написан метод, который получает текущее состояние записи, а затем последовательно просматривает сведения об изменении этой записи в таблице аудита. По данным, хранящимся в этой таблице, восстанавливается состояние записи на заданный момент.

Однако такая схема все же применяется, так как требует меньше места, чем вариант с таблицами-двойниками. В основных таблицах так же, как и в методе таблиц-двойников, хранятся только актуальные версии записей, что делает запросы по актуальным данным достаточно быстрыми. В целом, метод хорошо подходит для протоколирования изменений БД, где нет массовых операций (или есть возможность отключать протоколирование на время массовых операций), где есть требования экономии дискового пространства и нет необходимости быстро получать состояние записей на произвольный момент времени.

Одной из вариаций этого подхода является разбиение таблицы аудита на две, что показано на рис. 3.

Рис.3. Разделение таблицы аудита на две.

По сравнению с предыдущим подходом с единой таблицей аудита, здесь измененные значения полей вынесены в отдельную таблицу Tbl_LogData – для каждого изменения создается запись в Tbl_Log с пометкой, кто и когда проводил изменение, название таблицы и значение первичного ключа. Затем в таблицу Tbl_LogData заносятся измененные значения полей. Тип sql_variant не позволяет сохранять данные типа image или text, поэтому, если потребуется сохранять и их, то нужно делать дополнительную таблицу для них.

Метод таблиц-версий

Впервые задача по созданию базы с версионностью записей возникла у меня в моем первом проекте БД для корпоративной информационной системы. Одним из требований была возможность получения не только версии какой-то записи, но и версии записей из других таблиц, на которые эта запись ссылается. Каждая таблица, для которой был необходим этот механизм, разбивалась на две: таблицу-заголовок и таблицу версий. Таблица-заголовок — это таблица с неизменяемой частью записи. Запись туда заносится один раз и больше никогда не изменяется, за исключением проставления отметки о логическом удалении. Таблица версий записи содержит набор изменямых атрибутов. При изменении записи создается новая версия, прежняя версия используется только для истории. Для того чтобы ссылки указывали на определенную версию записи, делаем ссылку на таблицу версий записи. Если не нужно получать версию записи, то делаем ссылку на таблицу-заголовок. . На рис. 4. показан пример структуры таблиц при описанном подходе.

Рис. 4. Таблица с неизменяемой частью и таблица версий.

На диаграмме приведены три таблицы. Таблица Tbl_Subject содержит неизменяемую часть данных о контрагенте и является таблицей-заголовком для контрагента; таблица Tbl_SubjectVersion содержит записи с версиями контрагента; Tbl_SupplierOffer – одна из таблиц, содержащая информацию о коммерческих предложениях поставщиков. Как видно, для контрагента было найдено совсем немного неизменяемых атрибутов. На той же диаграмме видны два варианта ссылок: ссылка на таблицу-заголовок – поле ID_HigherSubject в Tbl_SubjectVersion (вышестоящий контрагент для холдинговых структур), и поле ID_SubjectVersion в таблице Tbl_SupplierOffer – ссылка на версию контрагента для коммерческого предложения. Таким образом, мы можем получить версию контрагента на момент занесения записи в Tbl_SupplierOffer. Поле ID_HigherSubject ссылается на таблицу-заголовок, так как нам не нужны версии ссылки для этого поля. Нам нужно получать версию контрагента для коммерческого предложения на тот момент, когда была создана запись, поэтому используется ссылка на версию контрагента. При создании новой версии записи контрагента прежняя версия помечается как удаленная, создается новая версия; при этом все ссылки, которые ссылались на предыдущую версию, остаются неизменными, что позволяет получать состояние записи на момент ввода.

Я не вижу существенных достоинств данного подхода по сравнению с другими. В то же время недостатков у него предостаточно. Объем требуемого дискового пространства не меньше, чем при работе с таблицами-двойниками, а реализация намного сложнее. Ту же самую функциональность легко получить при помощи таблиц-двойников, при сохранении даты изменения. Поскольку с использованием таблиц-двойников мы можем получить состояние записи на любой момент времени, то несложно восстановить состояние записи и всех записей, на которые она ссылается, по состоянию на любой момент времени. Хотя писать запросы при таком подходе проще, поскольку ссылка (если это ссылка на таблицу версий) и так указывает на нужную версию. При использовании таблиц-двойников приходится использовать более сложные запросы .

Я предпочитаю метод с таблицами-двойниками для тех таблиц, где требуется версионность. Если бы я снова проектировал ту структуру БД, то сделал бы все на основе метода таблиц-двойников – та база получилась довольно сложной (около 500 таблиц), и использование таблиц-двойников позволило бы существенно упростить реализацию. При использовании метода таблиц-версий в таблице версий смешиваются исторические и актуальные данные, что ухудшает производительность при выборках – серверу приходится просматривать много лишних записей, особенно при полном сканировании таблицы; ухудшается избирательность индексов, так как значительная часть записей индексов соответствует историческим данным, которые редко используются. Если таблица только с актуальными данными может быть закеширована в памяти, то таблица с актуальными и историческими данными может и не поместиться – и тогда серверу БД придется чаще обращаться к дискам, что сильно снижает скорость.

Метод одной таблицы версий

Этот метод похож на предыдущий – но создается не две таблицы, а одна. Запись при изменении помечается как удаленная, создается новая запись, в ней заполняется поле-ссылка на предыдущую версию записи. На рис. 5 показан пример таблицы для этого метода:

• ExampleID – первичный ключ таблицы;
• IsDeleted – отметка о том, что эта версия помечена как логически удаленная;
• PreviousExampleID – ссылка на предыдущую версию записи.

Рис. 5. Метод со ссылками на предыдущую запись.

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

Oracle и flashback queries

В Oracle поддерживается механизм flashback queries, позволяющий получить состояние записи на любой момент в прошлом, которое еще хранится в логе транзакций БД. Метод требует очень много места, при интенсивной работе с базой обычно можно смотреть данные всего на 10-15 часов назад – для большего не хватает места. Впрочем, если данные редко модифицируются, то такой подход тоже можно использовать.

C2 audit

SQL Server поддерживает c2 audit mode, при этом логируются все действия пользователей. Протокол действий пользователей пишется в файлы, путь к которым указывается в настройках. Из БД простыми средствами к этим файлам не обратишься, да их и непросто анализировать. Думаю, лучше этот метод не применять, если действительно нет необходимости в полном протоколировании всех действий пользователей. Метод основательно ухудшает производительность, пригоден только для протоколирования действий пользователей, но для версионности записей полностью не подходит – предыдущие версии записей, наверное, и в этом подходе можно получить, но это будет очень и очень непросто.

Эта статья опубликована в журнале RSDN Magazine #2-2004. Информацию о журнале можно найти здесь

История изменения данных в таблицах

Второй подход вообще позволяет удалить «значимые» поля из таблицы А, а просто брать последнюю запись из соответствующей таблицы истории. Буду благодарен за любую помощь.

UPDATE

БД — MSSQL

Отслеживать
задан 12 янв 2013 в 7:17
4,202 24 24 серебряных знака 49 49 бронзовых знаков
Может вам поможет бинарный журнал?
12 янв 2013 в 9:13
Используется MSSQL
12 янв 2013 в 10:24
Можно писать логи в таблицу : поле=значение. Вопрос в том, как вам нужно просматривать логи?
12 янв 2013 в 14:59

3 ответа 3

Сортировка: Сброс на вариант по умолчанию

Можно создать таблицу с примерно следующей структурой, заполнением которой будет заниматься триггер. При этом не важно, в какой таблице изменились данные — т.е. можно безболезненно расширяться.

Колонки: id (PK), таблица*, колонка*, предыдущее значение, результирующее значение, дата/время, пользователь. * Таблица, в которой произошло изменение; ** Колонка в этой таблице. 

Отслеживать
ответ дан 12 янв 2013 в 15:05
728 3 3 серебряных знака 8 8 бронзовых знаков

Тогда пару вопросов: 1) Что писать в столбцы «таблица», «колонка»? ID из системных таблиц? 2) Какого типа будут поля «предыдущее значение», «результируещее значение»?

15 янв 2013 в 5:52

1) В столбец «Таблица» — имя таблицы, «Колонка» — колонка той таблицы. Не ID, а их наименование. Например, у вас есть таблицы clients и orders ; в clients колонки id , firstname , middlename , lastname , birthday , в orders — id , number , client , description . Вам нужно отслеживать изменение clients.birthday . В триггере на INSERT/UPDATE будете сохранять в системную таблицу истории изменения реквизитов, например, sys_diary , записи, где «таблица» будет «clients», а колонка — «birthday». 2) Для хранения истории нет необходимости в преобразовании, так что — текст.

Отслеживание изменений данных (SQL Server)

SQL Server предоставляет две функции, которые отслеживают изменения данных в базе данных: отслеживание изменений и отслеживания изменений. Эти функции позволяют приложениям обнаруживать изменения DML (операции вставки, обновления и удаления), произведенные в пользовательских таблицах базы данных. Отслеживание измененных данных и отслеживание изменений можно включить для одной и той же базы данных без каких-либо дополнительных действий. Выпуски SQL Server, поддерживающие отслеживание изменений и отслеживания изменений, см . в выпусках и поддерживаемых функциях SQL Server 2022.

Преимущества использования отслеживания изменений или отслеживания изменений

Возможность выполнять запросы к измененным данным в базе данных — важное условие для эффективной работы некоторых приложений. Как правило, для определения изменений данных разработчики приложений должны реализовать настраиваемый метод отслеживания в своих приложениях с помощью сочетания триггеров, столбцов метки времени и дополнительных таблиц. Создание таких приложений обычно связано с большими трудозатратами, требует обновлений схемы и часто вызывает серьезное снижение производительности.

Использование функций отслеживания измененных данных или отслеживания изменений в приложениях для отслеживания изменений в базах данных, вместо разработки специализированного решения, имеет следующие преимущества.

• Сокращается время разработки. Так как функциональные возможности доступны в SQL Server, вам не нужно разрабатывать пользовательское решение.
• Изменения схемы не требуются. Вам не нужно добавлять столбцы, добавлять триггеры или создавать боковую таблицу, в которой можно отслеживать удаленные строки или хранить сведения об отслеживании изменений, если столбцы не могут быть добавлены в пользовательские таблицы.
• Существует встроенный механизм очистки. Очистка для отслеживания изменений выполняется автоматически в режиме в сети. Настраиваемая очистка данных, хранящихся в боковой таблице, не требуется.
• Для получения сведений об изменениях предоставляются функции.
• Существует низкая нагрузка на операции DML. Синхронному отслеживанию изменений всегда сопутствуют некоторые затраты. Однако отслеживание изменений может помочь снизить затраты. Затраты часто оказываются меньше, чем при использовании альтернативных решений, особенно решений с использованием триггеров.
• Отслеживание изменений основано на зафиксированных транзакциях. Порядок изменений основан на времени фиксации транзакции. Это позволяет получить надежные результаты при наличии долго выполняемых и перекрывающихся транзакций. Пользовательские решения, использующие значения метки времени, должны быть разработаны для обработки этих сценариев.
• Доступны стандартные средства, которые вы можете использовать для настройки и управления. SQL Server предоставляет стандартные инструкции DDL, SQL Server Management Studio, представления каталога и разрешения безопасности.

Различия между отслеживанием изменений и отслеживанием изменений

В следующей таблице приводится список отличий возможностей для системы отслеживания измененных данных и системы отслеживания изменений. Система отслеживания измененных данных задействует асинхронный механизм отслеживания изменений в журнале транзакций, поэтому изменения доступны после выполнения операции DML. В системе отслеживания изменений задействуется синхронное отслеживание изменений параллельно с операциями DML, поэтому данные изменений доступны немедленно.

Компонент	система отслеживания измененных данных	отслеживание изменений
Отслеживаемые изменения
DML-изменения	Да	Да
Отслеживаемые данные
Данные журнала	Да	No
Изменение столбца	Да	Да
Тип DML	Да	Да

система отслеживания измененных данных

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

Как показано на следующем рисунке, изменения в пользовательской таблице записываются в соответствующих таблицах изменений. Эти таблицы изменений предоставляют историческое представление изменений с течением времени. Функции отслеживания измененных данных, которые SQL Server предоставляет, позволяют легко и систематически использовать измененные данные.

Модель безопасности

В этом разделе описана модель безопасности системы отслеживания измененных данных.

Настройка и администрирование

Чтобы включить или отключить запись измененных данных для базы данных, вызывающий sys.sp_cdc_enable_db (Transact-SQL) или sys.sp_cdc_disable_db (Transact-SQL) должен быть членом предопределенной роли системного администратора сервера. Включение и отключение отслеживания измененных данных на уровне таблицы требует, чтобы вызывающий объект sys.sp_cdc_enable_table (Transact-SQL) и sys.sp_cdc_disable_table (Transact-SQL) был членом роли sysadmin или членом роли базы данных db_owner.

Использование хранимых процедур для администрирования заданий отслеживания измененных данных ограничено членами серверной роли sysadmin и членами роли database db_owner .

Изменение запросов перечисления и метаданных

Чтобы получить доступ к измененным данным, связанным с экземпляром записи, пользователю необходимо предоставить доступ SELECT ко всем захваченным столбцам связанной исходной таблицы. Кроме того, если при создании экземпляра записи указана роль gating, вызывающий объект также должен быть членом указанной роли gating, а схема отслеживания измененных данных ( cdc ) должна иметь доступ SELECT к роли gating.

Другие функции сбора общих изменений для доступа к метаданным будут доступны всем пользователям базы данных через общедоступную роль, хотя доступ к возвращаемым метаданным также обычно будет заключен с помощью доступа SELECT к базовым исходным таблицам и путем членства в любых определенных ролях gating.

Операции DDL для изменения исходных таблиц с поддержкой записи данных

Если в таблице включена система отслеживания измененных данных, операции DDL могут применяться только членом предопределенной роли сервера sysadmin, членом роли database role db_ownerили database role db_ddladmin. Для пользователей, которым явно предоставлены права на выполнение DDL-операций в такой таблице, выводится сообщение об ошибке 22914, если они пытаются выполнить такие операции.

Рекомендации по типу данных для отслеживания измененных данных

Система отслеживания измененных данных поддерживает все базовые типы столбцов. В следующей таблице описывается поведение и ограничения нескольких типов столбцов.

Тип столбца	Изменения, отслеживаемые в таблицах изменений	Ограничения
Разреженные столбцы	Да	Не поддерживает запись изменений при использовании набора столбцов.
Вычисляемые столбцы	Нет	Изменения вычисляемых столбцов не отслеживаются. Столбец отображается в таблице изменений с соответствующим типом, но имеет значение NULL.
XML	Да	Изменения отдельных XML-элементов не отслеживаются.
Метка времени	Да	Тип данных в таблице изменений будет преобразован в двоичный.
Типы данных BLOB	Да	Предыдущий снимок столбца BLOB будет сохранен только при изменении самого столбца.

Изменение записи данных и других функций SQL Server

В данном разделе описывается взаимодействие следующих функций с системой отслеживания измененных данных.

• Зеркальное отображение базы данных
• Репликация транзакций
• Восстановление или присоединение базы данных

Зеркальное отображение базы данных

Для базы данных, активированной для отслеживания измененных данных, можно установить зеркальное отображение. Чтобы обеспечить автоматическое выполнение отслеживания и очистки на зеркальном сервере, выполните следующие шаги.

1. Убедитесь, что агент SQL Server выполняется в зеркало.
2. Создайте задание отслеживания и задание очистки на зеркальном сервере после того, как был выполнен переход с основного сервера на зеркальный. Чтобы создать задания, используйте хранимую процедуру sys.sp_cdc_add_job (Transact-SQL).

Дополнительные сведения о зеркало базах данных см. в разделе «Зеркальное отображение базы данных» (SQL Server).

Репликация транзакций

Система отслеживания измененных данных и репликация транзакций могут сосуществовать в одной базе данных, но если обе эти функции были включены, то заполнение таблиц изменений будет выполняться другим способом. Для считывания изменений из журнала транзакций система отслеживания измененных данных и репликация транзакций всегда используют одну и ту же процедуру sp_replcmds. Если запись измененных данных включена самостоятельно, вызовы sp_replcmds заданий агент SQL Server. Если оба компонента включены в одной базе данных, агент чтения журналов вызывает sp_replcmds . Этот агент заполняет таблицы изменений и distribution таблицы базы данных. Дополнительные сведения см. в статье Replication Log Reader Agent.

Рассмотрим случай, когда для базы данных AdventureWorks2022 была включена система отслеживания измененных данных и две таблицы были включены для отслеживания. Для заполнения таблиц изменений задание отслеживания вызывает процедуру sp_replcmds . База данных активируется для репликации транзакций, после этого создается публикация. Для базы данных создается агент чтения журнала, задание отслеживания удаляется. Агент чтения журнала продолжает просматривать журнал, начиная с последнего регистрационного номера транзакции, зафиксированного в таблице изменений. Это обеспечивает согласованность данных в таблицах изменений. Если транзакционные реплика tion отключены в этой базе данных, агент чтения журналов удаляется, а задание записи создается повторно.

Когда агент чтения журналов используется как для отслеживания измененных данных, так и для реплика транзакций, реплика внесенные изменения сначала записываются в distribution базу данных. Затем в таблицы изменений записываются отслеженные изменения. Обе операции фиксируются одновременно. Если в базе данных возникает задержка при записи distribution , то перед изменениями в таблицах изменений появится соответствующая задержка.

Восстановление или присоединение базы данных для отслеживания измененных данных

SQL Server использует следующую логику, чтобы определить, включена ли запись измененных данных после восстановления или подключения базы данных:

• Если база данных восстанавливается на том же сервере с таким же именем базы данных, то система отслеживания измененных данных останется активированной.
• Если база данных восстанавливается на другом сервере, запись измененных данных по умолчанию отключена, а все связанные метаданные удаляются. Для сохранения системы отслеживания измененных данных в активированном состоянии при восстановлении базы данных следует использовать параметр KEEP_CDC . Дополнительные сведения об этом параметре см. в разделе RESTORE.
• Если база данных отсоединяется и присоединяется к тому же или другому серверу, то система отслеживания измененных данных остается активированной.
• Если база данных подключена или восстановлена с KEEP_CDC параметром к любому выпуску, отличному от Standard или Enterprise, операция блокируется, так как для отслеживания измененных данных требуется выпуски SQL Server Standard или Enterprise. Отображается сообщение об ошибке 932.

SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture. 

Процедуру sys.sp_cdc_disable_db можно использовать для отключения отслеживания измененных данных в восстановленной или присоединенной базе данных.

отслеживание изменений

Отслеживание изменений фиксирует тот факт, что строки в таблице были изменены, но не фиксируют измененные данные. Это позволяет приложениям определять изменившиеся строки, получая последние данные изменений непосредственно из пользовательских таблиц. Поэтому журнал системы отслеживания изменений более ограничен по сравнению с системой отслеживания измененных данных. Однако для тех приложений, которые не требуют исторических сведений, гораздо меньше затрат на хранение из-за неуправляемых данных. Для отслеживания изменений используется синхронное средство отслеживания. Оно было разработано, чтобы снизить до минимума объем служебных данных для операций DML.

На следующем рисунке показан сценарий синхронизации, при котором оптимальным является отслеживание изменений. В этом сценарии приложению требуются следующие данные: все строки таблицы, измененные с момента последней синхронизации, и только текущие данные строк. Поскольку для отслеживания изменений используется синхронный механизм, приложение может выполнить двустороннюю синхронизацию и уверенно определить возможные конфликты.

Отслеживание изменений и службы синхронизации для ADO.NET

Службы синхронизации для ADO.NET обеспечивают синхронизацию между базами данных, предоставляя интуитивно понятный и гибкий API, который позволяет создавать приложения, предназначенные для сценариев автономной работы и совместной работы. Службы синхронизации для ADO.NET предоставляют API для синхронизации изменений, но на самом деле не отслеживает изменения в базе данных сервера или одноранговой базы данных. Можно создать нестандартную систему отслеживания изменений, однако обычно это сопряжено со значительным усложнением среды и повышенными затратами ресурсов. Чтобы отслеживать изменения в сервере или одноранговой базе данных, рекомендуется использовать отслеживание изменений в SQL Server, так как легко настроить и обеспечить высокую производительность.

Дополнительные сведения об отслеживании изменений и службах синхронизации для ADO.NET см. по следующим ссылкам:

• Сведения о Отслеживание изменений (SQL Server) Описывает отслеживание изменений, предоставляет общий обзор работы отслеживания изменений и описывает взаимодействие отслеживания изменений с другими функциями SQL Server ядро СУБД.
• Центр разработки Microsoft Sync Framework Предоставляет полную документацию по Службам Sync Framework и Sync Services.

Следующие шаги

Задача	Статья
Содержит общие сведения о системе отслеживания измененных данных.	Отслеживание измененных данных (SQL Server)
Описывает, как включать и отключать отслеживание измененных данных для базы данных или таблицы.	Включение и отключение отслеживания измененных данных (SQL Server)
Описывает, как управлять мониторингом измененных данных и контролировать его.	Администратор и мониторинг отслеживания измененных данных (SQL Server)
Описывает способы работы с информацией об изменениях, доступные потребителям отслеживания информации об изменениях. В этой статье рассматриваются проверки границ LSN, функций запросов и сценариев функций запросов.	Работа с измененными данными (SQL Server)
Содержит общие сведения об отслеживании изменений.	Сведения о Отслеживание изменений (SQL Server)
Описывает, как включать и отключать отслеживание изменений для базы данных или таблицы.	Включение и отключение Отслеживание изменений (SQL Server)
Описывает, как управлять отслеживанием изменений, настраивать безопасность и определять влияние использования отслеживания изменений на производительность и требуемый объем хранилища.	Управление Отслеживание изменений (SQL Server)
Описывает, как приложения, использующие отслеживание изменений, могут получать отслеженные изменения, применять эти изменения к другому хранилищу данных и обновлять базу данных-источник. В этой статье также описывается отслеживание изменений ролей при отработки отказа, а база данных должна быть восстановлена из резервной копии.	Работа с Отслеживание изменений (SQL Server)

См. также

• Функции отслеживания измененных данных (Transact-SQL)
• функции Отслеживание изменений (Transact-SQL)
• Хранимые процедуры отслеживания измененных данных (Transact-SQL)
• Изменение таблиц отслеживания данных (Transact-SQL)
• Изменение связанных динамических административных представлений отслеживания данных (Transact-SQL)