How to disable SQL Server trigger for current process only?

Question

Can someone double-check my trimmed-down example? When the Documents table is updated, I want it update the EntryDate on the Queue table. However, I don't want the trigger on the Queue table to fire for THIS PROCESS ONLY. Meaning, if some other process updates the EntryDate on the Queue table while this process would be running, I WOULD want the trigger on the Queue table to fire for that particular transaction. I'm not sure if I need to do any kind of lockout for the below code to ensure no other process gets its toes stepped on. Thanks!

create trigger [dbo].[Documents_trigUpdate] on [dbo].[Documents]
for update
as

begin transaction

alter table [Queue] disable trigger Queue_trigUpdate
update  [Queue] set EntryDate = getdate()
alter table [Queue] enable trigger Queue_trigUpdate

commit transaction

go

score 12 · Accepted Answer · answered Aug 01 '12 at 19:53

You'll want to avoid disabling then re-enabling the trigger, because that will affect all processes and not just the current process. if you put some type of lock, you'll serialize your code which could become a bottleneck in your system.

An alternative would be to use the Context_info of the current connection, and change the trigger on the Queue to not execute if a specific context has been set.

This technique is covered in this article, and an example is provided below:

-- Creating the queue trigger
create trigger [dbo].[Queue_trigUpdate] on [dbo].[Queue]
for update
as   

declare @Cinfo varbinary(128)  
select @Cinfo = Context_Info()  
if @Cinfo = 0x55555  
    return  

print 'Trigger Executed'  
-- Actual code goes here  
go

To prevent the trigger from being executed you can do the following:

create trigger [dbo].[Documents_trigUpdate] on [dbo].[Documents]
for update
as

-- Save the original context info    
declare @originalContextInfo VARBINARY(128)
set @originalContextInfo = Context_Info()

set Context_Info 0x55555 
update  [Queue] set EntryDate = getdate()

-- Reset the context info
set Context_Info @originalContextInfo 
go

Ah wow, this is much cooler than my WHERE clause method of doing this. Time to make some changes. Thanks! — Aushin, Aug 01 '12 at 20:34
But wouldn't it be possible for another process to hit the Queue_trigUpdate when the context_info is set to 0x55555 during the Documents_trigUpdate process? — Curtis, Aug 02 '12 at 01:28
Never mind. Just read up on context_info and learned it only applies to the current process. I tried it out with a "waitfor delay '00:00:10'" command to verify another process could still execute the Queue_trigUpdate and it worked like a charm. Took some coaxing since it's a SQL Server 2000 database, but found a solution for that issue (http://stackoverflow.com/questions/2237499/sql-server-trigger-loop). Thanks! — Curtis, Aug 02 '12 at 01:46

score 0 · Answer 2 · answered Aug 01 '12 at 19:53

I've done this before and I don't know if it's the best way to do it or even exactly what you want, but you can use CONTEXT_INFO().

In the process you want to ignore, run:

DECLARE @ContextInfo varbinary(128)
SET @ContextInfo = CAST('SomethingMeaningfulToYou' AS varbinary(128))
SET Context_Info @ContextInfo

Then, in your trigger, if it's say, updating rows.

CREATE TRIGGER X 
ON TABLE Y 
AFTER UPDATE AS
BEGIN
    UPDATE Something
    SET something = somethingelse
    WHERE yourconditions AND 
         (CONTEXT_INFO IS NULL OR CONTEXT_INFO() <> CAST('SomethingMeaningfulToYou' AS varbinary(128)))
END

score -2 · Answer 3 · answered Aug 01 '12 at 19:52

-2

You should be able to make a call to DISABLE TRIGGER perform some work and then ENABLE TRIGGER once you are complete

answered Aug 01 '12 at 19:52

mreyeros

4,359
20
24

1

This will have bad repercussions for any other procedures. The question requires that the trigger only be disabled for the scope of this procedure alone. – Brisbe Jun 08 '15 at 14:26

How to disable SQL Server trigger for current process only?

3 Answers3