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

I am working on SQL Server 2008 R2.

I have a table benefit which has a AFTER INSERT, UPDATE trigger named tiu_benefit.

I want to write an UPDATE statement for this table to update 1 row but I dont want its trigger to fire. I know I can disable trigger before UPDATE and then enable the trigger after UPDATE :

DISABLE TRIGGER tiu_benefit ON benefit;  
GO  
UPDATE benefit SET editor = 'srh' where benefit_id = 9876
GO
ENABLE TRIGGER tiu_benefit ON benefit;  
GO

But this disable and enabling trigger will affect all users logged in currently. So there is a possibility that another user run an UPDATE/INSERT while the trigger is disabled by my script which is not good. Thats why I only want to disable and enabling trigger for my current session. Is it possible? If yes please tell how.

Thanks

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

StackExchange Database Administrators Q#204339, answer score: 21