Pass a variable into a trigger

问题:

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.

回答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.

回答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

回答3:

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.

回答4:

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.

回答5:

Why not try this:

CREATE TRIGGER trgDataUpdated
   ON tblData FOR UPDATE
AS 
BEGIN
    EXECUTE tbLogUpdate intUserId
END

回答6:

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.