Consolidate rows via trigger

[Benades]

Hi there, I need to create a trigger that sums different column values per user.

I have multiple users on a table (below) and would like to on another table insert sum the number values of each a_user per column and insert it into another table as a combined history.

ie user1: 106 | 60 | 73

user3: 96 | 45 | 71

[TellMeWhy]

9 hours ago, Benades said:

Hi there, I need to create a trigger that sums different column values per user.

I have multiple users on a table (below) and would like to on another table insert sum the number values of each a_user per column and insert it into another table as a combined history.

ie user1: 106 | 60 | 73

user3: 96 | 45 | 71

Here's a sample

 

Child Table MUST HAVE a unique ID, you'll see why.

 

First Part of the Trigger

This is for inserting to PARENT Table Names that DO NOT EXIST yet on the Parent Table

 

1.) Set a VARIABLE,  Select from the Child Table, and Inner Join with the #inserted, or vice versa so we can use both fields in the WHERE.  Important here is to select Expression on the fields, then append the aggregation block (COUNT/SUM, ETC) use WHERE so you'll only SUM the Name in the Child Table that is the same as the #inserted

2.) Select From WHERE NOT EXISTS, select the field you want to check, in this case I want to check the Parent Table's Name Field, and then I'll check for values that are equal to #inserted.Name

If it does not exist, it will proceed with inserting the values I've set

3.) For Total Allowance, I'll check first if it's not a number (because the sum can be a NULL), if it's not, I will output 0, else, the SUM. Then, you'll add the #inserted.Allowance, that's only named Allowance because you're already Selecting From #inserted in the Insert Into Block. Check if it's not blank, if it's not, get the value, else 0.

 

2nd Part

just below the Insert Into Block

 

 

What this does is UPDATE the values that are in the Parent Table.

1.) Inner Join with #inserted.Name then parent.Name

2.) Use Case When, same concept as the one in the INSERT INTO. In this case, the WHERE checks if the Child.Name that is getting summed up is equal to the Parent.Name. We're also excluding the Child ID that's being updated from that SUM (#inserted.Child_ID not equal to Child.Child_ID) because we're ADDING the NEW VALUE instead

 

This looks complicated, but, it's actually not.