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

DennisL
Дата: 16.03.2001 14:17:13
Может у кого есть готовое решение для журнализации изменений полей таблицы (т.к. мне кажеться это используеться довольно часто ...)

Задача :
Есть одна таблица в ней куча всяких полей (Main_id и т.д.), есть вторая таблица в виде :
Change_id
Main_id
FieldName
OldValue varchar(200) - размер самого большого поля в таблице 1.
NewValue varchar(200)
ChangeDate
ChangeUser
Так вот нужен триггер на UPDATE первой таблици кторый бы заполнял автоматически вторую, DELETE из первой таблицы не разрешены, а INSERT обрабатываеться отдельно !

Как я понимаю надо брать имена столбца из какойто системной таблицы и т.д. но нормального решения пока придумать не могу !
victor
Дата: 16.03.2001 14:27:22
Готового решения нет, но могу посоветовать посмотреть примеры с функцией "IF UPDATED(column)"
DennisL
Дата: 16.03.2001 14:39:42
IF UPDATE конечно здесть надо будет использовать или COLUMNS_UPDATED() но сама загвоздка как организовать проверку всех столбцов особенно если их много например больше 64 !

Кстсти по ходу вопрос по IF UPDATE(а) при INSERT он будет TRUE если значение для а задано и FALSE если нет ?
SergSuper
Дата: 16.03.2001 16:11:58
описание колонок таблицы можно взять из syscolumns.
придеться генерировать скрипты и запускать их EXECом
IF UPDATED можно использовать, но оно проверяет не реальное изменение полей, а написано ли это поле в операторе UPDATE(или INSERT), который вызвал триггер.

получиться что-то типа

select
'insert SecondTable(FieldName ,OldValue,NewValue ) select "'+name+'", d.'+name+',i.'+name+' from deleted d, inserted i where d.'+name+'<>i.'+name+' and привязать к основной таблице'
into #t
from syscolumns
where id=object_id('FirstTable')

ну а потом выполнить все строки таблицы #t

я, чесно говоря, сначала подумал что это невозможно и лучше написать для каждого поля по отдельности, но потом посмотрел что 64 поля...
alexeyvg
Дата: 16.03.2001 16:58:58
Для таких случаев "лучше написать для каждого поля по отдельности, но потом посмотрел что 64 поля" могу посоветовать пользоваться темплейтами в ErWin. У меня у таблиц в ErWin-овской схеме есть свойство Audit, если его включить, то в триггер вставляется соотв.текст. Что-то вроде:
insert tblAudit( taTable, taRowID, taAction, taUser, taHost, taDate, oldData, newData )
select '%TableName', t.%PK(), '%Action', SYSTEM_USER, HOST_NAME(), GetDate(),
%if(%==(%Action,UPDATE)){%if(%!=(%TableProp(AuditData),)){%TableProp(AuditData), %Substitute(%TableProp(AuditData),t,i)}%else{null, null}
from inserted i, deleted t
where %JoinPKPK(i,t," = "," and")}
%if(%==(%Action,INSERT)){
%if(%!=(%TableProp(AuditData),)){null, %TableProp(AuditData)}%else{null,null}
from inserted t}
%if(%==(%Action,DELETE)){
%if(%!=(%TableProp(AuditData),)){%TableProp(AuditData),null}%else{null,null}
from deleted t}
Естественно, это надо изменить в зависимости от потребностям, но идея понятна. У меня колонки для аудита указываются в св-ве AuditData, но можно сделать и цикл %ForEachAtt(<table>,<separator>,<sort order> { <macro code> }
DennisL
Дата: 16.03.2001 18:12:04
К сожалению я не очень понял с темплейтами (честно говоря я вообще не знаю что это такое и как они применяютья в SQL SERVER)
Если не сложно объясните популярнее
SergSuper , в вашем примере я тоже не могу уловить сому идею можно по подробнее ...

Заранее спасибо, за сответы !
Garya
Дата: 16.03.2001 19:13:46
Могу подсказать красивое решение для SQL2000. Заводится две таблицы одинкаовой структуры. В одной - только актуальные записи, во второй - журнал (включая удаленные, измененные, добавленные). К первой таблице цепляются Instead-триггеры, которые прежде чем что-либо изменить или удалить в основной таблице, сбрасывают во вторую прежнюю копию, заодно в служебных полях отмечая время модификаци, кто изменил, сетевое имя компьютера и т.д.
Сохраняется всегда вся строка целиком, и не надо голову ломать над каждым полем или совокупностью полей.
DennisL
Дата: 19.03.2001 10:05:10
2 DennisL

Сделайте такой триггер у таблицы

create trigger on имя_таблицы for update
as
select
'insert SecondTable(FieldName ,OldValue,NewValue ) select "'+name+'", d.'+name+',i.'+name+' from deleted d, inserted i where d.'+name+'<>i.'+name+' and привязать к основной таблице'
into #t
from syscolumns
where id=object_id('имя_таблицы')
select * from #t

и в QA попробуйте проапдейтить таблицу

если и после этого будет ничего не понятно, пришлите по почте структуру таблиц, я напишу полный текст триггера(мне это будет не сложно, займет не более 10 мин)

с приветом Сергей
sergsuper@mail.ru
Павел
Дата: 19.03.2001 12:42:45
А не проще создать трейс на необходимые обьекты и события, и сваливать результат в таблицу?