Why would Triggered Action "Select from #inserted" actually give multiple values?

[JamieJM]

Hi All - I often receive the 2116 error ("Only one value is expected") when trying to create a Triggered Action like the below.

I was under the impression that a Triggered Action is triggered every time a new record/row is inserted/updated/deleted into a table. So, I'm having trouble understanding why the 2116 error would ever even occur. What are examples of situations where multiple records would be captured? If I have a table with one column titled "Cohort_Start_Date" and I add a record, I will only have one value. It is not a list field, it is a date.
 

The 2116 error happens in less complicated scenarios too, not just in this IF statement.

The only thing I can think of is if you have bulk add/delete/update enabled in a datapage. But even then I would think the triggered action should apply to all of them?

I feel like I am missing a critical piece of knowledge about how Caspio works.

Thank you for any help you can offer!!!
Jamie

 

 

Edited April 12 by JamieJM
Duplicate image

[CoopperBackpack]

Hello @JamieJM,

You are correct, the #inserted virtual table can contain multiple records. For example, an Application Task can insert multiple records into the Table and this Table can have a Trigger on the insert action.

And of course, it is possible to use bulk edit/bulk delete on the Reports. As a result, the #inserted to #deleted virtual tables will contain multiple records.

I have no precise answer about the case on your screenshot but let me share my thoughts.

In the IF statement, the condition returns True or False so, in case of multiple inserted records, this Trigger will fail without TOP 1.

Actually, it is mostly not related to Caspio but to the SQL Server and correct syntax that should be used for different use cases.

 

Other cases that come to mind then the '2116: Only one value is expected' error occurs:

1) In comparisons, e.g. when a scalar value is compared with the set of data

This can be fixed by using the TOP 1 clause in the SELECT statement or by using the IN comparison operator if it is needed to compare with the set of data.

2) It is possible to store a single value in a scalar variable (so not in a Table variable)

 

And in most cases the IF Then block can be replaced with a better design. Please note that from the performance point of view, it is better to put conditions in the WHERE clause.

For example:

 As you can see, the TOP clause can be skipped with this design.

Feel free to update this thread if you have further questions. 

[JamieJM]

Thank you!! This is really helpful.

I guess my next question is, if you change "Select from Top 1" to be "Select from Top 100" will is perform the triggered actions on each record among 100 records? Or is it saying, where the top 100 records ALL have xyz criteria, execute the insert. ?

 

[cheonsa]

On 4/17/2023 at 8:02 AM, JamieJM said:

Thank you!! This is really helpful.

I guess my next question is, if you change "Select from Top 1" to be "Select from Top 100" will is perform the triggered actions on each record among 100 records? Or is it saying, where the top 100 records ALL have xyz criteria, execute the insert. ?

 

Hi @JamieJM,

The trigger executes to the first 100 records that satisfy the given criteria.

[Flowers4Algernon]

Hello! Just wanted to share this article that will be helpful as you go along: https://howto.caspio.com/tables-and-views/triggered-actions/