Trigger Action to update child table

[CesarG]

Hi, I am new to Caspio and this is why I need help even though my problem being simple.

I need to set up a triggered action to update a child table whenever any field of the parent table is updated by the user. There is no calculations, nothing complex. I Just need that when the user updates any of the data of a certain transaction record (several fields in the parent table) the triggered action updates the equivalent record in the child table accordingly (even though in the child table there are only fewer fields). 

 

Before anyone asks why I dont consolidate both tables in a View, it is because I have more than 20 tables, and I need to "copy&maintain" certain fields from all records from all tables in a single place/table to do a pivot table report.

Thanks in advance. 

Best, Cesar

[MayMusic]

The best is yet to prevent redundant data in your table and include those values only in the parent table and then to generate your pivot you can create a view to join your tables and use it as the source of the report page. 

But just to answer your question, when a record is being updated/inserted/deleted in a triggered action that manipulated record gets saved to a temporary table called #inserted. What you can do is to compare the column value from the #inserted table with the actual table value and if changed then update the child table with the same ID field.

[cheonsa]

Hi @CesarG,

Here is a sample Triggered Action for reference:

Here is the link to the howto article: https://howto.caspio.com/tables-and-views/triggered-actions/

 

[astroboy]

Since you've mentioned that you have parent and child records, I suggest considering  to enforce referential integrity on the relationship established between the fields. For more information, here's a How To article about Relationship Settings:

https://howto.caspio.com/tables-and-views/relationships/relationship-settings/

 

[RuisiHansamu]

Yes, @astroboyis correct. To add on this thread, I believe there is a similar forum post that you can also check out for this case. I think this could help you out as well: 

 

[PeterQJ]

Hi! I have an issue very similar to this and I'm curious if there are known workarounds. My flow is like this:

(1) User submits a record using a Submission Form Datapage, which updates tblParent

(2) A triggered action on tblParent fills in some blanks on that table based on criteria from other tables.

(3) Not yet functioning. Create a new record on tblChild using the GUID from tblParent as a foreign key.

tblChild is a lookup table creating a many-to-many relationship between tblParent and a third table.

The problem I'm encountering is explained in the documentation on Caspio's page about triggered actions "In the case of referential integrity enabled in a relationship, if a trigger executes on an insert event to the parent table, and inserts a record to the child table, the triggered action will fail. This is due to the action of inserting a record to the child table occurring first, thus failing the integrity check as the parent record does not yet exist." I will not disable referential integrity, so what are my options? One way to to create a second submission datapage that comes immediately after the first and is auto-submitted  for the user, but I'd be interested if there are solutions that don't require a second datapage.

[NiceDuck]

Another note here.

If you have a trigger on your parent table that is meant to update or insert record/s to your child table and that child table has a trigger.  avoid having a case where the child table will update/insert its parent table.

If its necessary for your workflow, make sure that the trigger in the parent and the trigger on the child will not be nested to one another. Otherwise, the trigger will not be enabled due to a 'Recursion Error'

The trigger will tell you, however, that it is something about nested level. 

[NiceDuck]

 

Example.

Table A has a trigger that runs on insert, and has an insert block that inserts to table B

Table B also has a trigger that runs on insert, and has an insert block that inserts to table A

Only one of those triggers would allowed to be enabled regardless of how many nested levels you have available on your account (The usual is just one nested level).

This is because the trigger will be looping on one another infinitely. 

Regardless of the condition you have on your trigger, the system will simply not allow it. 

[IamNatoyThatLovesYou]

Hi everyone, just wanted to share this HowTo Article that provides information regarding the nested levels

- https://howto.caspio.com/triggered-actions/triggered-actions-2/#:~:text=Date%2C and Variables. -,Limits,-Depending on your