Make your own free website on Tripod.com

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

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New

NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line
See also:    
  Database programming guidelines, conventions SQL Server DBA and developer interview questions
  Evaluation of federated database servers SQL Server FAQ on programming, designing, administration, tools, replication
  SQL Server security best practices SQL Server administration best practices

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
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.


Disclaimer and terms of use
Copyright 1997 - 2002 Narayana Vyas Kondreddi. All rights reserved.