Create trigger dbo.TR_table ON dbo.Table
After INSERT, UPDATE
AS
BEGIN
INSERT INTO Log Values(...)
END
Контроль над пользователями БД
Используется MS SQL Server 2005.
Необходимо получить информацию о том какой пользователь какие изменения вносил, какие записи добавлял и т.д.
Где эту инфу можно найти? И можно ли выудить её с помощью SQL запроса?
Необходимо получить информацию о том какой пользователь какие изменения вносил, какие записи добавлял и т.д.
Где эту инфу можно найти? И можно ли выудить её с помощью SQL запроса?
Средствами самого сервера - нет. Нужно писать триггеры, заполняющие логи
Хорошо, пробуем через триггеры.
Пусть есть таблица Log, в которую будем заносить информацию об измененных и добавленных строках(заносим в таблицу, а не в лог, чтобы потом можно было вытянуть инфу с помощью запроса).
В ней есть столбцы:
ID-первичный ключ таблицы,
login-имя пользователя БД,
date-время изменения БД
action-изменение/добавление,
table-имя таблицы, в которой произошли изменения,
record_id-номер добавленной или измененной записи.
Теперь пишем триггер. Например для таблицы Table:
Вот по этой строке
возникают вопросы:
1). Как выловить номер последней записи в таблице Log и передать его как параметр+1 в триггер?
2). Есть ли стандартная переменная, хранящая имя текущего пользователя БД?
3). Поле action таблицы Log должно содержать значение "изменен", если триггер сработал на Update и "добавлен"-на Insert. Где-то запоминается на какое событие сработал триггер?
4). Как передать в триггер номер измененной/добавленной записи?
Пусть есть таблица Log, в которую будем заносить информацию об измененных и добавленных строках(заносим в таблицу, а не в лог, чтобы потом можно было вытянуть инфу с помощью запроса).
В ней есть столбцы:
ID-первичный ключ таблицы,
login-имя пользователя БД,
date-время изменения БД
action-изменение/добавление,
table-имя таблицы, в которой произошли изменения,
record_id-номер добавленной или измененной записи.
Теперь пишем триггер. Например для таблицы Table:
Код:
Вот по этой строке
Код:
INSERT INTO Log Values(...)
возникают вопросы:
1). Как выловить номер последней записи в таблице Log и передать его как параметр+1 в триггер?
2). Есть ли стандартная переменная, хранящая имя текущего пользователя БД?
3). Поле action таблицы Log должно содержать значение "изменен", если триггер сработал на Update и "добавлен"-на Insert. Где-то запоминается на какое событие сработал триггер?
4). Как передать в триггер номер измененной/добавленной записи?
Цитата: Balda
1). Как выловить номер последней записи в таблице Log и передать его как параметр+1 в триггер?
А зачем? [ID] делается счетчиком и все
Цитата: Balda
2). Есть ли стандартная переменная, хранящая имя текущего пользователя БД?
SYSTEM_USER
Цитата: Balda
3). Поле action таблицы Log должно содержать значение "изменен", если триггер сработал на Update и "добавлен"-на Insert. Где-то запоминается на какое событие сработал триггер?
С MSDN:
В следующем примере выполняются запросы к представлениям каталога sys.triggers и sys.trigger_events с целью определения, какие события языка Transact-SQL вызывали срабатывание триггера safety.
Код:
SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 0
AND T.name = 'safety'
GO
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 0
AND T.name = 'safety'
GO
Цитата: Balda
4). Как передать в триггер номер измененной/добавленной записи?
В триггере доступны таблицы INSERTED и DELETED. Структура у них та-же что и у изменяемой таблицы.
SELECT * FROM INSERTED - данные до изменения.
SELECT * FROM DELETED - данные после изменения.
Соответственно можно в лог записывать что конкретно изменялось
Пробую записать в Log номер измененной записи. В теле триггера пишу:
Выдает ошибку...
Код:
Insert into Log Values(... , inserted.ID)
Выдает ошибку...
Цитата: Balda
Пробую записать в Log номер измененной записи. В теле триггера пишу:
Выдает ошибку...
Код:
Insert into Log Values(... , inserted.ID)
inserted (также как и deleted) это не одна запись. Это набор! Приучайтесь мыслить в терминах множеств - это сильно помогает при написании запросов.
Код:
DECLARE @time_stamp as datetime
SELECT @time_stamp = GETDATE()
INSERT INTO Log ([user_id], [table_name], [record_id], [time_stamp])
SELECT SYSTEM_USER as [user_id], 'my_table' as [table_name], inserted.ID as [record_id], @time_stamp as [time_stamp]
FROM inserted
SELECT @time_stamp = GETDATE()
INSERT INTO Log ([user_id], [table_name], [record_id], [time_stamp])
SELECT SYSTEM_USER as [user_id], 'my_table' as [table_name], inserted.ID as [record_id], @time_stamp as [time_stamp]
FROM inserted
Теперь возникает другая проблема. Пробую вытянуть тип триггера:
Для комбинированного тригера, который срабатывает на INSERT или на UPDATE
type_desc в sys.trigger_events выдает два значения. Тогда невозможно загнать тип триггера в @TriggerType.
Можно ли вытянуть только один type_desc, так чтобы он зависел от события, на которое триггер сработал?
Или придется писать два триггера - один на INSERT, другой на UPDATE?
Код:
DECLARE @TriggerType varchar(10)
SELECT @TriggerType = (SELECT TE.type_desc FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 1 AND
T.name = 'TR_table')
SELECT @TriggerType = (SELECT TE.type_desc FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 1 AND
T.name = 'TR_table')
Для комбинированного тригера, который срабатывает на INSERT или на UPDATE
type_desc в sys.trigger_events выдает два значения. Тогда невозможно загнать тип триггера в @TriggerType.
Можно ли вытянуть только один type_desc, так чтобы он зависел от события, на которое триггер сработал?
Или придется писать два триггера - один на INSERT, другой на UPDATE?
Т.е. выдается 2 записи? Тогда можно написать условие
Код:
DECLARE @TriggerType varchar(10)
SELECT @TriggerType = CASE WHEN (SELECT TE.type_desc
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 1 AND
T.name = 'TR_table') = 1 THEN 'insert' ELSE 'update' END
SELECT @TriggerType = CASE WHEN (SELECT TE.type_desc
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 1 AND
T.name = 'TR_table') = 1 THEN 'insert' ELSE 'update' END
Жаль у вас не Firebird. В IBExpert есть такая замечательная вещь как "Протоколирование". Она бы за вас все сделала.
