November 23, 2009 at 11:20 am
I am trying to set up my first trigger, and it's mostly there, but I'm having a little trouble. The trigger will log all deletes on a table, along with the SQL statement that did the delete. But I'm having trouble getting the SQL statement that triggered the delete correctly. When I use DBCC INPUTBUFFER(@@SPID), it returns the correct statement, but only the first 255 characters, which isn't long enough. When I use fn_get_sql, it returns the SQL of the trigger creation instead of the SQL that triggered the delete. Here is what my trigger looks like:
create trigger deletion_trigger on mytable
for delete
as
begin
set nocount on
declare @DelDate char(8),
@DelTime char(12),
@Handle binary(20),
@Qry nvarchar(4000)
select @Handle = sql_handle FROM master..sysprocesses WHERE spid = @@SPID
SELECT @Qry = convert(nvarchar(4000),[text]) FROM ::fn_get_sql(@Handle)
set @DelDate = convert(varchar(8), getdate(), 112)
set @DelTime = convert(varchar(12), getdate(), 114)
insert into deletelog values (@DelDate, @DelTime, @Qry)
end
I've read several similar questions to this on the net, but none of them seemed to ever be answered. Does anyone have any ideas on this?
Thanks,
Andy
November 23, 2009 at 1:12 pm
This is a known deficiency unfortunately. DBCC INPUTBUFFER is still around for this reason. I whipped up some test code to demonstrate some of this... and was rather surprised by what I found. DBCC INPUTBUFFER (on my 2K8 Database) shows the full string. Observe the following test code:
--Create MyTable
CREATE TABLE mytable(a char(1))
INSERT INTO mytable(a)
SELECT 'a' UNION ALL SELECT 'b'
GO
-- Create DeleteLog
CREATE TABLE deletelog(
DelDatedatetime,
Qrynvarchar(MAX),
QryTypevarchar(50)
)
GO
--Create Trigger
create trigger deletion_trigger on mytable
for delete
as
begin
set nocount on
declare @handle binary(20), @Qry nvarchar(MAX)
select @Handle = most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
SELECT @Qry = [text] FROM sys.dm_exec_sql_text(@Handle)
insert into deletelog (DelDate, Qry, QryType)
values (GETDATE(), @Qry, 'dm_exec_sql_text')
SELECT @Qry = [text] FROM fn_get_sql(@handle)
insert into deletelog (DelDate, Qry, QryType)
values (GETDATE(), @Qry, 'fn_get_sql')
CREATE TABLE #IB(
EventTypeNVARCHAR(30) NULL,
ParametersINT NULL,
EventInfoNVARCHAR(max) NULL
)
INSERT #IB
EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')
SELECT @Qry = EventInfo FROM #IB
insert into deletelog(DelDate, Qry, QryType)
values (GETDATE(), @Qry, 'DBCC IB')
END
GO
-- Delete Values
DELETE FROM MyTable WHERE a = 'a'
-- Show what happened
SELECT DelDate, LEFT(Qry,35) Qry, QryType, LEN(Qry) QryLength
FROM DeleteLog
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 1
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 2
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 3
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 4
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 5
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 6
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 7
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 8
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 9
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 10
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 11
-- Comments Comments Comments Comments Comments Comments Comments Comments Comments Comments 12
GO
-- Cleanup
DROP TABLE MyTable
DROP TABLE DeleteLog
On my 2K8 server, I get these results(Regardless of compatibility level):
DelDateQryQryTypeQryLength
2009-11-23 15:11:45.047 --Create Trigger create trigger dm_exec_sql_text840
2009-11-23 15:11:45.047 --Create Trigger create trigger fn_get_sql840
2009-11-23 15:11:45.060 -- Delete Values DELETE FROM MyTDBCC IB1316
On 2000(after modifying it to comply with SQL 2000 of course), I get:
DelDateQryQryTypeQryLength
2009-11-23 15:09:17.657 --Create Trigger create trigger fn_get_sql880
2009-11-23 15:09:17.673 -- Delete Values DELETE FROM MyTDBCC IB255
The good news is it looks like DBCC INPUTBUFFER returns the full string in 2K8 at least. Unfortunately I don't have a 2K5 server to run this on, so if someone does and wants to post the results, I'd appreciate it.
November 23, 2009 at 2:50 pm
Thanks alot Seth. I got looking deeper and realized the server I'm working on isn't 2005 after all, it's 2000sp3! I've got so many servers here to deal with, I got mixed up which one I was looking at.
I've tried your test on a 2005 server and it does work correctly and returns the same results you listed for 2008.
Thanks again for the help.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply