Is there a way to disable a SQL Server trigger for just a particular scope of execution?

Question

In SQL Server 2005, is there a way for a trigger to find out what object is responsible for firing the trigger? I would like to use this to disable the trigger for one stored procedure.

Is there any other way to disable the trigger only for the current transaction? I could use the following code, but if I'm not mistaken, it would affect concurrent transactions as well - which would be a bad thing.

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ]  ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ] 

If possible, I would like to avoid the technique of having a "NoTrigger" field in my table and doing a NoTrigger = null, because I would like to keep the table as small as possible.

The reason I would like to avoid the trigger is because it contains logic that is important for manual updates to the table, but my stored procedure will take care of this logic. Because this will be a highly used procedure, I want it to be fast.

Triggers impose additional overhead on the server because they initiate an implicit transaction. As soon as a trigger is executed, a new implicit transaction is started, and any data retrieval within a transaction will hold locks on affected tables.

From: http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1170220,00.html#trigger

Answer 1

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

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

---

EDIT by Terrapin:

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

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

Answer 2

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

I suggest denying table update permissions if nothing else works.

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