Using fn_get_sql in a trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using fn_get_sql in a trigger

Author

Topic

ajreynolds
Starting Member

9 Posts

Posted - 2009-11-19 : 14:22:35

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

ajreynolds
Starting Member

9 Posts

Posted - 2009-11-23 : 13:03:20

Anybody? Thanks.

ajreynolds
Starting Member

9 Posts

Posted - 2009-11-23 : 16:53:53

Never mind... I just realized I was actually testing on a 2000sp3 server, where the DBCC INPUTBUFFER limit is 255 characters. Switching to a 2005 or 2008 server, it will now return my full string.

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources