Solved 70-461 Querying SQL Server 2014 - Create and Alter Triggers Question

---

Hi,

In the sample code below (see show notes) from the "CREATE and ALTER DML TRIGGERS' video, line 7 states: "IF @@ROWCOUNT = 0 RETURN;"

The explanation provided in the video is a bit unclear:

"So now, the first thing I'm actually telling it, that if you do a row count.Okay, so in other words, as I run through this statement, if I do a row count,I want it to begin with zero and then to show me what that return is going to be."

Microsoft defines @@ROWCOUNT as: Returns the total number of rows affected by the last statement, and RETURN as: Exits unconditionally from a query or procedure.

By that definition, wouldn't @@ROWCOUNT always be greater than 0, because the AFTER TRIGGER would only execute if the INSERT or UPDATE is successful? And therefore making line 7 redundant as it would never be executed?

Thanks.

@imran-dossa ,

It took me a while to remember myself but able to put it together on why I did what I did there.

By the definition you quoted, yes I believe you're right! So great job! It appears it can be redundant

The context though is based on when the DML trigger fires. It is not only when there is a successful insert or update but... "These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected." So on any valid event will do.
I wanted it to check and start with 0 regardless.

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.

@imran-dossa,

Glad you're a member. Remember that this exam has been updated as well as the content. We have a refreshed show on the SQL Server 2016 edition as well!