CODEWITHSUNDEEP
sqlsql-servertriggerssql-updatedatabase-trigger
jj367
jj367

Reputation: 25

SQL Server trigger: identify specific Update statement use

I need to use a series of relatively simple update statements on a large table, for example as below:

UPDATE Table1
SET Col1 = 'A' 
WHERE Col2 = '1'

UPDATE Table1
SET Col1 = 'A' 
WHERE Col3 = 'X'

UPDATE Table1
SET Col1 = 'B' 
WHERE Col2 = '2'

I am using a trigger to track which records are updated. How would I go about identifying which specific update statement had resulted in the update in the table output from the trigger?

Would it be possible to reference a variable set next to the update statement in the trigger script?

Upvotes: 1

Views: 979

Answers (1)

Sean Pearce
Sean Pearce

Reputation: 1169

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.

Taken from here!

Upvotes: 2

Related Questions