Jump to content
  • 0

Expressions in Triggers


RBobowski

Question

I'm looking for information about the "expression" feature within triggers. I have not been able to find anything other than a reference to expression aliases in the Caspio Bridge 12.0 release notes.

https://howto.caspio.com/release-notes/caspio-bridge-12-0/
" Expression names can now be renamed for easier reference in other areas. "
" When saving or validating a Triggered Action, incomplete expressions in a SELECT block will be automatically removed. "

I discovered the feature when working with a select statement embedded in an If statement. I assume these expression alias can be used elsewhere in the trigger code. Unfortunately, I have not found a means to use them.image.png.a365e8793cb07175076dcaff43dd40f8.png

I created the following in an attempt to figure out how to use the "expression" feature. The IF block passes validation. I saved it along with additional code that modifies the inserted record (see below). Caspio doesn't complain about the simulated no operation (NOP) I created by using an IF 'exists' comparison for select statement return value
image.png.71519df07f8282062466887530998bba.png

This is a far as I have been able to reverse engineer the "expression" features.

I have tried to access the alias (Var1, Var2, and Var3) elsewhere in the trigger withing many different contexts. I tried within the IF and THEN clauses where they are defined. I tried within subsequent Update and Select statements. etc.....


Use Case
I have a table that tracks eight different topic scores and an award for each topic by person. Awards qualifications for each topic are based on several factors (topic, score, when scored, gender, and age).  The scope and qualification factors for each award are contained in a reference table. I implemented the triggers needed to identify and update or insert the correct the awards. It works as expect.

A different qualification/criteria row may apply to identify the correct award for each topic for any person. Without going into all of the details you will not be able to identify whether or no the data has been properly normalized. You will have to trust me that it has been. Besides, I have other use cases that would benefit from variables. There are ten large Update statements used to the identify the award for each topic as well as updating summary awards in another table. The logic is complex enough along with the size of each update statement that maintenance of the code will be cumbersome and error prone. In fact, I haven't yet combined them into one update and insert trigger. I am concerned about perfomance due to the number of select and join statements. I would rather clean this up using something like variables before deploying them in one update and insert trigger.

Ideal Solution
Ideally, I could use variables to gather the criteria based on context for the person and use them within subsequent IF statements. This would significantly shorten the length and complexity of the trigger. Based on my reading of forum posts, Caspio help documentation, and viewing of YouTube videos I am nearly certain that triggers do not support variables. Except that maybe "expressions" within triggers could solve my problem.

An Example
I included the following to help the reader better understand the ideal solution that trigger "expressions" may solve. The following test uses dummy columns to hold values for use in the trigger. I think this is a dangerous kludge. I am not using this method in my production triggers. For example, three dummy columns called "ShortCountryCode", "NumbersTests", and "TextTests" were added to the table. TextTests is assigned the value from the CountryCode field in the first Update statement. It is used in the next Update statement as part of a concatenated string to update the ShortCountryCode field. TextTests is assigned the string "SumeGamaa" in the last Update statement. The NumbersTests column is used to execute the trigger from the datapage.

image.thumb.png.00cc406c6fc965cae9e4b2fac7239021.png

Ideally, variables would be used instead of dummy columns.

Here is the entire trigger with the test "expression" code. The IF statement that uses three expressions is not used in the last three Update statements. Note that Caspio doesn't complain about this trigger.

image.thumb.png.e1f2dfec7230c19fa68d53746f50d6fb.png

I have attached the table and a datapage I used for my testing. The data is public domain that I downloaded from https://www.kaggle.com/

 

image.png

sb_dp_internet_country_codes_2018-May-13_1706.zip

sb_t_internet_country_codes_2018-May-13_1705.zip

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

Hello RBobowski,

Expressions can be used in Select statement the way you did. You could also use 'Equal' or any other comparison operator instead of 'Exists' and compare the value with the aggregate calculated in the Select statement.

Triggers do not allow usage of variables in Caspio like we do in any other programming language. You can have an alias for a field in the Select statement and use that alias in other parts of the same query such as in Where, Group By etc but cannot use it in other trigger actions such as insert, update or delete.

Hope this helps.

Regards,

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...
×
×
  • Create New...