Insert Update trigger how to determine if insert or update

sql-server tsql triggers
MSIL picture MSIL · Apr 12, 2009 · Viewed 498.7k times · Source

I need to write an Insert, Update Trigger on table A which will delete all rows from table B whose one column (say Desc) has values like the value inserted/updated in the table A's column (say Col1). How would I go around writing it so that I can handle both Update and Insert cases. How would I determine if the trigger is executed for an update or insert.

Answer

gbn picture gbn · Apr 12, 2009

Triggers have special INSERTED and DELETED tables to track "before" and "after" data. So you can use something like IF EXISTS (SELECT * FROM DELETED) to detect an update. You only have rows in DELETED on update, but there are always rows in INSERTED.

Look for "inserted" in CREATE TRIGGER.

Edit, 23 Nov 2011

After comment, this answer is only for INSERTED and UPDATED triggers.
Obviously, DELETE triggers can not have "always rows in INSERTED" as I said above

Related questions

Need to list all triggers in SQL Server database with table name and table's schema

I need to list all triggers in SQL Server database with table name and table's schema. I'm almost there with this: SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj), isupdate = OBJECTPROPERTY( …

Read More
How to emulate a BEFORE INSERT trigger in T-SQL / SQL Server for super/subtype (Inheritance) entities?

This is on Azure. I have a supertype entity and several subtype entities, the latter of which needs to obtain their foreign keys from the primary key of the super type entity on each insert. In Oracle, I use a …

Read More
How can I edit values of an INSERT in a trigger on SQL Server?

I have the table Tb ID | Name | Desc ------------------------- 1 | Sample | sample desc I want to create a trigger on INSERT that will change the value of the inserting Desc, for example: INSERT INTO Tb(Name, Desc) VALUES ('x', 'y') Will …

Read More