Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Disabling a trigger for a session
Message
General information
Forum:
Microsoft SQL Server
Category:
Other
Miscellaneous
Thread ID:
00719302
Message ID:
00722221
Views:
21
This message has been marked as the solution to the initial question of the thread.
>> 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
Michael Levy
MCSD, MCDBA
ma_levy@hotmail.com
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform