Posted by Erland Sommarskog on 02/02/06 01:08

Dima Gofman (dg@cfa-solutions.com) writes:
> I have a trigger on UPDATE on a table. I'm running some maintenance
> UPDATE and DELETE queries which I want the trigger to ignore but at the
> same time I want other UPDATE queries that other users might be running
> to keep triggering the trigger. Is there a SET statement perhaps that
> I could set before my query? Or a clause in the UPDATE statement?
>
> This is on MSSQL 2000 server, on Win2k3

And how do you know that you don't need to fire the trigger? Because
you are to violate some business rules? :-)

In addition to David's suggestion to use ALTER TABLE DISABLE TRIGGER, a
trick is to have this check in the trigger:

IF object_id('tempdb..#trigger$skip') IS NOT NULL
RETURN

Before you run your maintenance operation, you would create this table.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]