(c) Create Sample StoredProcedure 1
Create Proc spApplication1
as
DECLARE @CONTEXT_INFO varbinary(128)
select @CONTEXT_INFO = cast(‘spApplication1’ + space(128) as binary(128))
–Set the CONTEXT_INFO with the storedprocedure name
set CONTEXT_INFO @Context_Info
Insert into TestContextinfo(Name) select ‘AAAA’
GO
(d) Create Sample StoredProcedure 2
Create Proc spApplication2
as
DECLARE @CONTEXT_INFO varbinary(128)
select @CONTEXT_INFO = cast(‘spApplication2’ + space(128) as binary(128))
–Set the CONTEXT_INFO with the storedprocedure name
set CONTEXT_INFO @Context_Info
Insert into TestContextinfo(Name) select ‘BBBB’
GO
(e) Create Sample StoredProcedure 3
Create Proc spApplication3
as
DECLARE @CONTEXT_INFO varbinary(128)
select @CONTEXT_INFO = cast(‘spApplication3’ + space(128) as binary(128))
–Set the CONTEXT_INFO with the storedprocedure name
set CONTEXT_INFO @Context_Info
Insert into TestContextinfo(Name) select ‘CCCC’
Screen Shot
If you have multi level/nested triggers implemented then the CONTEXT_INFO method will assist to track the workflow.
How to fire the trigger only when a particular application /storedprocedure fires the DML statement
I have a trigger on a table and I want that trigger to be fired only when Application1 (spApplication1) fires a DML statement . I am keeping the same test cases as given above. I just need to change the Trigger body. I will set the CONTEXT_INFO for all the stored procedures, and inside the trigger I will check the CONTEXT_INFO and if only the CONTEXT_INFO is spApplication1 will I insert it, otherwise I will rollback.
The following trigger will check which application fired the DML and if the CONTEXT_INFO is application1 then it is Inserted into the Table otherwise it will rollback the transaction:
Create Trigger trgTestContextinfoIUD ON TestContextinfoCase2
For Insert
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 varchar(128))
–Joining with Inserted Table to update InsertedApplication column
If cast (@CONTEXT_INFO as varchar(128))=’spApplication1′
Begin
Insert Into TestContextinfo
select Name, cast (@CONTEXT_INFO as char(128)) from Inserted
–Reinitialising Context_info. It can be anything I reintialised with SPID
Select @SPID=@@SPID
Set CONTEXT_INFO @SPID
End
Else
Begin
RAISERROR(‘This table can be only modified by Application1 ‘,16,1)
ROLLBACK TRAN
RETURN
End
Summary
CONTEX_INFO function can be tweaked for many purposes like this. You can bypass, disable trigger or you can route the trigger in different way.
I have tested the above code in SQL Server 2005 and 2008. For 2000 users you will need to make the following changes in the above mentioned code as the CONTEXT_INFO() function is not available in SQL Server 2000.
Replace
SELECT @Context_Info=CONTEXT_INFO()
Replace with
SELECT @Context_Info= CONTEXT_INFO FROM master.dbo.SYSPROCESSES WHERE SPID=@@SPID
]]>