Hi @Ronnie-Wong ,
Thanks for the quick reply.
So, after re-reading the Microsoft documentation again, I guess what initially had me confused was the statement you quoted:
"These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected."
Because later in the documentation, it says:
"AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully."
I think I understand where I went wrong. If the UPDATE statement used a WHERE clause and 0 records were updated, in that case, the UPDATE statement would have "executed successfully" (even though no records where modified), thus causing the AFTER TRIGGER to execute. In this case the @@ROWCOUNT would be 0.
As for the statement IF @@ROWCOUNT = 0 RETURN;, which I thought was redundant, Microsoft explains the statement as:
"Triggers work in transactions (implied, or otherwise) and while they are open, they lock resources. The lock will remain in place until the transaction is confirmed (with COMMIT) or rejected (with a ROLLBACK). The longer a trigger runs, the higher the probability that another process will be blocked. Therefore, triggers should be written in a way to decrease their duration whenever possible. One way to achieve this is to release a trigger when a DML statement changes 0 rows."
So, in order to decrease the triggers execution time (where the trigger wouldn't have any impact, because 0 rows are affected), using the statement: IF @@ROWCOUNT = 0 RETURN; would end the trigger execution and remove any lock on resources. This would be quicker than if it had to execute the entire trigger statement.
Thanks for your help.