[ACCEPTED]-Is there a way to disable a SQL Server trigger for just a particular scope of execution?-triggers

I just saw this article recently highlighted 7 on the SQL Server Central newsletter and 6 it appears to offer a way which you may 5 find useful using the Context_Info on the 4 connection:

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

EDIT by Terrapin:

The above link 3 includes the following code:

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 2 to prevent the trigger from being executed 1 you can do the following:

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

If your trigger is causing performance problems 11 in your application, then the best approach 10 is to remove all manual updates to the table, and 9 require all updates to go through the insert/update 8 stored procedures that contain the correct 7 update logic. Then you may remove the trigger 6 completely.

I suggest denying table update 5 permissions if nothing else works.

This also 4 solves the problem of duplicate code. Duplicating 3 code in the update SP and in the trigger 2 is a violation of good software engineering 1 principles and will be a maintenance problem.

ALTER TABLE tbl DISABLE TRIGGER trg

http://doc.ddart.net/mssql/sql70/aa-az_5.htm

I don't 2 understand the meaning of your 1st paragraph 1 though

Since you indicate that the trigger contains 11 logic to handle all updates, even manual 10 updates, then that should be where the logic 9 resides. The example you mention, wherein 8 a stored procedure "will take care of this 7 logic" implies duplicate code. Additionally, if 6 you want to be sure that every UPDATE statement 5 has this logic applied regardless of author, then 4 the trigger is the place for it. What happens 3 when someone authors a procedure but forgets 2 to duplicate the logic yet again? What happens 1 when it is time to modify the logic?

Not sure if this is a good idea but it seems 3 to work for me. Transaction should prevent 2 inserts to the table from other processes 1 while trigger is disabled.

IF OBJECT_ID('dbo.TriggerTest') IS NOT NULL
 DROP PROCEDURE dbo.TriggerTest
GO

CREATE PROCEDURE [dbo].[TriggerTest]
AS
BEGIN TRANSACTION trnInsertTable1s
;
DISABLE TRIGGER trg_tblTable1_IU ON tblTable1
;
BEGIN -- Procedure Code
    PRINT '@@trancount'
    PRINT @@TRANCOUNT
    -- Do Stuff

END -- Procedure Code
;
ENABLE TRIGGER trg_tblTable1_IU ON tblTable1

IF @@ERROR <> 0 ROLLBACK TRANSACTION
ELSE COMMIT TRANSACTION

Do not disable the trigger. You are correct 5 that will disable for any concurrent transactions.

Why 4 do you want to disable the trigger? What 3 does it do? WHy is the trigger casuing 2 a problem? It is usually a bad idea to disable 1 a tigger from a data integrity perspective.

Consider rewriting the trigger to imporve 1 performance if performance is the issue.

I waffled a bit on this one. On the one 15 hand I'm very anti-trigger mostly because 14 it's one more place for me to look for code 13 executing against my table, in addition 12 to the reasons stated in the article linked 11 in the question post.

On the other hand, if 10 you have logic to enforce stable and immutable 9 business rules or cross-table actions (like 8 maintaining a history table) then it would 7 be safer to get this into a trigger so procedure 6 authors and programmers don't need to deal 5 with it - it just works.

So, my recommendation 4 is to put the necessary logic in your trigger 3 rather than in this one proc which, will 2 inevitably grow to several procs with the 1 same exemption.

I concur with some other answers. Do not 20 disable the trigger.

This is pure opinion, but 19 I avoid triggers like the plague. I have 18 found very few cases where a trigger was 17 used to enforce database rules. There are 16 obvious edge cases in my experience, and 15 I have only my experience on which to make 14 this statement. I have typically seen triggers 13 used to insert some relational data (which 12 should be done from the business logic), for 11 insert data into reporting table ie denormalizing 10 the data (which can be done with a process 9 outside the transaction), or for transforming 8 the data in some way.

There are legitimate 7 uses for triggers, but I think that in everyday 6 business programming they are few and far 5 between. This may not help in your current 4 problem, but you might consider removing 3 the trigger altogether and accomplishing 2 the work the trigger is doing in some other 1 fashion.

I just confronted the same problem and came 26 up with the following solution, which works 25 for me.

1. Create a permanent DB table that 24 contains one record for each trigger that 23 you want to disable (e.g. refTriggerManager); each 22 row contains the trigger name (e.g. strTriggerName 21 = 'myTrigger') and a bit flag (e.g. blnDisabled, default 20 to 0).

2. At the beginning of the trigger body, look 19 up strTriggerName = 'myTrigger' in refTriggerManager. If 18 blnDisabled = 1, then return without executing 17 the rest of the trigger code, else continue 16 the trigger code to completion.

3. In the stored 15 proc in which you want to disable the trigger, do 14 the following:

BEGIN TRANSACTION

UPDATE refTriggerManager 13 SET blnDisabled = 1 WHERE strTriggerName 12 = 'myTrigger'

/* UPDATE the table that owns 11 'myTrigger,' but which you want disabled. Since 10 refTriggerManager.blnDisabled = 1, 'myTrigger' returns 9 without executing its code. */

UPDATE refTriggerManager 8 SET blnDisabled= 0 WHERE triggerName = 'myTrigger'

/* Optional 7 final UPDATE code that fires trigger. Since 6 refTriggerManager.blnDisabled = 0, 'myTrigger' executes 5 in full. */

COMMIT TRANSACTION

All of this 4 takes place within a transaction, so it's 3 isolated from the outside world and won't 2 affect other UPDATEs on the target table.

Does 1 anyone see any problem with this approach?

Bill

you can use 'Exec' function to diable and enable 1 triggers from a stored procedure. Example: EXEC ('ENABLE TRIGGER dbo.TriggerName on dbo.TriggeredTable')