My picture album
My code library
Search my site
SQL Server Articles New
NEW!!! Subscribe to my
|Want to keep in touch with the
latest in SQL Server world? Email email@example.com with
'subscribe' in the subject line
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
FOR INSERT, UPDATE, DELETE AS
SET NOCOUNT ON
DECLARE @ExecStr varchar(50), @Qry nvarchar(255)
CREATE TABLE #inputbuffer
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO #inputbuffer
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
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.