My colleague asked me once if there is a possibility to get info in trigger, from which stored procedure was trigger invoked. That time I didn’t have answer but now I had to resolve similar task and I found it very useful. We have a table which is getting data and their modifications from many places, since there are lots of mechanisms in the game. There is direct approach from applications, databases, SSIS packages etc. APP_NAME () function was not sufficient solution in my case since there were more ways to modify data from one application. My task was to log info regarding one concrete mechanism changing data in that table. I saw on internet few samples with CONTEXT_INFO solution and I found it very useful since the mechanism is implemented in database stored procedure. Solution was very easy then:
I put something similar to script bellow to stored procedure changing data on the table
DECLARE @context SYSNAME = 'SOMEINFO' DECLARE @contextBIN VARBINARY(128) = CAST ( @context AS VARBINARY(128) )
In database trigger logging changes, I just put following code to get stored data in CONTEXT_INFO.
SELECT CAST (CONTEXT_INFO() AS SYSNAME) FROM inserted
SET CONTEXT_INFO establishes information with current session or connection, could be used for other scenarios, e. g. passing info between nested procedures, adding more details to batches called from applications, etc. See more info here: https://bit.ly/2Kzh1Pl.
Do you have another scenarios how to use CONTEXT_INFO ?