Jump to content
  • 0

Getting Average of all related records along with the newly inserted/expected record (trigger)




I notice that when we are using the aggregate functions on the triggered action, we can easily combine the results for the SUM and COUNT to the value of the #inserted, however, if we tried to use the same AVE, we will be getting a wrong average results.

This one is wrong


Any Ideas?

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0
13 hours ago, NiceDuck said:

NVM, I figured it out. 

Turns out that I will need to compile all the records first into a table variable then get the average values from there.


Yeah AVG + AVG is different than just the whole AVG.

I'm confused, tho, why the for each is needed?

This works on mine, but, they do need the Inner Joins, is the for loop a better, and efficient way than joins? I believe each has different effect to performance, but, I'm not aware of which is faster to execute


Link to comment
Share on other sites

  • 0

@TellMeWhy The For Each?

Good question. 

Lets just say that it is a way of making sure that the trigger will still work on the cases where there are more than 1 records inserted or updated (Bulk Insert/Update). Most of the trigger setup is design to accommodate only 1 record at a time and tend to fail or return an incorrect result during bulk inserts and updates.

That is why I am using a 'for each' loop so the trigger blocks will process the contents/records of the #inserted 1 by one. 

Of course, there are better ways to setup your trigger to optimize it and make it adaptive to bulk inserts. Still, let just say that the use of 'For Each' loop is a 'default' or 'basic' way and works most of the times. It kind of slows your trigger though, specially when lots of records are being involved. 


Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...