Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Журналирование изменений БД с помощью тригеров  [new]
zvezda_t
Member

Откуда: Урал
Сообщений: 919
Здравствуйте, уважаемые программисты!

Подскажите мне пожалуйста, как правильно делают журнализацию с помощью тригеров?
Если можно небольшой примерчик)

Заранее, огромное спасибо!!!
2 июн 11, 20:57    [10755713]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
zvezda_t
Member

Откуда: Урал
Сообщений: 919
Есть таблицы, которые изменяют пользователи.

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

journal
date_change id_user old_value new_value
2 июн 11, 21:06    [10755727]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Ищите посиком, обсуждалось. Даже статься есть на эту тему.
2 июн 11, 21:10    [10755738]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
zvezda_t
Member

Откуда: Урал
Сообщений: 919
В полях old_value и new_value
Я хочу сохранить значения изменяемой записи в виде:
"имя_поля1: значение1, имя_поля2: значение2, имя_поля3: значение3"

Например таблица
student
lastnamenamebirthdayfaculty


В MS SQL при создании поля таблицы можно заполнить описание поля. А как получить это значение в запросе? Например чтобы записать туда названия полей по русски, а потом использовать для формирования записи (old_value и new_value) таблицы journal.
Чтобы в итоге получить:

date_change id_user old_value new_value
03.06.20111Фамилия:Иванов Имя:Иван Дата Рождения:23.03.82 Факультет:Исторический Фамилия:Петров Имя:Сергей Дата Рождения:13.03.8й Факультет:Физический
2 июн 11, 21:22    [10755794]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
Журналирование изменений структуры БД и данных

Не понимаю, зачем хранить "значение после изменения", если оно и так хранится в самой таблице?
2 июн 11, 21:28    [10755809]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
zvezda_t
Member

Откуда: Урал
Сообщений: 919
iap,

потому что может быть несколько последовательных изменений от разных пользователей, и необходима история, о том кто и что менял.
2 июн 11, 21:41    [10755858]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
zvezda_t,

автор
Я хочу сохранить значения изменяемой записи в виде:
"имя_поля1: значение1, имя_поля2: значение2, имя_поля3: значение3"



Лучше не надо, ибо не взлетит. Уже все украдено до Вас придумано за Вас:

Change Data Capture
2 июн 11, 21:43    [10755872]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
zvezda_t
Member

Откуда: Урал
Сообщений: 919
pkarklin, спасибо за ссылку!
А у меня MS SQL 2005. там такая Система Отслеживания Изменения Данных есть?
И как ей пользоваться? Как то не понятно...
3 июн 11, 07:27    [10756636]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
zvezda_t,

В 2005 нет.
3 июн 11, 08:11    [10756705]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
zvezda_t
Member

Откуда: Урал
Сообщений: 919
Вот такой триггер у меня получился.
Только я не знаю как можно перечислить поля таблицы, не обращаясь к ним по имени.
Не очень рационально, для каждого поля функцию вызывать.
Научите пожалуйста, как правильно сделать?

ALTER TRIGGER [dbo].[trg_student_u] 
   ON  [dbo].[student]
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
		
	INSERT INTO dbo.history_jurnal 
    (id_user,id_action,date_action,description_action)
     SELECT user_change,1,GetDate(),
    (SELECT 'Изменение данных <Студента>
Старые значения:
    '+convert(varchar, property1.value) + ': ' + del.lastname +'
    '+convert(varchar, property2.value) + ': ' + del.name+ '
Новые значения:
    '+convert(varchar, property1.value) + ': ' + ins.lastname +' 
    '+convert(varchar, property2.value) + ': ' + ins.name
	FROM 
	fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'student', 'column', 'lastname') as property1,
	fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'student', 'column', 'name') as property2,
	inserted as ins, deleted as del),    
    FROM INSERTED
	
END
3 июн 11, 14:43    [10759323]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
zvezda_t, 10755809
3 июн 11, 14:57    [10759436]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
Glory
Member

Откуда:
Сообщений: 104751
zvezda_t
Научите пожалуйста, как правильно сделать?

какова польза от вашего текстового поля, где все свалено в кучу ?
Как из него узнать, например, когда поле lastname поменяло значение с Иванов на Петров ?
Что делать с полями типа integer/decimal/datetime ?
3 июн 11, 15:14    [10759613]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
zvezda_t
Member

Откуда: Урал
Сообщений: 919
Glory
какова польза от вашего текстового поля, где все свалено в кучу ?

[/quot]
Не всё, а только значения.

Glory
Как из него узнать, например, когда поле lastname поменяло значение с Иванов на Петров ?

так есть же GetDate(), по ней смотрим когда запись изменилась. Изменение по полям, сделать наверно очень сложно, это нужно отслеживать в старой и новой записи одинаковые ли поля.

Glory
Что делать с полями типа integer/decimal/datetime ?

так же записывать их можно сделать convert(varchar) не получится?
3 июн 11, 15:49    [10759891]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
Glory
Member

Откуда:
Сообщений: 104751
zvezda_t
так есть же GetDate(), по ней смотрим когда запись изменилась. Изменение по полям, сделать наверно очень сложно, это нужно отслеживать в старой и новой записи одинаковые ли поля.

особенно удобно искать такое изменение в строке
Изменение данных <Студента>
Старые значения:
field1:value1
field2:value2
field3:value3
field4:value4
field5:value5
field6:value6
field7:value7
field8:value8
field9:value9
field10:value10
field11:value11
field12:value12
field13:value13
Новые значения:
field1:value1
field2:value2
field3:value3
field4:value4
field5:value5
field6:value7
field7:value6
field8:value8
field9:value9
field10:value10
field11:value11
field12:value12
field13:value13

И это я еще закрыл глаза на декартово произведение таблиц inserted/deleted в вашем запросе

zvezda_t
так же записывать их можно сделать convert(varchar) не получится?

И к какому формату будет приводится datetime ?
А float ?

Вы заново пытаетесь изобрести колесо ? Только квадратное ?
3 июн 11, 17:27    [10760828]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
zvezda_t
Вот такой триггер у меня получился.
Ужас.
Вы вообще понимаете что делаете? Зачем этот генератор томов "Война и Мир"?

Решение должно не удовлетворять (абы как) поставленной задаче, а чётко достигать поставленные цели (и ничего более).
Задача понятна - журналирование, цель - не ясна.

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

Если цель, тупо отображать изменения в клиенте - решение одно. Если цель, возможность построение эффективных запросов по данным изменениям - решение другое. И т.д.
Профессионал Обычный программист пытается найти эффективное решение для всех текущих целей и вероятно будущих. Ибо знает КАК.
4 июн 11, 01:51    [10762428]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
davim
Member

Откуда:
Сообщений: 11
zvezda_t, как вариант можно сохранять значения таблиц inserted и deleted соответственно в полях newvalues и oldvalues в xml виде.
CREATE TRIGGER [dbo].[trg_student_u] 
   ON  [dbo].[student]
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
		
	INSERT INTO dbo.history_jurnal 
    (id_user,id_action,date_action,newvalues,oldvalues)
     SELECT user_change
		, 1
		, GetDate()
		, (select * from inserted for xml path(''), type)
                , (select * from deleted for xml path(''), type)
	
END

Вытаскивать запросами данные из xml гораздо проще чем из неструктурированной текстовой строки. Да и по производительности это гораздо эффективнее.
5 июн 11, 01:28    [10764246]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
zvezda_t
iap
Не понимаю, зачем хранить "значение после изменения", если оно и так хранится в самой таблице?
потому что может быть несколько последовательных изменений от разных пользователей, и необходима история, о том кто и что менял.
Зачем мне холодильник если я не курю
+ Для тех кто в танке:
1. Создали строку А в Т1
2. Изменили А на Б в Т2
3. Изменили Б на В в Т3
4. Удалили В в Т4
--------------------------------------------------------------
У вас в логах в итоге получилось:
ВремяЗначение
Т1А
Т2А
Т2Б
Т3Б
Т3В
Т4В
Или:
ВремяСтароеНовое
Т1А
Т2АБ
Т3БВ
Т4В

--------------------------------------------------------------
О чём толкует iap:
1. Создали
Таблица:
ВремяЗначение
T1A
Лог:
ВремяЗначение

2. Изменили А на Б
Таблица:
ВремяЗначение
T2Б
Лог:
ВремяЗначение
T1A

3. Изменили Б на В
Таблица:
ВремяЗначение
T3В
Лог:
ВремяЗначение
T1A
T2Б

4. Удалили
Таблица:
ВремяЗначение
Лог:
ВремяЗначение
T1A
T2Б
T3В
T4

Где, <T> - время + пользователь (и всякая другая инфа).

Ещё можно так:
Таблица:
ВремяЗначениеУдалено
T4ВTrue
Лог:
ВремяЗначение
T1A
T2Б
T3В
5 июн 11, 05:09    [10764285]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
zvezda_t
Member

Откуда: Урал
Сообщений: 919
Glory, я понимаю что не права...
Извините меня пожалуйста, что показываю такой не правильный код.
Я показала этот триггер, чтоб объяснить что я хочу получить.
Вы конечно, правы и хранить всё текстовым полем не нужно. А как хранить правильно, я пока не знаю.

Mnior
автор
Если цель, тупо отображать изменения в клиенте - решение одно.

Да, цель именно такая.

автор
Обычный программист пытается найти эффективное решение для всех текущих целей и вероятно будущих.

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

P.S. Огромное спасибо Всем, кто откликнулся в моей теме!
6 июн 11, 10:05    [10767474]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
zvezda_t
Теперь хочу попробовать хранить в xml формате, но как в этом случае хранить даты, тоже еще не знаю.
Так это это делает сам (FOR) XML без всяких Convert. В xml форматы типов данных чётко определены. Xml это не текст, а структура, которую можно отобразить в виде текста определённого формата.
Читайте доки.

А может 2008 и мудохаться.
6 июн 11, 12:47    [10768961]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Журналирование изменений БД с помощью тригеров  [new]
Лифчик
Member

Откуда:
Сообщений: 1699
davim
zvezda_t, как вариант можно сохранять значения таблиц inserted и deleted соответственно в полях newvalues и oldvalues в xml виде.
CREATE TRIGGER [dbo].[trg_student_u] 
   ON  [dbo].[student]
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
		
	INSERT INTO dbo.history_jurnal 
    (id_user,id_action,date_action,newvalues,oldvalues)
     SELECT user_change
		, 1
		, GetDate()
		, (select * from inserted for xml path(''), type)
                , (select * from deleted for xml path(''), type)
	
END


Вытаскивать запросами данные из xml гораздо проще чем из неструктурированной текстовой строки. Да и по производительности это гораздо эффективнее.
А не подскажете, как вместо select * в журнал помещать только измененные данные, т.е. сформировать Columnlist с колонками, по которым inserted и deleted отличаются?
Заранее благодарен.
18 июн 13, 11:00    [14446402]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
Лифчик
Member

Откуда:
Сообщений: 1699
SQL-2005
18 июн 13, 11:00    [14446406]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
Лифчик
А не подскажете, как вместо select * в журнал помещать только измененные данные, т.е. сформировать Columnlist с колонками, по которым inserted и deleted отличаются?
Это довольно сложно, поскольку для каждой строки в inserted и deleted этот Columnlist будет разный.
18 июн 13, 11:11    [14446471]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
Лифчик
Member

Откуда:
Сообщений: 1699
alexeyvg
Лифчик
А не подскажете, как вместо select * в журнал помещать только измененные данные, т.е. сформировать Columnlist с колонками, по которым inserted и deleted отличаются?
Это довольно сложно, поскольку для каждой строки в inserted и deleted этот Columnlist будет разный.
А если одна строка (допустим на клиенте запретим редактировать несколько сразу)?
Ну или вообще - какие рекомендации будут - чисто интуитивно кажется, что в журнале будет много лишней информации, а я по ментальности экономный и даже свет в офисе выключаю, если вижу, что зря горит
18 июн 13, 11:22    [14446509]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
Лифчик
alexeyvg
Это довольно сложно, поскольку для каждой строки в inserted и deleted этот Columnlist будет разный.
А если одна строка (допустим на клиенте запретим редактировать несколько сразу)?
тогда можно сделать динамический SQL...
Ну или как то средствами сиквельного XML, может кто нибуть подскажет, кто в этом разбирается лучше меня :-)

Естественно, нужно делать в триггере проверку и откатывать транзакцию, если изменились более чем одна строка. А то все говорят - "в клиенте запретим", а потом... :-)
Лифчик
Ну или вообще - какие рекомендации будут - чисто интуитивно кажется, что в журнале будет много лишней информации, а я по ментальности экономный и даже свет в офисе выключаю, если вижу, что зря горит
Для экономии лучше делать таблицы - копии логируемых таблиц с добавлением времени и пользователя. Это много лишней информации, но для сервера это несравнимо легче всех этих парсингов и преобразований.

Хотя конечно всё зависит от конкретных требований и системы. Если например есть широкая таблица с неизменяемыми полями, но одно поле постоянно и очень часто меняется (какой нибуть статус, остаток и т.п.), то можно сделать лог-таблицу и одним этим полем (плюс поля ИД, времени и пользователя)
18 июн 13, 11:46    [14446677]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование изменений БД с помощью тригеров  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Лифчик
alexeyvg
пропущено...
Это довольно сложно, поскольку для каждой строки в inserted и deleted этот Columnlist будет разный.
А если одна строка (допустим на клиенте запретим редактировать несколько сразу)?
Ну или вообще - какие рекомендации будут - чисто интуитивно кажется, что в журнале будет много лишней информации, а я по ментальности экономный и даже свет в офисе выключаю, если вижу, что зря горит


т.е. после поджога офиса вы выключаете свет
18 июн 13, 12:23    [14446991]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить