Conditional Triggers

Here's an interesting problem that I've come accross:

Suppose I have a trigger that fires on a table and updates a collection of other tables. Suppose I roll out a new piece of business code that updates a large portion of the table, but I DON'T want this update to cause the trigger to fire. How do I do this? In short, I want to be able to enable - disable a trigger on a session basis. I can't use ALTER TABLE DISABLE TRIGGER because that will turn off the trigger for all concurrent sessions.

Here's an interesting solution that someone suggested in usenet that works well for me.

In the stored procedure that does the mass update, I have the following line of code:

CREATE Table #DontRunTrig (i int)

Then, I alter the trigger to have this as the as the FIRST line:

IF OBJECT_ID('tempdb..#DontRunTrig') IS NOT NULL RETURN

This allows the trigger to do it's thing on a per spid basis.

Sort of nasty looking, but it works.