title: Is there a way to disable a SQL Server trigger for just a particular scope of execution?
body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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 } [ ; ]
  </code></pre>
  
  <p>If possible, I would like to avoid the technique of having a "NoTrigger" field in my table and doing a <code>NoTrigger = null</code>, because I would like to keep the table as small as possible.</p>
  
  <p>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.</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>From: <a href="http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1170220,00.html#trigger" rel="nofollow noreferrer">http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1170220,00.html#trigger</a></p>