Отслеживание изменений или аудит данных сегодня одна из главных задач программиста, который разрабатывает базу данных или ее дорабатывает. И сегодня мы будем разговаривать именно об этом, но использовать мы будем не распространенный вариант с использованием триггеров, а по средствам одной процедуры, которую можно интегрировать с другими процедурами, так как иногда требуется отслеживать изменения, которые происходят во время выполнения определенной процедуры и триггер нам здесь не поможет.
И для начала хотелось бы сказать, что все примеры мы будем рассматривать на базе данных 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)
И тем самым Вы получите все изменения по данному столбцу, конечно дополнив запрос еще нужными Вам полями.
И на сегодня это все! Надеюсь, представленная выше информация, будет Вам полезна. Удачи! До новых встреч на сайте «Программирование для начинающих».
