One of my frieds told me that INSERT trigger will not fire if no data are added to the main table. It is a mistake that some people think that triggers will not fire if no row is affected. Note that trigger is event based and not data based.

Here is a proof that trigger fires eventhough no row is affected.

Consider this table

create table testing(prod_id int , prod_name varchar(100))

and create this trigger

create trigger test_trigger on testing
for insert
as
raiserror ('The insert trigger is fired',10,16)

Now execute the following code that adds no row to the table

insert into testing(prod_id ,prod_name )
select 1,'test' where 1=0

The message retuned is

The insert trigger is fired

(0 row(s) affected)

So it is clear that when a statement is valid, trigger will fire no matter how many rows are actually affected