PDF
Несколько раз встречал подобные вопросы в форумах. Задача, формулируется примерно так. Получить в триггере текст команды, которая привела к тому, что этот триггер сработал.
Это может быть полезно если мы, например, хотим увидеть/залогировать конкретный запрос, который привел к изменению данных. Сразу скажу, что штатный механизм для обеспечения этого на сегодняшний день, в версиях 2000, 2005, 2008 — не предусмотрен. Но тем не менее, что-то похожее можно реализовать с некоторыми ограничениями.
В этом нам поможет команда DBCC INPUTBUFFER. В отличии от fn_get_sql и, появившейся в 2005, sys.dm_exec_sql_text, которые вернули бы текст самого триггера, DBCC INPUTBUFFER — вернет последнюю инструкцию, отправленную серверу, в которой будет весь текст пакета.
Для того чтобы иметь возможность записать данные, возвращаемые DBCC, воспользуемся маленькой хитростью, при помощи динамического sql, обернем выполнение этой команды в вызов хранимой процедуры и запишем результат процедуры во временную таблицу, пользуясь синтаксисом insert … exec.
В итоге, выглядит это все так:
/*
создаем тестовые таблицы, одну - которую будем логировать,
вторую - в которую будем писать лог
*/
if object_id('dbo.TriggerTable') is not null drop table dbo.TriggerTable
if object_id('dbo.LogTable') is not null drop table dbo.LogTable
create table dbo.TriggerTable( field int )
create table dbo.LogTable( field nvarchar(4000) )
/*создаем триггер на вставку*/
go
create trigger TriggerTable_insert on dbo.TriggerTable after insert as
begin
set nocount on;
declare @temp table(EventType nvarchar (30), Parameters int, EventInfo nvarchar(4000) )
insert into @temp
exec sp_executesql N'DBCC inputbuffer (@@spid) WITH NO_INFOMSGS'
insert into dbo.LogTable
select EventInfo from @temp
end
/* добавляем значения */
go
insert into dbo.TriggerTable values (1)
go
insert into dbo.TriggerTable values (2)
go
/* проверяем что записалось в лог */
select * from dbo.LogTable
go
/* удаляем тестовые таблицы */
drop table dbo.LogTable
drop table dbo.TriggerTable
go
результат:
field ---------------------------------------- insert into dbo.TriggerTable values (1) insert into dbo.TriggerTable values (2) (2 row(s) affected)
О проблемах и ограничениях. Как было сказано, это не штатный механизм, и он имеет некоторые проблемы. Первая и главная, на мой взгяд, проблема, в том, что в буфер попадают все инструкции в текущем пакете, а не только та, что инициировала выполнение триггера. По этому получить в чистом виде именно тот запрос, который вызвал триггер, без остальных инструкций — невозможно. Вторая проблема заключается в том, что буфер имеет ограниченную четырьмя тысячами символов длину. Еще одна проблема, это выполнение вставки из ХП, в этом случае в буфер записывается вызов этой ХП, а не сам запрос на вставку. Ниже это все проиллюстрировано.
/*
создаем тестовые таблицы, одну - которую будем логировать,
вторую - в которую будем писать лог
*/
if object_id('dbo.TriggerTable') is not null drop table dbo.TriggerTable
if object_id('dbo.LogTable') is not null drop table dbo.LogTable
if object_id('dbo.TriggerTable_add') is not null drop proc dbo.TriggerTable_add
create table dbo.TriggerTable( field int )
create table dbo.LogTable( field nvarchar(4000) )
/*создаем триггер на вставку*/
go
create trigger TriggerTable_insert on dbo.TriggerTable after insert as
begin
set nocount on;
declare @temp table(EventType nvarchar (30), Parameters int, EventInfo nvarchar(4000) )
insert into @temp
exec sp_executesql N'DBCC inputbuffer (@@spid) WITH NO_INFOMSGS'
insert into dbo.LogTable
select EventInfo from @temp
end
/* добавляем значения */
/* Тут все в порядке, в лог попадает запрос на вставку*/
go
insert into dbo.TriggerTable values (1)
go
/* Вот тут в лог попадет все что до и после инсерта, т.е. все содержимое пакета*/
go
-- попадает все что есть в пакете
select 1
insert into dbo.TriggerTable values (2)
select 2
go
/* Тут в лог попадет только сам вызов процедуры*/
create proc dbo.TriggerTable_add @value int
as
insert into dbo.TriggerTable values (@value)
go
exec dbo.TriggerTable_add 3
go
/* проверяем что записалось в лог */
select * from dbo.LogTable
go
/* удаляем тестовые таблицы */
drop proc TriggerTable_add
drop table dbo.LogTable
drop table dbo.TriggerTable
go
результат:
field ---------------------------------------- insert into dbo.TriggerTable values (1) -- попадает все что есть в пакете select 1 insert into dbo.TriggerTable values (2) select 2 exec dbo.TriggerTable_add 3 (3 row(s) affected)
Наверняка существуют и еще какие-то проблемы, но мне хватило вышеперечисленного, чтобы отказаться всерьез рассматривать такой метод логирования действий пользователей на рабочих серверах. Тем не менее даже в таком виде, с ограничениями, этот способ имеет право на жизнь, и может использоваться в простых случаях. А для своих нужд, я остановился на использовании штатного механизма трассировки, при помощи функций семейства sp_trace_XXX. О том как и для чего они у меня используются напишу в следующем посте.
