Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Логирование изменений данных  [new]
jsmirnoww
Member

Откуда: Москва
Сообщений: 132
Предстоит написать сабж для большого кол-ва таблиц, в таблицы периодически (не реже 1раз/неделю) добавляются/удаляются поля. И нужен откат на состояние в любую дату.

Задумал такую штуку: сделать табличку ( <Таблица> , <Поле> , <Дата> , <Значение SQL_VARIANT>); и универсальный триггер, который пробегался бы по всем полям измененной записи и скидывал в эту табличку; механизм восстановления - дело техники.

Пытался поставить вопрос здесь (https://www.sql.ru/forum/actualthread.aspx?tid=675950) более частным образом - ответа не получил. Может подскажете как это реализовать?
30 июн 09, 09:33    [7357242]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
iiyama
Member

Откуда:
Сообщений: 642
в такой постановке задаче в указанной таблице не забудьте указать первичный ключ
30 июн 09, 10:04    [7357370]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
Taffy
Member

Откуда:
Сообщений: 20498
1. Одна таблица логов на все таблицы данных???
2. Напишите скрипт восстановления неправильно удаленных данных
3. Напишите скрипт анализа изменений данных но не в таблице, а в клиентском приложении (где чаще всего одно изменение делается в нескольких связанных таблицах)
4. Где ИД изменяемой строки?
5. Где автор изменений и хоста с которого пришли изменения?

Может ответы на эти вопросы Вам помогут построить систему логирования?
30 июн 09, 10:38    [7357529]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
jsmirnoww
Member

Откуда: Москва
Сообщений: 132
Taffy
1. Одна таблица логов на все таблицы данных???

Скорее две. одна - общая инфа о факте изменения (таблица, кто, когда, что сделал.) вторая - примерно та, что я показал - с данными из полей.

Taffy
2. Напишите скрипт восстановления неправильно удаленных данных

написал.

Taffy
3. Напишите скрипт анализа изменений данных но не в таблице, а в клиентском приложении (где чаще всего одно изменение делается в нескольких связанных таблицах)

Мне анализ изменений не нужен, тем более в клиенте. Нужна история изменений по таблицам.

Taffy
4. Где ИД изменяемой строки?

ID обязательно будет. :)


Taffy
5. Где автор изменений и хоста с которого пришли изменения?

Тоже обязательно будет.

Taffy
Может ответы на эти вопросы Вам помогут построить систему логирования?

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

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

Надеюсь, на реальную помощь. Думаю подобный механизм помогбы не мне одному.
30 июн 09, 11:48    [7358031]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
А это уже читали?
30 июн 09, 11:55    [7358090]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
jsmirnoww
Member

Откуда: Москва
Сообщений: 132
Спасибо за интересную статью. Я выбрал первый способ из описанных в ней. но там нет ответа на мой чисто технический вопрос.
1 июл 09, 14:05    [7363426]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
Taffy
Member

Откуда:
Сообщений: 20498
Например так
на каждую таблицу вешаете триггер - в котором
записываете данные из inserted и deleted во временные таблички, и вызываете хранимую процедуру в которую передаете название таблицы на которую сработал триггер,
в хранимой процедуре из системной информации берете список полей по названию таблицы, и пишете динамический запрос, который сравнивал бы указанные поля во временных табличках. Те которые разные и пишутся в ваш лог.
1 июл 09, 15:30    [7364013]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
jsmirnoww
Member

Откуда: Москва
Сообщений: 132
Taffy
Например так
на каждую таблицу вешаете триггер - в котором
записываете данные из inserted и deleted во временные таблички, и вызываете хранимую процедуру в которую передаете название таблицы на которую сработал триггер,
в хранимой процедуре из системной информации берете список полей по названию таблицы, и пишете динамический запрос, который сравнивал бы указанные поля во временных табличках. Те которые разные и пишутся в ваш лог.


Во! то что надо! сегодня постараюсь попробовать. спасибо
1 июл 09, 15:42    [7364082]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
Saller
Member

Откуда: exUSSR
Сообщений: 1141
Может поможет, когда-то тоже нужно было написать триггер для аудита:
1 июл 09, 16:30    [7364499]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
Saller
Member

