Posted by David Portas on 02/01/06 15:49

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
--

[Back to original message]