July 30, 2013 at 1:15 am
Hello!
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)
SET CONTEXT_INFO @context
--This is how I retrieve
DECLARE @deleteUser VARCHAR(MAX)
--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.
July 30, 2013 at 1:24 am
It's a little tricky to work with context_info. See here for how for examples: http://www.sommarskog.se/grantperm.html#context_info. (That's a section in a longer article, and you only need to read down to the next header.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply