Re: update statement not to fire trigger

Dima Gofman wrote:

> 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

Not without rewriting the trigger. If the business rule isn't universal
then it shouldn't be in a trigger. IMO that consideration should be the
first thing you think about when deciding whether to create the
trigger.

You can do this:

BEGIN TRAN
ALTER TABLE tbl DISABLE TRIGGER ALL
....

ALTER TABLE tbl ENABLE TRIGGER ALL
COMMIT TRAN

but other users will be blocked for the duration of the transaction.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx 
--

.

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)