Thursday, June 17, 2010

Disabling a Trigger for a LINQ to SQL DataContext

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: