In SQL Server every batch has an attribute associated with it called Context_info. This session specific variable is accessible to the user to be set or read. Here is an example from BOL on setting and retrieving the value of Context_Info:
SET CONTEXT_INFO 0x1256698456
GO
SELECT CONTEXT_INFO()
GO
Some ingenious developers have determined this to be an effective way to disable actions in a trigger that are not desired for a specific action. They set CONTEXT_INFO in some process before manipulating a table. Then in the trigger, they retrieve the context information using CONTEXT_INFO(). If the value does not match the value they can either process or ignore part or all of the actions contained in a trigger.
This is useful should you have a process for which you don’t want a trigger to fire, but you cannot disable the trigger in case it is needed for some other process. The opposite is true; you can also use this process to enable the features of a trigger under special circumstances.
What makes this so powerful is that the setting of the CONTEXT_INFO value is specific to the current batch, or SPID. This allows you to isolate actions from your command from the actions of any other database connection. No other process will share your setting of CONTEXT_INFO.
This process extends the danger of using Triggers. Triggers act as magic code that is difficult to test and verify. There are some things that are difficult or impossible outside of a trigger, so they have their place. I would be careful to use the CONTEXT_INFO filter in a trigger for only those processes where you literally have no other solution. A well normalized database will probably not require either triggers or CONTEXT_INFO tricks to maintain correct data.
Would you use the CONTEXT_INFO technique to isolate processes, especially in trigger actions? Share your thoughts or experiences online or by email to btaylor@sswug.org.
Cheers,
Ben