By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,959 Members | 1,196 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,959 IT Pros & Developers. It's quick & easy.

update statement not to fire trigger

P: n/a
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

Feb 1 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
--

Feb 1 '06 #2

P: n/a
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, 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
Feb 1 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.