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

[NiceDuck]

Hello,

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?

[NiceDuck]

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.

[TellMeWhy]

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

[NiceDuck]

@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. 



 

[NiceDuck]

I'm not sure if I'm correct, but as I read your trigger, you will have a conflict on the result there if you happen to have multiple inserts and updates with different Parent ID. It will end up giving the same AVG values to different parent records. 

[NiceDuck]

This should be my setup without the for each loop. I know that the table variable will surely not have any records on it on initial setup but I still keep the delete action block just for the sake of my paranoia.