Trigger Action using Group by and Having

[Tanager]

Hello,

Issue:  My trigger action is not processing the Having statement in the Where clause. 

I barely know SQL and while the 30 minute Caspio webinar on Trigger Actions was helpful, the action I am attempting was not covered. 

I have a parent table and a child table, their common field is NTAID.

I am trying to use a trigger action on Inserted/Updated/Deleted to quantify the number of records in the child table for the NTAID and assign a category to each parent record.  The purpose of the categories is I need to use the field in the parent record to create filter views of the parent table for another data page.

Some portions of my Update trigger actions work - I have four Updates. The Update for categorizing "Regional" is not occurring under any circumstances, and "Local" categorization is occurring even if the Count in the Having clause exceeds the limitation.  I reversed the order of these two Updates and that did not alter the outcome. 

The other two updates run flawlessly.

I appreciate any assistance in the proper set-up of these trigger actions. Thank you!

[BaySunshine]

Hi Tanager,

In both of your triggers where Scale is set to either Regional or Local, you are counting inserted values in the Having condition. This value is never going to be more than 3 because it is being compared to only 1 inserted value. In case of less than 3, it is updating always since the inserted value is always going to be 1, which is less than 3.

So, to fix this issue, join both inserted and the table values. Then it updates the data correctly. Also, since you want to run this action when delete happens too, you should compare it with deleted table also. I created one update with the deleted table. You can create the second one following the same example.

Hope this helps.

[Tanager]

Thank you BaySunshine for your response and suggested edits! 

That was a big help in understanding what is probably obvious to some folks but not me.

J