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.

You cant pass variables to triggers. Depending on how users connect to the database you could use SYSTEM_USER to get the current user connected to the database.

This is like a hack, but you can potentially pass in an APP name in the connection string, and your trigger will be able to access that value. When you compose the connection string, you can append the userid into the app name, and then in the trigger, you get the app name by calling

SET @AppName = APP_NAME();

and then parse out the userid from the @AppName Be warned that this approach will create tons of connection pools, not a good approach for web app.

You do not pass variables to triggers because you are not able to call triggers directly. They are executed as a result of data being inserted, modified or deleted.