SQL Server : How to disable trigger for an update only for your current session?

To solve your problem, we have to take a programmatic approach to the problem. There are two routes you can go here. The reason for needing these approaches is because you cannot disable a trigger for a particular statement, it can only be disabled for the entirety of the table.

Option 1: Context_Info()

Samuel Vanga on MS SQL Tips had a great example:

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

Now when Samuel does not want want the trigger to execute, they use this:

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

Context_Info uses the following system views to grab information regarding the current session:

• sys.dm_exec_requests

• sys.dm_exec_sessions

• sys.sysprocesses

The ideology here is that the binary string you are setting is exposed only to the current session, so when the trigger executes during your session, it will see the scope and variable setting of the Context_info function and it will jump to the escape portion of the trigger instead.

Option 2: Temp Table

Itzik Ben-Gan has a great solution in his book "Inside Microsoft SQL Server 2008 T-SQL Programming: T-SQL Programming" which is also in his later book T-SQL Querying. The primary problem with this over the context_info function is the minor TempDB overhead.

To spoil the surprise but not ruin the plot of the books (I felt they are worth purchasing and reading), you will alter your trigger.

Your trigger should perform a check for a temporary table. If the temporary table exists, the trigger should know to end and not perform the actions.

In the update statement you want to perform, create the temporary table first. It will be seen in the same transaction as the trigger and it will cause the trigger to ignore your statement.

Example of trigger:

CREATE TRIGGER TRIGGERNAME ON TABLENAME for INSERT AS

IF OBJECT_ID('tempdb..#FAKETEMPTABLE') IS NOT NULL RETURN;
GO

Example of beginning statement when you don't want the trigger to run:

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);

Putting it altogether for your example:

ALTER TRIGGER tiu_benefit ON benefit FOR 
... 
AS
...
IF OBJECT_ID('tempdb..#FAKETEMPTABLE') IS NOT NULL RETURN;
--... rest of code here
GO

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);
UPDATE benefit SET editor = 'srh' where benefit_id = 9876;
GO

I did some testing on this and I think you'd be fine if you run your process in a single transaction.

BEGIN TRANSACTION
GO

DISABLE TRIGGER tiu_benefit ON benefit;
GO

UPDATE benefit
SET editor = 'srh'
WHERE benefit_id = 9876
GO

ENABLE TRIGGER tiu_benefit ON benefit;
GO

--Decide to commit or rollback

--commit
--rollback 

In my testing, I only highlighted and executed the BEGIN TRANSACTION and the DISABLE TRIGGER first. I then opened up a new (second) query window and tried to run various DML statements (SELECT, INSERT, UPDATE DELETE) against the base table. All attempts to access the base table in the second query window waited on the locks held by the window with the explicit transaction. Once I committed (or rolled back) my explicit transaction, the second window was able to access the table.

I would use either CONTEXT_INFO or the newer SESSION_CONTEXT. Both are session based values.

• CONTEXT_INFO is a single VARBINARY(128) value. This has been available since at least SQL Server 2000. CONTEXT_INFO is viewable by anyone with VIEW SERVER STATE as it is a field returned by the sys.dm_exec_sessions DMV. I have used this one before and it works quite well.

  Set via SET CONTEXT_INFO
  Get via CONTEXT_INFO() or sys.dm_exec_sessions

  Depending on the type of value that you are storing to CONTEXT_INFO, there are some nuances to be aware of. I cover that in the following blog post:

  Why Doesn’t CONTEXT_INFO() Return the Exact Value Set by SET CONTEXT_INFO?

• Session_context is a key/value pair of SQL_VARIANT values. This was introduced in SQL Server 2016. The separation of values for different purposes is quite nice. Session_context is only viewable by the current session.

  Set this value via sp_set_session_context
  Get this value via SESSION_CONTEXT

One thing to consider regarding the local temporary table option and even the disable / enable Trigger option: both of those require some amount of locking and tran log activity. Both of those options increase the potential for contention, even if minimally. The two "context" options should be lighter weight / memory-only.