13

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

Brandon
  • 68,708
  • 30
  • 194
  • 223
  • I don't think it would be possible, because may be it's not a stored procedure that ran, it may have been a simple batch. I may be wrong, but I would say that in your case you should look into some sort of method to get a list of recent batches that involved a specific table, which is essentially what the monitor does. – BeemerGuy Nov 23 '10 at 21:57

3 Answers3

3

you can try: CONTEXT_INFO

here is a CONTEXT_INFO usage example:

in every procedure doing the insert/delete/update that you want to track, add this:

DECLARE @string        varchar(128)
       ,@CONTEXT_INFO  varbinary(128)
SET @string=ISNULL(OBJECT_NAME(@@PROCID),'none')
SET @CONTEXT_INFO =cast('Procedure='+@string+REPLICATE(' ',128) as varbinary(128))
SET CONTEXT_INFO @CONTEXT_INFO

--do insert/delete/update that will fire the trigger

SET CONTEXT_INFO 0x0 --clears out the CONTEXT_INFO value

here is the portion of the trigger to retrieve the value:

DECLARE @string         varchar(128)
       ,@sCONTEXT_INFO  varchar(128)
SELECT @sCONTEXT_INFO=CAST(CONTEXT_INFO() AS VARCHAR) FROM master.dbo.SYSPROCESSES WHERE SPID=@@SPID

IF LEFT(@sCONTEXT_INFO,9)='Procedure'
BEGIN
    SET @string=RIGHT(RTRIM(@sCONTEXT_INFO),LEN(RTRIM(@sCONTEXT_INFO))-10)
END
ELSE
BEGIN --optional failure code
    RAISERROR('string was not specified',16,1)
    ROLLBACK TRAN
    RETURN
END

..use the @string
KM.
  • 101,727
  • 34
  • 178
  • 212
  • @KM, thanks, I'll give this a shot, but I'm not sure if my team would be thrilled with me going through and modifying every stored procedure for temporary debugging :P – Brandon Nov 23 '10 at 22:05
  • would be even more thrilled to not fix the bug? triggers can be a pain sometime. – KM. Nov 24 '10 at 22:20
  • APP_NAME() is helpful tracking down different systems. – user423430 Jun 01 '12 at 18:38
2

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.

Community
  • 1
  • 1
Andy
  • 21
  • 3
  • Sadly, this is the best workaround to the abject lack of any Procedure/Trigger Call-Stack in SQL Server. Even Oracle has this! The 3rd Party Database I work in also uses `CONTEXT_INFO` everywhere. The only new advice I could offer now is with SS2016 you may use a `MEMORY_OPTIMIZED` Table to store your temporary Call-Stack Information in memory or use `SESSION_CONTEXT` to Track _ONLY_ the most-recent `@@PROCID` hot potato. Note: Beginning with SS2014-SP2 you may also use the `sys.dm_exec_input_buffer()` without Declaring Table-Variables and calling Dynamic-SQL to Execute `DBCC INPUTBUFFER`. – MikeTeeVee Jul 04 '18 at 16:48
-4

I've not tried this but @@PROCID looks like it might return what you want.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 4
    I believe that once you're inside the trigger code, this would return the ID of the trigger itself. – Joe Stefanelli Nov 23 '10 at 21:51
  • 1
    correct. @@procid (or object_name(@@procid)) is useful as a column default value in a table to know where an insert came from. – bwperrin Jul 23 '13 at 21:41