How to track which stored procedure fired a trigger
Having one database that supports many applications is very common in an enterprise environment. For example, XYZ Telecom may provide services to its customers through web applications, call centers, billing systems etc. Generally in each of these scenarios the database will remain the same but the applications will be completely independent from each other. To extend this scenario further, it is also possible that even a single application can have different methods to enter data into a single database table.
Herein the problem arises, how do you track down which application or which route inserted data into a database? Suppose a table has a trigger and data can be inserted into this table from multiple stored procedures how do you know which stored procedure fired the trigger? This article will show to solve this problem using the CONTEXT_INFO function.
Scenario 1
For this example I will use a table called TestContextinfo. This table is being updated from three different applications by three different stored procedures, spApplication1, spApplication2 and spApplication3. I need to track which stored procedure causes the trigger to fire on each table. You may feel this is not a valid scenario. I aggree, this senario can be directly handled by passing the Stored procedure name in the insert statement itself. But what happens if a nested trigger fires ie. if this trigger fires a insert statement in another table and there is another trigger fired and you need to track the Stored Procedure that fired the first DML statement, then it becomes a little tricky. This scenario is aimed to address this.
(a) Create Sample table
IF OBJECT_ID(‘TestContextinfo’) is Not Null
Begin
Drop table TestContextinfo
End
Create table TestContextinfo (Id int identity, Name varchar(40),InsertedApplication varchar(100))
GO
I will capture the CONTEXT_INFO information to the InsertedApplication column from the trigger.
(b) Create Trigger
Create Trigger trgTestContextinfoIUD ON TestContextinfo
FOR Insert,Update,Delete
as
DECLARE @CONTEXT_INFO varbinary(128),@SPID int
–call system Function to get context info
SELECT @Context_Info=CONTEXT_INFO()
–Cast the binary data to plain text
select cast (@CONTEXT_INFO as char(128))
–Joining with Inserted Table to update InsertedApplication column
Update TestContextinfo
SET InsertedApplication= cast (@CONTEXT_INFO as char(128))
From Inserted I
INNER JOIN TestContextinfo TC
on TC.ID=I.ID
–Reinitialising Context_info. It can be anything I reintialised with SPID
Select @SPID=@@SPID
Set CONTEXT_INFO @SPID
GO