Trigger Gotchas

I found these weird behaviors in SQL Server. Well, weird because I never expected it this way. And whoever I asked, even they thought its weird.

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

INSERT INTO SomeTable VALUES (Col1, Col2)

SELECT Val1, Val2 FROM SourceTable WHERE 11

Now, the select statement is never going to return any row due to the where clause (always false) therefore, no new row in SomeTable table. 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. And that’s why I call it weird.

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

Now, if SourceTable has, say, 100 records and there is an AFTER INSERT trigger, 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 help you to avoid this trap.

Enjoy!