update statement not to fire trigger

[Dima Gofman]

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

[David Portas]

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/m...S,SQL.90).aspx
--

[Erland Sommarskog]

Dima Gofman ([e-mail]) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx