Question

  Asked: Apr 22 2008   5:34 PM GMT
  Asked by: Leo W


How to prevent SQL update trigger from firing on insert?


SQL Server, Triggers, T/SQL, SQL Server 2005

Category: SQL Server 2005, T-SQL

I have an insert trigger on TableA that updates Column1 in TableA. I also have an update trigger on TableA that I don't want to fire, or at least not to execute certain code, when a new record is inserted in the table. How can this be achieved?

I don't want to use a computed column instead of the update statement in the insert trigger -- the value has to be static, and I don't want performance hit when selecting from the table.

Thanks,
Leo

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window

Answer Wiki (Improve, edit or add to this answer)


 RATE THIS ANSWER
+1
Click to Vote:
  •   1
  •  0



You can merge both triggers into one trigger for update and insert:

CREATE TRIGGER <trigger name> ON TableA for INSERT,UPDATE


If you have different logic for update and for insert, you can check Inside the trigger which command fired it. There are two ways to do so:
1) If you have a column that is always updated, you can ask:
IF UPDATE (column)

2) You can check if the deleted table is not empty, which means this is an update:
IF EXISTS (SELECT 1 FROM deleted


This way the trigger will not fire the second time, just make sure that the 'recursive triggers' db option is OFF:
exec sp_dboption <you database name>,'recursive triggers'



Hope this helps,
Michelle.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on SQL Server and Database.

Looking for relevant SQL Server Whitepapers? Visit the SearchSQLServer.com Research Library.


Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register