Binaryworld - Code to find out the statement that caused the trigger to fire! ... [ SQL Server -> Trigger ]

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.

Click here to copy the following block

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

From the above code, replace the TableName and TriggerName with your table name and trigger name respectively and you can test the trigger by creating the trigger first and then by inserting/updating/deleting data.