>> a question remains, is the context info reseted when a session is created?
I did a rough test. Filled the value with 'Hello World', checked it, got the spid
closed the connection and reopened it. verify that I had the same connection.
the value was cleared.
now, you really need to do a more through test and remember that there are options for the connection string that determine whether an explicit reset is made when the connection is pulled from the connection pool.
Another option would be to reset context_info after explicitly setting it.
-Mike
>I've found a workaround using a mixture of different sugestions.
>
>first set the context_info of the session
>
>declare @BinarySessionId varbinary(128)
>select @BinarySessionId = convert(varbinary(128), @@spid)
>Set context_info @sessionid
>
>Then add in the Trigger :
>Declare @BinarySessionIdToSkip varbinary(128)
>SELECT @BinarySessionIdToSkip = context_info
> FROM master.dbo.sysprocesses
> WHERE SPID=@@SPID
>
>If @SessionIdToSkip <> convert(varbinary(128), @@spid)
>begin
> --do logging process
>
>end
>return
>
>a question remains, is the context info reseted when a session is created?
>
>>Thk for the reply i'll look into it next week
>>
>>>What about using an INSTEADOF trigger. Within it you can query the sysprocess table looking for a flag in the context_info section. You can use SET CONTEXT_INFO to put a value in there.
>>>
>>>As another thought, if there are situations where the behavior needs to be circumvented, maybe a trigger is the wrong tool.
>>>
>>>-Mike
>>>
>>>
>>>>Hi,
>>>>
>>>>is there a way to do a update command without firing the update trigger for a session?
>>>>
>>>>we can't use the "alter table table1 disable trigger UpdTrig1" because it will affect all session.
>>>>
>>>>
>>>>I was thinking of using the not for replication option of the trigger.
>>>>Is there a way to "fool" the trigger to believe that i'm in a replication update?
>>>>
>>>>Alain