9

Possible Duplicate:
Is it possible for a trigger to find the name of the stored procedure that modified data?

I have a table with a trigger (on update) on it. When the trigger is executed I would like to know the name of the stored procecure which updated the table in question.

Community
  • 1
  • 1
Anthony
  • 7,210
  • 13
  • 60
  • 70

1 Answers1

19

This won't always be 100% reliable, and it sometimes will capture the outer procedure call even if that procedure called an inner one. But you can at least get some idea of what the user called that ended them up in the trigger.

ALTER TRIGGER dbo.whatever
ON dbo.something
FOR UPDATE
AS
BEGIN
    ... other trigger logic

    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

   -- of course you can store this somewhere instead of select
END

Stolen from Vyas K: http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm

You may also want to check out this question, which has an answer relating to using sys.dm_exec_query_stats - you can track back to procedure name using the object id exposed by sys.dm_exec_sql_text, and you can limit to very recent invocations using GETDATE() as a guide for "recent." There is also a lengthy example of using Extended Events to do it.

Create Trigger to log SQL that affected table?

Or these ones which use CONTEXT_INFO (but this requires changing all of the stored procedures that update the table):

Find query that fired a trigger

Is it possible for a trigger to find the name of the stored procedure that modified data?

Finally, you may check out these posts for some ideas:

http://www.thecodepage.com/post/TIP-A-Call-Stack-in-SQL-Server.aspx

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 1
    Ok, that is - brutal. I hope that the OP does not use that trigger too much because this is a SERIOUS performance killer here when used a lot. Temp table, dynamic sql, all in a trigger. Ouch. – TomTom Jan 22 '13 at 21:32
  • Indeed it's just a one-off thing for troublshoothing. All the tables in the database are updated through stored procedure so I know for sure, in this particular case, that the table in question is updated by a stored procedure. The table has some incorrect values in it and I just want to know which stored procedure does that (and yes I've already gone through the code of the stored procedures but I can't see anything wrong with them so I thought that having a temporary trigger on the table would help me find the cause of the problem). – Anthony Jan 23 '13 at 09:44