Jump to content
  • 0
Cstricker

Aggregate (SUM) from multiple inserted rows in a trigger

Question

I am trying to craft a trigger that will sum inserted rows (multiple rows) and put the total in another table.  I cannot seem to find a way to select multiple rows from the #inserted table though.  

To explain further, I have three tables, Master, Details, Totals.  When a new row is inserted into the master table an insert trigger fires on the master table and several detail rows are copied from a template table into the detail table, where each row contains a price and quantity.  The price and quantity are multiplied to create a total for each row through a formula field in the details table. 

On the details table there is a triggered action that on insert, update or delete will sum the cost of the total formula column for all rows and put that total into a totals table.  I have to sum the #insert and existing detail rows separately and add them together to get the total.  This all seem to work well when inserting one row but when inserting multiple rows only the first row from the #inserted table is totaled.  That makes sense because the trigger on the detail table  doing the sum specifies Select top 1 from #inserted when summing.  However, I need to sum multiple rows from #inserted and the trigger is not letting me remove the top 1 clause stating that it only expects one row from the select query.  I have tried putting the select query for the inserted table within a sum block and alternatively putting a sum expression on the result of the select query.  In both cases the trigger will not save stating that the select must specify top 1.

I have attached some screen prints of what I am doing in the trigger and the issues with each scenario. 

TriggerValidationError.docx

Share this post


Link to post
Share on other sites

9 answers to this question

Recommended Posts

  • 0

Hello!

Can you provide me with more details regarding your workflow?
You can write me in Privat.

I need some more information about your first trigger and how do you insert a few rows into the Details table.

Share this post


Link to post
Share on other sites
  • 0

How is using Count() going to give me a SUM()?  I am not really understanding your directions.  I am needing the SUM of the cost of the rows in the #inserted table in a trigger.  There will be multiple rows in the #Inserted  table. 

 

Share this post


Link to post
Share on other sites
  • 0

You can try to build this trigger not using IF-ELSE blocks but using WHERE block.
In this case, you can specify your condition in WHERE block like you do in IF part.

Also, it is very helpful to use JOINs to get the proper result sets and make aggregations with them.
You can check this article to get familiar wi this function: 
https://www.w3schools.com/sql/sql_join.asp
https://www.w3schools.com/sql/sql_join_inner.asp
https://www.w3schools.com/sql/sql_join_left.asp
https://www.w3schools.com/sql/sql_join_right.asp
https://www.w3schools.com/sql/sql_join_full.asp

Share this post


Link to post
Share on other sites
  • 0

In my case it was more a SQL syntax issue.  

I have been writing SQL for a long time, 20 years.  Using Caspio's triggers are a very small subset of SQL obviously but I was getting caught up in  their syntax.  When I thought about it in terms of SQL I figured it out. 

image.png.b65797128dc154aa1c6d403d3104a632.png

 

The group by groups all the inserted rows, by a particular value, in this case the header id and the sum agregate sums them which is exactly how you would do it in just plain SQL.  Previously I was not grouping but when I used the group by it worked. 

 

 

Share this post


Link to post
Share on other sites
  • 0

Cstricker, thank you so much for your quick reply. Your explanation makes sense. I will give it a try and I hope to be able to reproduce what you described and get the correct result. Also, thank you for sharing the screenshot; it helps a lot.

Thanks again.

Share this post


Link to post
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.

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

Loading...

×
×
  • Create New...