Sum Function in Triggered Actions

[MyMelody]

In my table, I have a field for Expenses. Upon insert, I want to update the other table with the total sum of all expenses per user. 

I have set up a triggered action. However, I can't get it to work because I'm having this error when I use the Sum Function: An aggregate may not appear in the set list of an UPDATE statement. (error code: SQL157)

Can you please help?

Thanks. 

[cheonsa]

Hi @MyMelody,

 

I  got the same problem with you last week. The solution I thought of is to create another table. It will be like storage of whatever I insert in the main table. In the trigger, I used that table as a reference to sum up all the expenses. 

Please see the screenshot I created:

 

Upon Insert in the MainTbl, it will be also inserted to the Table_2 (storage table).  The total expenses is the sum of all Expenses inserted in the Table_2 per ID.

 

Hope this helps.

 

-kristina

[RuisiHansamu]

Hello! Just wanted to share this reference from the SQL Server helper for every one's reference just in case you encounter this sort of error messages: http://www.sql-server-helper.com/error-messages/msg-157.aspx 

Cheers!  

[Flowers4Algernon]

Hi, you might also find these links helpful:
1. 

2. https://howto.caspio.com/tables-and-views/triggered-actions/