While working with triggers, I noticed a couple of unexpected behaviors in SQL Server.

1. Let’s say you have an INSERT statement something like this:

INSERT INTO SomeTable VALUES (Col1, Col2)
SELECT Val1, Val2 FROM SourceTable WHERE 1=0

The select statement is never going to return any rows due to the where clause (always false) and nothing would be inserted into SomeTable. So, if there is an AFTER INSERT trigger on SomeTable, what do you think about it. Should it fire? Remember, there is not going to be any insert.  If your answer is NO ( as it was mine and few others too), think again. Because it DOES fire.

2. Another situation. Lets have the same INSERT statement but without any WHERE clause. So the query will be something like this:

INSERT INTO SomeTable VALUES (Col1, Col2)
SELECT Val1, Val2 FROM SourceTable

In this case if SourceTable has 100 records and there is an AFTER INSERT trigger on SomeTable, how many times that trigger should fire. Remember, there are going to be 100 new rows in SomeTable. If you answer is 100 (as it was mine too, again), check Books Online once more. The trigger is going to fire ONLY once.

One thing to note in both situations is that the LOGICAL tables have the correct information. That said, INSERTED logical table is going to be empty in first example and it will have 100 rows in second one. So, these logical table are your best bet. This behavior is same for DELETE and UPDATE after trigger too. Means, if you execute a delete or update statement which will not affect any row, triggers will fire regardless and it will fire once per statement NOT per affected row.

Hope this will helps. Enjoy!