Code to find out the statement that caused the trigger to fire!
Last updated: April 15th '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

Sometimes you may want to find out what exact statement that updated your table. Or you may want to find out how the WHERE clause of the DELETE statement (Executed by someone) looked like.

DBCC INPUTBUFFER can provide you with this kind of information. You can create a trigger on your table, that uses DBCC INPUTBUFFER command to find out the exact command that caused the trigger to fire.

The following trigger code works in SQL Sever 2000 (In SQL Server 7.0, you can't create tables inside a trigger. So, you'll have to create a permanent table before hand and use that inside the trigger). This code only displays the SQL statement, login name, user name and current time, but you can alter the code, so that this information gets logged in a table for tracking/auditing purposes.

CREATE TRIGGER TriggerName 
ON TableName 
FOR INSERT, UPDATE, DELETE AS 
BEGIN
 SET NOCOUNT ON
 
 DECLARE @ExecStr varchar(50), @Qry nvarchar(255)
 
 CREATE TABLE #inputbuffer 
 (
  EventType nvarchar(30), 
  Parameters int, 
  EventInfo nvarchar(255)
 )
 
 SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
 
 INSERT INTO #inputbuffer 
 EXEC (@ExecStr)
 
 SET @Qry = (SELECT EventInfo FROM #inputbuffer)
 
 SELECT @Qry AS 'Query that fired the trigger', 
 SYSTEM_USER as LoginName, 
 USER AS UserName, 
 CURRENT_TIMESTAMP AS CurrentTime
END