SQL Server: pause a trigger

Question

I am working with SQL Server 2005 and I have trigger on a table that will copy an deletions into another table. I cannot remove this trigger completely. My problem is that we have now developed an archiving strategy for this table. I need a way of "pausing" a trigger when the stored proc that does the archiving runs.

Answer 1

A little more detail would be useful on how the procedure is accessing the data, but assuming you are just getting the data, then deleting it from the table and wish to disable the trigger for this process, you can do the following

DISABLE TRIGGER trg ON tbl;

then

ENABLE TRIGGER trg ON tbl;

for the duration of the procedure.

This only works for SQL 2005+

Answer 2

if DISABLE TRIGGER/ENABLE TRIGGER is not an option for some reason, you can create a table with a single row which will serve as a flag for the trigger.

Answer 3

An alternate method is to use Context_Info to disable it for a single session, while allowing other sessions to continue to fire the trigger.

Context_Info is a variable which belongs to the session. Its value can be changed using SET Context_Info.

The trigger will mostly look like this:

USE AdventureWorks;  
GO  
-- creating the table in AdventureWorks database  
IF OBJECT_ID('dbo.Table1') IS NOT NULL  
DROP TABLE dbo.Table1  
GO  
CREATE TABLE dbo.Table1(ID INT)  
GO   
-- Creating a trigger  
CREATE TRIGGER TR_Test ON dbo.Table1 FOR INSERT,UPDATE,DELETE  
AS  
DECLARE @Cinfo VARBINARY(128)  
SELECT @Cinfo = Context_Info()  
IF @Cinfo = 0x55555  
RETURN  
PRINT 'Trigger Executed'  
-- Actual code goes here  
-- For simplicity, I did not include any code  
GO

If you want to prevent the trigger from being executed you can do the following:

SET Context_Info 0x55555 
INSERT dbo.Table1 VALUES(100)

Before issuing the INSERT statement, the context info is set to a value. In the trigger, we are first checking if the value of context info is the same as the value declared. If yes, the trigger will simply return without executing its code, otherwise the trigger will fire.

source: http://www.mssqltips.com/tip.asp?tip=1591