How to use CONTEXT_INFO in SQL Server


(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

Pages: 1 2




Related Articles :

  • No Related Articles Found

One Response to “How to use CONTEXT_INFO in SQL Server”

  1. Got me on the road to using CONTEXT_INFO. Thank you!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |