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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy