Pass a variable into a trigger

Question

I have a trigger which deals with some data for logging purposes like so:

CREATE TRIGGER trgDataUpdated
   ON tblData FOR UPDATE
AS 
BEGIN
    INSERT INTO tblLog ( ParentID, OldValue, NewValue, UserID )
    SELECT  deleted.ParentID, deleted.Value, inserted.Value, 
            @intUserID -- how can I pass this in?
    FROM    inserted INNER JOIN deleted ON inserted.ID = deleted.ID
END

How can I pass in the variable @intUserID into the above trigger, as in the following code:

DECLARE @intUserID int
SET @intUserID = 10

UPDATE tblData
SET    Value = @x

PS: I know I can't literally pass in @intUserID to the trigger, it was just used for illustration purposes.

Castleman

Answer 1

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.

Statistical

I was considering this but it didn't look like the most elegant solution. If it's the only way, then sure I'll do it, but I was hoping for something else :( – Castleman

I'm implementing this now. Just wanted to paste this link for anyone else wanting to do this in the future: msdn.microsoft.com/en-us/library/aa214382%28SQL.80%29.aspx – Castleman

Answer 2

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

Arriviste

Answer 3

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)

Browne

sp_set_session_context is available from SQL 2016 onwards, including Azure SQL Database. – Triggerfish

Answer 4

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.

Rausch

Sensational answer – Zennie

Answer 5

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.

Slusher

Answer 6

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.

Armenian

Answer 7

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.

Picklock

I wasn't looking for a yes/no answer here. Surely there's a way to store information against the current transaction that the trigger can pick up? – Castleman