How to prevent SQL update trigger from firing on insert?
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
Software/Hardware used:
Software/Hardware used:
ASKED:
April 22, 2008 5:34 PM
UPDATED:
April 23, 2008 2:33 PM
Answer Wiki:
You can merge both triggers into one trigger for update and insert:
<pre>
CREATE TRIGGER <trigger name> ON TableA for INSERT,UPDATE</pre>
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:
<pre>IF UPDATE (column)</pre>
2) You can check if the deleted table is not empty, which means this is an update:
<pre>IF EXISTS (SELECT 1 FROM deleted</pre>
This way the trigger will not fire the second time, just make sure that the 'recursive triggers' db option is OFF:
<pre>exec sp_dboption <you database name>,'recursive triggers'</pre>
Hope this helps,
Michelle.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
