Question

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

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.