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

Откуда:
Сообщений: 10
Помогите написать триггер UPDATE для MS SQL 2005.
Суть задачи, прикрутить к базе логирование изменяющихся значений колонок одной из таблиц. Причем одна запись в таблицу лога – имя измененного столбца и его новое значение, хотя в одном UPDATE могут меняться несколько значений колонок.
Т.е. не так как это сделано здесь в 9 примере: https://www.sql.ru/articles/mssql/2005/030701ChangesLogging.shtml#35
В примере таблица лога arch_t1 повторяется структура таблицы t1.
Хочется, чтоб структура таблицы лога была:
create table arch_t1(
    id             int identity,
    id_t1          int,
    [column]       varchar(10),
    new_values     varchar(10)
    )

В клиентской программе обновление происходит вида
UPDATE t1 set f1=’новое значение1’, f2=f2, f3=’новое значение2’, f4=f4 where id=1

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

Я как понимаю надо в том примере как-нибудь прикрутить перебор всех полей таблицы select sc.name from syscolumns sc where id = object_id('t1'), сравнивая их со значениями соответствующих полей из таблиц inserted и deleted, и вот это не знаю, как сделать.


if (object_id('t1') is not null)
    drop table t1
if (object_id('arch_t1') is not null)
    drop table arch_t1

create table t1(
    id    int primary key,
    f1    varchar(10),
    f2    varchar(10),
    f3    varchar(10)
)

create table arch_t1(
    id             int identity,
    id_t1          int,
    [column]       varchar(10),
    new_values            varchar(10)
    )
go

create trigger trg_t1_u on t1 for update
as
begin
--insert into arch_t1 (id_t1,sc.name,new_value)
--select sc.name from syscolumns sc where id = object_id('t1')

--from
--        inserted i
--        inner join deleted d on i.id = d.id
--    where
--        (i.f1 <> d.f1)
--        or (i.f2 <> d.f2)

--как здесь написать?
end

go
insert into t1 (id, f1, f2, f3) values (1, '123', 'qwerty','asdfg')
insert into t1 (id, f1, f2, f3) values (2, '456', 'qwerty', null)


update t1 set f1 = '123', f2='qwerty' where id = 1
update t1 set f1 = '456', f2='qwerty' where id = 1
update t1 set f1 = '789', f3='asdfg' where id = 2


select * from arch_t1

В результате хочу получить в arch_t1:
id id_t1 [column] new_value
1 1 f1 456
2 2 f1 789
3 2 f3 asdfg

В качестве типов переменных полях в основном varchar, но есть несколько int, их можно привести к varchar.
18 июл 11, 03:15    [10987106]     Ответить | Цитировать Сообщить модератору
 Re: Логирование реальных изменений данных в триггере  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3265
mfox,

Писать в лог со структурой оригинальной таблицы + поля кто это сделал , когда и что U I D. Всё остальное от лукавого.
18 июл 11, 08:36    [10987264]     Ответить | Цитировать Сообщить модератору
 Re: Логирование реальных изменений данных в триггере  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
как-то так

create trigger trg_t1_u on t1 for update
as
begin
	insert into #arch_t1 (id_t1,[column],new_values)
	select
		id id_t1
		, [column]
		, new_values
	from
		(
		select 
			i.id
			, case when isnull(i.f1, '') <> isnull(d.f1, '') then i.f1 else null end f1
			, case when isnull(i.f2, '') <> isnull(d.f2, '') then i.f2 else null end f2
			, case when isnull(i.f3, '') <> isnull(d.f3, '') then i.f3 else null end f3
		from
			inserted i
		inner join deleted d on i.id = d.id) pvt
	unpivot (new_values for [column] in (f1, f2, f3)) unpvt

end
18 июл 11, 09:12    [10987369]     Ответить | Цитировать Сообщить модератору
 Re: Логирование реальных изменений данных в триггере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
Ken@t
mfox,

Писать в лог со структурой оригинальной таблицы + поля кто это сделал , когда и что U I D. Всё остальное от лукавого.
+1

mfox
Я как понимаю надо в том примере как-нибудь прикрутить перебор всех полей таблицы select sc.name from syscolumns sc where id = object_id('t1'), сравнивая их со значениями соответствующих полей из таблиц inserted и deleted, и вот это не знаю, как сделать.
Для вашей модели данных нужно написать запросы по количеству полей в таблице, или использовать UNPIVOT
mfox
Т.е. не так как это сделано здесь в 9 примере: https://www.sql.ru/articles/mssql/2005/030701ChangesLogging.shtml#35
Там есть Пример 4, как раз то что вам надо.
18 июл 11, 09:17    [10987381]     Ответить | Цитировать Сообщить модератору
 Re: Логирование реальных изменений данных в триггере  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
На на 99% не нужно ТС-у знать, что на что поменяли.
Скорее всего ему надо знать кто, под каким логином, с какого компа в сети изменил какую табличку + PK записи.

Человек скорее всего финансовую систему пишет.

Тогда пусть будет готов раз за ночь, перепаковывать эти логи (с жОсткой нормализацией), и выносить их в другую базу.
Иначе, пара недель, и боевая база тормозить начнет из-за миллионов записей, которые из триггеров генерятся.
19 июл 11, 04:29    [10992602]     Ответить | Цитировать Сообщить модератору
 Re: Логирование реальных изменений данных в триггере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
Makar4ik
На на 99% не нужно ТС-у знать, что на что поменяли.
Скорее всего ему надо знать кто, под каким логином, с какого компа в сети изменил какую табличку + PK записи.
Ну, обычно желательно такие вещи знать, контроль для этого и делается.
Makar4ik
Тогда пусть будет готов раз за ночь, перепаковывать эти логи (с жОсткой нормализацией), и выносить их в другую базу.
Иначе, пара недель, и боевая база тормозить начнет из-за миллионов записей, которые из триггеров генерятся.
Что вы выдумываете???

Если сделать правильно:
Ken@t
Писать в лог со структурой оригинальной таблицы + поля кто это сделал , когда и что U I D. Всё остальное от лукавого.
то всё будет летать, и уж за десяток лет хранить можно без проблем. Писаться тоже, понятно, будет без проблем.

Какие то странные у вас представления о сиквеле. Как это скорость записи может зависеть от того, сколько времени писали??? Совершенно независимые величины, хоть 2 недели писать, хоть 200 лет.
19 июл 11, 08:45    [10992739]     Ответить | Цитировать Сообщить модератору
 Re: Логирование реальных изменений данных в триггере  [new]
mfox
Member

Откуда:
Сообщений: 10
Спасибо всем за ответы, особенно HandKot.
4 пример проглядел.

Цель такого лога, не столько контроль кто сделал изменения, а чтобы в клиентской программе на форме с карточкой объекта выводилась информация, какие действия производились с объектом и какие параметры были до изменения.
Например, учет компьютеров: добавили памяти, поменяли процессор, сменился человек за которым был записан компьютер, все эти изменения в кратком виде выведутся в комментарии к этому компьютеру.
20 июл 11, 09:07    [10998045]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить