I have a trigger on a table that fires on updates and adds a record to another table indicating that it needs to be rolled up to an external system. In a few cases I need to dynamically suppress this trigger to prevent circular updates between the two systems. My data access is via LINQ to SQL for the system in question.
The article Disabling a Trigger for a Specific SQL Statement or Session deals with the same problem.
using(DbDataContext dc = new DbDataContext())
{
//Update fields etc...
dc.Connection.Open();
//Set the context_info here within the open connection so that it is in-scope
//for the triggers
dc.ExecuteCommand("SET Context_Info 0x55555");
dc.SubmitChanges();
dc.Connection.Close();
}
CREATE TRIGGER TR_Mock ON dbo.TableForTrigger FOR UPDATE AS DECLARE @Cinfo VARBINARY(128) SELECT @Cinfo = Context_Info() IF @Cinfo = 0x55555 RETURN PRINT 'Trigger Executed' -- Actual code goes here GO
See Also:
