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

Question

There are a few stored procedures that routinely get called by a few different systems to do maintenance on a few tables in our database. Some are automated, some aren't.

One of the tables has a column where the number is sometimes off, and we don't know for sure when or why this is happening. I want to put a trigger on the table so I can see what is being changed and when, but it'd also be helpful to know which procedure initiated the modification.

Is it possible to get the name of the stored procedure from the trigger? If not, is there any other way to tell what caused something to be modified? (I'm not talking about the user either, the name of the user doesn't help in this case).

Answer 1

Our system is already using the CONTEXT_INFO variable for another purpose so that is not available. I also tried the DBCC INPUTBUFFER solution which almost worked. The draw back to the inputbuffer is that it returns only the outside calling procedure. Ex: procA calls procB which fires a trigger. The trigger runs DBCC INPUTBUFFER which only shows procA. Since my trigger was looking for procB, this approach failed.

What I have done in the meantime is to create a staging table. Now procA calls procB. procB inserts a line in the staging table then fires the trigger. The trigger checks the staging table and finds the procB entry. Upon return procB deletes its entry from the staging table. It's a shell game but it works. I would be interested in any feedback on this.