Trigger Gotchas

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!

Advertisements

One thought on “Trigger Gotchas

  1. Hey,Though I am convinced by the way you think, but i had answers to both of these correct, my perception being that the, After trigger is called whenever the command is called, not necessary that it updates 0 or 1000 records. Also even if your statment updates 1000 or 1 lac records, the trigger will be called only once, as you have called the command once, basically it depends how many times you call that command (insert/update) watever.Abhishek

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s