Jump to content
  • 0

Trigger Action using Group by and Having


Tanager
 Share

Question

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!

TriggerActionHavingGroupBy.PNG

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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.

image.thumb.png.1065aba20f908da687a489aa4e2a8012.png

image.thumb.png.c876bff97fd0262a0e6caa8054f04bfc.png

Hope this helps.

Link to comment
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...
 Share

×
×
  • Create New...