Re: update statement not to fire trigger

Dima Gofman (dg@xxxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Records not inserting...
    ... > I am totally new to SQL server. ... However, in a trigger, would I be ... test the effects of certain data modifications and to set conditions for ... UPDATE statements. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Help for an Oracle Newbie
    ... application I wrote using SQL Server ported over to Oracle. ... Obviously there are several built in data types in SQL Server that do ... Of course I had to use a different name for the trigger under each ...
    (comp.databases.oracle.server)
  • Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !
    ... >into SQL server, the record exist anyway! ... >If you refresh the data you will see the record... ... it gets refreshed after trigger excecution automatically. ... I don't know your monitoring system, but I guess that removing the result ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance and second table
    ... There is lot of evaluations ... SQL Server doesn't know that the data in both tables is ... down because the trigger has to be executed. ... keeping other queries blocked for longer ...
    (comp.databases.ms-sqlserver)
  • Re: Access Bug with SQL Server identity columns
    ... Even though SQL Server since version 2000 has had a reliable ... The work-around I've always used is to make sure that no trigger inserts into ... a table that contains an IDENTITY column, and use some other unique identifier ... >create a form on table1 with a subform on table2. ...
    (comp.databases.ms-access)