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
Many thanks in adv.