Pass a variable into a trigger

I use SET CONTEXT_INFO for this kind of action. That's a 2008+ link, prior link has been retired.

On SQL Server 2005+, you'd have CONTEXT_INFO to read it but otherwise you have to get from context_info column in dbo.sysprocesses.

you can't pass a variable into a trigger.

the only way to get the information in the trigger is to be able to SELECT it based on the INSERTED or DELETED tables or add a column onto the affected table and put the value in that column.

EDIT in the previous question OP posted about this, they said that they didn't want to use CONTEXT_INFO, but here they say it is Ok to use, so here is a CONTEXT_INFO usage example:

in the procedure doing the update

DECLARE @intUserID     int
       ,@CONTEXT_INFO  varbinary(128)
SET @intUserID = 10
SET @CONTEXT_INFO =cast('intUserID='+CONVERT(varchar(10),@intUserID)+REPLICATE(' ',128) as varbinary(128))
SET CONTEXT_INFO @CONTEXT_INFO

--do update that will fire the trigger

SET CONTEXT_INFO 0x0 

here is the portion of the trigger to retrieve the value:

DECLARE @intUserID     int
       ,@sCONTEXT_INFO  varchar(128)
SELECT @sCONTEXT_INFO=CAST(CONTEXT_INFO() AS VARCHAR) FROM master.dbo.SYSPROCESSES WHERE SPID=@@SPID

IF LEFT(@sCONTEXT_INFO,9)='intUserID'
BEGIN
    SET @intUserID=RIGHT(RTRIM(@sCONTEXT_INFO),LEN(RTRIM(@sCONTEXT_INFO))-10)
END
ELSE
BEGIN
    RAISERROR('intUserID was not specified',16,1)
    ROLLBACK TRAN
    RETURN
END

..use the @intUserID

I use the sp_set_session_context stored procedure to set the value:

exec sp_set_session_context @key = N'userid', @value = 123 

And in my trigger to read the value:

DECLARE @userid int
SELECT @userid = cast(SESSION_CONTEXT(N'userid') as int)

Old question, but I wonder how come nobody mentioned that temporary tables created before the trigger is invoked are visible in the trigger? So, this would work:

SELECT 10 intUserID INTO #intUserID

UPDATE tblData
SET    Value = @x

The trigger will see the temp table #intUserID and can read the id from there.