Sql-server – SQL Server : How to disable trigger for an update only for your current session

sql servertriggerupdate

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

Best Answer

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.