Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Value set in CONTEXT_INFO not able to fetch in trigger proc Expand / Collapse
Posted Tuesday, July 30, 2013 1:15 AM


Group: General Forum Members
Last Login: Sunday, November 2, 2014 9:45 PM
Points: 12, Visits: 53

I have two different procs for deleting the record and another proc for trigger which stores data in some history table.

I am trying to set value in CONTEXT_INFO with user name and I need this value in trigger proc so that I can insert user info in History table. But in trigger I am not getting value which I have set in delete proc.

is this because of session of delete proc is getting expired and in trigger I am getting value for totally different session??

--This is how I set
DECLARE @context varbinary(128)
SET @context = CAST(@USER AS varbinary)

--This is how I retrieve
--SELECT @deleteUser = CAST(CONTEXT_INFO() AS varchar(MAX))
SELECT @deleteUser = CAST(CONTEXT_INFO AS VARCHAR(MAX))FROM [master].[sys].[sysprocesses] WHERE SPID=@@SPID

Please suggest

Many thanks in adv.
Post #1478850
Posted Tuesday, July 30, 2013 1:24 AM

SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:29 AM
Points: 889, Visits: 862
It's a little tricky to work with context_info. See here for how for examples: (That's a section in a longer article, and you only need to read down to the next header.)

Erland Sommarskog, SQL Server MVP,
Post #1478855
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse