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