First of all what microsoft tells us about triggers:
"A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected."Taken from: http://msdn.microsoft.com/en-us/library/ms189799.aspx
There is something interesting in above sentences, I suppose many of us would not understand correctly the last sentence: "These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected". I have also walked through these problems some days before.
Let me explain that sentence. So for example if we have a table named extremedev with coulmns: id(int, primaryKey) and test(int). We will have some data in that table:
In order to see if the trigger was actioned we will create a trigger like below:
Now, if you will do the following:
And if you take a look at the messages you will see:
(1 row(s) affected)
Which means that your trigger was fired and everything was processed normally.
However if you do the following:
You will have in messages the following:
(0 row(s) affected)
Which means that your trigger was fired which actually means that if your trigger had a lot of logic he would do that without any sense, and you also can see that table inserted has no rows in it. Respectively if you would like to do something like an integrity check in our example, for example we would like to check if values inserted in the table test2 exists in table test, then don't do it like this:
Why not do do it like above? very simple, because if you will do an update like above then table inserted will not have any rows and that will lead to that that an error will be thrown. This is why you need to replace above with the following:
So now you understand that sentence from microsoft, trigger is fired even when it is not really needed, which is a little odd from my point of view.
Let me know your opinion about this, leave a comment. Subscribe for more interesting posts.