Откуда: exUSSR
Сообщений: 1141
ALTER TRIGGER [dbo].[TRGA] ON [dbo].[Название_Таблицы]
   AFTER UPDATE, DELETE
AS 
BEGIN
declare @tab_name nvarchar(150)
declare @column_name varchar(150)
declare @id_column int
declare @id_old int
declare @id_new int
declare @old_val nvarchar(4000)
declare @new_val nvarchar(4000)
declare @SQLSTR Nvarchar(4000)

set @tab_name = 'Таблица'

SELECT COLUMN_NAME, 
	   COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID
into #columns
FROM База.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tab_name

select * into #c_deleted 
from deleted 

select * into #c_inserted
from inserted

declare cur_columns cursor for
select * from #columns

open cur_columns
fetch cur_columns into @column_name, @id_column
WHILE (@@FETCH_STATUS=0) BEGIN

if @id_column = 1
	begin
		set @SQLSTR = N'SELECT @new_val = '+@column_name+' from #c_inserted'
		EXEC sp_executesql @SQLSTR, N'@new_val as varchar(4000) output', @new_val output
		set @id_new = @new_val

		set @SQLSTR = N'SELECT @old_val = '+@column_name+' from #c_deleted'
		EXEC sp_executesql @SQLSTR, N'@old_val as varchar(4000) output', @old_val output
		set @id_old = @old_val
	end

	begin
		set @SQLSTR = N'SELECT @old_val = '+@column_name+' from #c_deleted'
		EXEC sp_executesql @SQLSTR, N'@old_val as varchar(4000) output', @old_val output
		
		set @SQLSTR = N'SELECT @new_val = '+@column_name+' from #c_inserted'
		EXEC sp_executesql @SQLSTR, N'@new_val as varchar(4000) output', @new_val output

/* Редактирование записи ============================================================================================================*/
		if (@id_new is not null and @id_old is not null)
			begin
				if (@new_val <> @old_val) or (@new_val is not null and @old_val is null) or (@new_val is null and @old_val is not null)
					insert into audit (action, tab_name, field_name, id, old, new)
								values('U', @tab_name, @column_name, @id_new, @old_val, @new_val)
			end
/*===================================================================================================================================*/

/* Удаление записи ==================================================================================================================*/
		if @id_new is null and @id_old is not null
			begin
				insert into audit (action, tab_name, field_name, id, old, new)
							values('D', @tab_name, @column_name, @id_old, @old_val,@new_val)		
			end
/*===================================================================================================================================*/

/* Добавление записи ================================================================================================================*/
--		if @id_new is not null and @id_old is null
--			begin
--				insert into audit (action, tab_name, field_name, id, old, new)
--							values('I', @tab_name, @column_name, @id_old, @old_val,@new_val)		
--			end
/*===================================================================================================================================*/
	end

FETCH cur_columns INTO @column_name, @id_column
END 
CLOSE cur_columns
DEALLOCATE cur_columns

END
Работает для любой таблицы
1 июл 09, 16:32    [7364518]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
jsmirnoww
Member

Откуда: Москва
Сообщений: 132
Saller
Может поможет, когда-то тоже нужно было написать триггер для аудита:

Спасибо, помогло :)
8 июл 09, 10:34    [7389904]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Saller,

во-первых, Ваше решение справедливо только для MSSQL > 2005 (свойство 'ColumnID'). Универсальнее, например, 'AllowsNull'
во-вторых, предполагается, что изменили/удалили только одну запись? Я что-то не вижу, как Вы связываете deleted и inserted? Курсор же не по строкам, а по колонкам?
8 июл 09, 11:03    [7390088]     Ответить | Цитировать Сообщить модератору
 Re: Логирование изменений данных  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31209
iap
Saller,

во-первых, Ваше решение справедливо только для MSSQL > 2005 (свойство 'ColumnID'). Универсальнее, например, 'AllowsNull'
во-вторых, предполагается, что изменили/удалили только одну запись? Я что-то не вижу, как Вы связываете deleted и inserted? Курсор же не по строкам, а по колонкам?
Там просто записываются значения колонок и ид записей, причём предполагается, что ид - это всегда только одна колонка, причём с системным идентификатором, равным "1" :-)
8 июл 09, 12:31    [7390816]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить