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

Откуда:
Сообщений: 14
Доброе время суток!

Ситуация примерно такая. Есть БД MS SQL с несколькими таблицами.
Также есть таблица с логом действий над остальными таблицами - AuditLog

На каждую из таблиц подвешены триггеры на insert, update, delete. Они вносят данные об изменениях в таблицу AuditLog.

Я не особо силен в t-sql, но смог это все написать. Главная проблема состоит в том, что в записи в таблице AuditLog мне нужно заполучить ID пользователя, который выполнил некое действие с таблицей.
Все было бы просто, если бы все пользователи коннектились под разными логинами на sql. Но у меня не так - все коннектятся под одним логином. И исправить это нельзя.
По сути мое приложение - это некое web приложение. В базе есть табличка Users. В asp с помощью <authentication mode="Forms"> сделана аутентификация по этой табличке.

И вот собственно вопрос: как передать ID пользователя из asp, чтобы триггер в MS SQL внес его в AuditLog.

Я примерно представляю себе, что скорее всего понадобится написать некую сборку на asp, каким-то образом её зарегить в sql и как-то получить нужные данные через все это в триггере. Но вот только как? Подскажите, в какую сторону копать?
Или может есть какой-нить другой более простой способ?

Заранее спасибо за ответ!
18 авг 11, 01:04    [11136734]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
olegik-ah
Member

Откуда:
Сообщений: 14
Забыл добавить - может это как то моможет.

Все таблицы у меня есть в DataSet и все действия над данными в ASP (update, delete, insert) производятся через TableAdapter
18 авг 11, 01:10    [11136746]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7599
olegik-ah,

Можно устанавливать контекст сессии как ID юзера

SET CONTEXT_INFO 0x1

И в триггере потом его вытягивать так...

SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
18 авг 11, 01:23    [11136768]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
olegik-ah
Member

Откуда:
Сообщений: 14
Огромнейшее спабо за подсказку! Все гениальное как всегда просто! Чуть порылся в нете и нашел, что вместо
И в триггере потом его вытягивать так...
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

Можно использовать просто
SET @Context_Info = CONTEXT_INFO()

Но есть один вопрос... Как этим корректно воспользоваться.

К примеру есть 2 залогиненых пользователя и они одновременно (с разрывом меньше доли секунды) вносят изменения в одну и туже таблицу

у одного команда будет такой
set context_info 55
update tbl set fld1=1 where fld2=2
а у второго такой
set context_info 22
update tbl set fld1=3 where fld2=4

может ли случиться так, что изза отсутствия между коммандами разрыва во времени sql сервер выполнит комманды в следующем порядке
set context_info 55
set context_info 22
update tbl set fld1=1 where fld2=2
update tbl set fld1=3 where fld2=4

Если такое может случиться, то как этого избежать? Засунуть set context и update в транзакцию? Или может такого не может вообще быть?

Заранее спасибо за ответ!
18 авг 11, 02:55    [11136840]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7599
olegik-ah
Если такое может случиться, то как этого избежать? Засунуть set context и update в транзакцию?
Да
18 авг 11, 03:14    [11136844]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
olegik-ah
Member

Откуда:
Сообщений: 14
Блин! Вот этого то я больше всего и боялся! Ацкий гемор предстоит:((

Есть еще один вопрос - последний. вот код триггера на обновление для одной из таблиц:
ALTER TRIGGER trg_ModuleRedirect_u
ON dbo.ModuleRedirect
FOR UPDATE
AS
begin
    declare @groupID uniqueidentifier
    set @groupID = newid()

	declare @tableName nvarchar(50)
	set @tableName = 'ModuleRedirect'

    insert into dbo.AuditLog(GroupID, EventType, TableName, rec_id, ColName, NewValue, OldValue)
    select @groupID, 1, @tableName, i.id, 'LocalRedirect', i.LocalRedirect, d.LocalRedirect
		 from
		 inserted i
		 inner join deleted d on i.id=d.id
		 where i.LocalRedirect<>d.LocalRedirect

    insert into dbo.AuditLog(GroupID, EventType, TableName, rec_id, ColName, NewValue, OldValue)
    select @groupID, 1, @tableName, i.id, 'PageID', i.PageID, d.PageID
		 from
		 inserted i
		 inner join deleted d on i.id=d.id
		 where i.PageID<>d.PageID

    insert into dbo.AuditLog(GroupID, EventType, TableName, rec_id, ColName, NewValue, OldValue)
    select @groupID, 1, @tableName, i.id, 'URL', i.URL, d.URL
		 from
		 inserted i
		 inner join deleted d on i.id=d.id
		 where i.URL<>d.URL

end
В таблице ModuleRedirect всего 3 контролируемых поля... А есть таблицы с большим количеством полей. И таблиц в базе много...
Может подскажите, как можно сделать этот скрипт универсальным? Чтобы его можно было подключить сразу к нескольким таблицам.

PS: Если с вашей точки зрения скрипт написан коряво - не стреляйте в пианиста - он играет как умеет:)
18 авг 11, 04:06    [11136856]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
olegik-ah
Member

Откуда:
Сообщений: 14
Люди, хелп плиз! Или скажите, что такое невозможно или мега-сложно!
18 авг 11, 15:29    [11140328]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
olegik-ah
В таблице ModuleRedirect всего 3 контролируемых поля... А есть таблицы с большим количеством полей. И таблиц в базе много...
Может подскажите, как можно сделать этот скрипт универсальным? Чтобы его можно было подключить сразу к нескольким таблицам.

PS: Если с вашей точки зрения скрипт написан коряво - не стреляйте в пианиста - он играет как умеет:)
Во-первых, надо знать, как будет использоваться этот лог.
Ибо если единственная задача - однажды вывалить всё оттуда в грид на клиенте - это одно.
А если он будет использоваться для представления данных на какую-нибудь произвольную дату в прошлом - то это совсем другое.

Во-вторых, Вы сравниваете значения полей так, будто в них не может появиться NULL.

В-третьих, универсальность потребует динамический SQL, использующий метаданные из системных таблиц/представлений.
Стало быть, и цикл по курсору потребуется. С вытекающими последствиями, например падением производительности.
А внутри DSQL использовать inserted и deleted не получится - придётся создавать и заполнять временные таблицы.
Надо ли всё это?
18 авг 11, 15:56    [11140564]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
olegik-ah
Member

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

Вот это не понял - в чем различие?

iap
Во-вторых, Вы сравниваете значения полей так, будто в них не может появиться NULL.

Мда. Об этом то я и не подумал. А как написать правильно?

iap
В-третьих, универсальность потребует динамический SQL, использующий метаданные из системных таблиц/представлений.
Стало быть, и цикл по курсору потребуется. С вытекающими последствиями, например падением производительности.
А внутри DSQL использовать inserted и deleted не получится - придётся создавать и заполнять временные таблицы.
Надо ли всё это?

В таком случае не надо. Просто я думал, что можно как-то проще поступить используя COLUMNS_UPDATED
18 авг 11, 16:16    [11140752]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
olegik-ah
я думал, что можно как-то проще поступить используя COLUMNS_UPDATED
Про UPDATE() и COLUMNS_UPDATED() забудьте сразу и навсегда. :)

Я правильно понял, что Ваш триггер сохраняет в логе имя колонки, старое и новое значения?
В таком случае воспользоваться этой информацией для чего-то конструктивного, а не для показа в гриде,
можно только в динамическом SQL. Одно это уже не радует. Возьмите и попробуйте выдать запись таблицы
за, скажем, день, который был месяц назад. Посмотрим, что получится.

Гораздо удобнее иметь таблицу-лог со структурой основной таблицы + поля "Дата изменеия", "Кто изменил" и т.п.,
в которую записывать целиком записи из deleted, но только в том случае, если вносятся хоть какие-нибудь реальные изменения.
inserted логировать не надо, ибо такая запись и так находится в основной таблице.

Плюс к сказанному - Журналирование изменений структуры БД и данных

Система отслеживания измененных данных
18 авг 11, 16:29    [11140881]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
olegik-ah
Member

Откуда:
Сообщений: 14
Забыл добавить. Условия
where i.field<>d.field

Я написал умышленно, т.к. конструкция

IF UPDATE (field)

всегда возвращала true. Всязано это с тем, что при обновлении в ASP через TableAdaper обновляются в любом случае все поля, а не только измененные...
18 авг 11, 16:30    [11140888]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
olegik-ah
Забыл добавить. Условия
where i.field<>d.field

Я написал умышленно, т.к. конструкция

IF UPDATE (field)

всегда возвращала true. Всязано это с тем, что при обновлении в ASP через TableAdaper обновляются в любом случае все поля, а не только измененные...
Вот именно! Поэтому и забудьте. Тем более, что никакой связи с реальными изменениями эта функция не имеет.
18 авг 11, 16:31    [11140908]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
olegik-ah
Member

Откуда:
Сообщений: 14
iap
Плюс к сказанному - Журналирование изменений структуры БД и данных

Вот какраз по этой статье (но не с этого сайта) я и написал свой триггер:)) Я в SQL не силен... На уровне select * from tbl знаю:)
Несколько таблиц не сделал преднамеренно, т.к. мне хотелось, чтоб весь лог был в одной таблице.

iap
Система отслеживания измененных данных

Это я почтаю-повникаю...

iap
Я правильно понял, что Ваш триггер сохраняет в логе имя колонки, старое и новое значения?

Да, так все и есть.

А вот что значит
iap
В таком случае воспользоваться этой информацией для чего-то конструктивного, а не для показа в гриде,
можно только в динамическом SQL. Одно это уже не радует. Возьмите и попробуйте выдать запись таблицы
за, скажем, день, который был месяц назад. Посмотрим, что получится.

Честно говоря не вкурил.... В табле AuditLog есть еще поле Date, в дефолте у которого GetDate(). Также есть индекс на Date Asc+GroupID Asc
В таком случае в чем проблема с выдачей записей о событии месяц назад?
18 авг 11, 16:41    [11140997]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
olegik-ah
Member

Откуда:
Сообщений: 14
Система отслеживания измененных данных - клевая вещь.... НО!
Система отслеживания изменений в данных доступна только в следующих выпусках: Enterprise, Developer и Evaluation SQL Server.

А у меня 2005 express:(
18 авг 11, 16:45    [11141031]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
olegik-ah
Честно говоря не вкурил.... В табле AuditLog есть еще поле Date, в дефолте у которого GetDate(). Также есть индекс на Date Asc+GroupID Asc
В таком случае в чем проблема с выдачей записей о событии месяц назад?
Проблема в том, что Вы не сможете обратиться к полю напямую, ибо его имя хранится как строка в таблице.
Что-то типа этого: FAQ
Кроме всего прочего, надо определить состояние на заданное время каждого поля
(а менялись-то они не синхронно, надо полагать).
Вот возьмите и напишите скрипт. Посмотрим. :)
18 авг 11, 16:46    [11141035]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
olegik-ah
Система отслеживания измененных данных - клевая вещь.... НО!
Система отслеживания изменений в данных доступна только в следующих выпусках: Enterprise, Developer и Evaluation SQL Server.

А у меня 2005 express:(
Мало того, только начиная с SQL2008
18 авг 11, 16:46    [11141043]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
olegik-ah
Member

Откуда:
Сообщений: 14
iap
olegik-ah
Честно говоря не вкурил.... В табле AuditLog есть еще поле Date, в дефолте у которого GetDate(). Также есть индекс на Date Asc+GroupID Asc
В таком случае в чем проблема с выдачей записей о событии месяц назад?
Проблема в том, что Вы не сможете обратиться к полю напямую, ибо его имя хранится как строка в таблице.
Что-то типа этого: FAQ
Кроме всего прочего, надо определить состояние на заданное время каждого поля
(а менялись-то они не синхронно, надо полагать).
Вот возьмите и напишите скрипт. Посмотрим. :)


За статью оргомное спасибо! Не понял как применить к данной ситуации, но зато нашел применение к другой теме, решение для которой давно искал!!!
К полям напрямую мне не нужно обращаться - не стоит такая задача. И определять состояние на заданное время тоже не надо. Моя задача был просто понять кто и когда что сделал, а NewValue и OldValue - это просто как информация к размышлению...

Остался последний вопрос - как правильно написать
where i.field<>d.field
Чтоб не было исключения на NULL

Огромное вам спасибо за помощь!
18 авг 11, 16:55    [11141105]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
olegik-ah
Остался последний вопрос - как правильно написать
where i.field<>d.field
Чтоб не было исключения на NULL
WHERE i.field<>d.field OR i.field IS NULL AND d.field IS NOT NULL OR i.field IS NOT NULL AND d.field IS NULL
Или
WHERE NOT EXISTS(SELECT i.field INTERSECT SELECT d.field)
Или
WHERE EXISTS(SELECT i.field EXCEPT SELECT d.field)
Это по убыванию производительности.
Можно и ещё способов придумать...
18 авг 11, 17:02    [11141172]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать триггер  [new]
olegik-ah
Member

Откуда:
Сообщений: 14
iap,
Огромнейшее спасибо за помощь!
Вопрос закрыт.
18 авг 11, 17:05    [11141195]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить