Trigger to update a record AND insert new record when the ID match in both tables

[roattw]

The post belwo discusses a task to update a record from one table to another.  Is there a way to combne both process: 1) if new record identified in Table 1 copy/nsert it to Table 2, AND/OR 2) if a field in Table 1 is changed (ie an address or notes)  update the same field in Table 2?

https://forums.caspio.com/topic/16393-task-to-update-a-record-when-the-id-match-in-both-tables/

 

 

[CoopperBackpack]

Hello @roattw,

The main idea I tried to cover is the #inserted table on Update.

I know that it is confusing, so I wanted to highlight this topic. 

The trick is that when the record is updated, the #inserted table includes all the fields of the updated record and not only those that are actually updated. 

In my example, I have 4 fields: ID, Notes, Address, and Phone:



Even when I update just one field (let`s say, the 'Notes'  field is updated), the  #inserted table still has 4 fields. 

As a result, the #inserted table looks like this:

 

So, as I mentioned, the WHERE clause is skipped. 

However, this Trigger is fired when the Update action occurs in Table_1.

The Join helps us to identify the record that was updated. For that, it is really crucial to join tables on the unique field. 
Since in the SET block, all the fields are mapped with the fields of the #inserted table, if the value was not updated, it will be "reassigned".

In this example, Address and Phone will be set in Table_2 by the Trigger, but the values are the same, so the result of this reassigning is invisible because we still can see the old values.

To summarize, by this Trigger we replace these values

with these values from the #inserted table:


Hope this makes sense. 

[CoopperBackpack]

Hello @roattw,

It is important to understand the structure of the Tables to create a Trigger/Task with the described logic.

Are you interested in a Trigger or a Task? 

I assume that Table1 and Table2 store records with unique IDs.

For example:

 

The expected result is: 

1) to add a record with ID = 3 to the Table_2

2) to update the Notes for ID = 1 in the Table_2

 

Task example:

This Task inserts records with IDs that are absent in Table_2 and updates the Notes field if the value in Table_1 is different for the same ID.

Please provide more details if you need to create a Trigger. Can the user insert new records into Table_1 and update existing records in Table_1?

If so, this Trigger should work:

[Flowers4Algernon]

Hello! This how-to link should also be helpful as you go along: https://howto.caspio.com/tables-and-views/triggered-actions/

[roattw]

CooperBlack, thanks for the info!

I am interested in a Trigger, and Table1 and Table2 do store records with unique IDs.  In this case the ENTRY_ID from Table 1 gets copied to Table 2 as well.

Scenerio: Im copying fields from a Directory table where people are marked as Alumni over to an Alumni tabkle.  First time record created and gets indicated as an Alumni that data copied over (Insert).  But if we change a field in Directory (like thier address) we would like that field updated in Alumni Table as well (Update)

DIRECTORY TABLE:
Entry_ID (when first entered in Directory)

ALUMNI TABLE:
Alum_ID (created when first instance copied over from Directory)
Entry_ID (passed over from Directory Table in trigger)

In your proposed example (thank you!) covering UPDATING Alumni Table, if something changes in Directorfy table the SET command matches up the fields for updates between the two tables.  However, in the WHERE portion you show Table2.Notes not equal Table1.Notes.  Just the one field.  In mine Im making that comparison on many fields that may change.  But in the WHERE action, there isnt an ADD option.  Dont I also need to Add all the fields in to be compared SET (#1 below) to the WHERE portion (#2 below)?

[CoopperBackpack]

Hello @roattw,

Thank you for your clarification. Since the list of fields is rather long, I think you may skip using the WHERE clause.

For example, there is a record in the table:

And the user updates the Notes field. The Address and Phone are left without changes.

 

If the Trigger works on Update, the virtual #inserted table stores:

* new values if the values in some fields were changed
and

* old values if the values in some fields were not changed. 

In this example, the  #inserted table stores:

Notes: 'NEW_comment'

Address: 3011 Byrd Lane

Phone: 505-220-6225

With this Trigger design, you will receive relevant data:

 

! Important note: this solution (skipping WHERE clause) works if you have separate Triggers (one Trigger on Insert and 1 Trigger on Update).

 

If you want to combine Insert and Update in one Trigger, then the WHERE clause is needed. You may combine all the fields that can be changed with the logical 'OR' operator.

As for me, I don`t like this idea since the Trigger performance can be degraded by using too many comparisons in the WHERE clause. 

[roattw]

Thanks, a ton!  That explanation helps a lot.  In your example, the Update stores address and Phone even though there was no change?  Or does it just update the fields with changes?  And regardless of approach, you have to specify all target fields individually that you wnat changed (either in Set or Where).  Adding all the possible fields that might change is honerous sometimes on larger datasets ;^)  There isnt a way to set up a trigger to just compare all fields and update any with changes IF some primary ID field doesnt match?

Again, thanks for the explanation.  It helps for peopel that arent quite grapsing the logic in triggered actions.

[roattw]

This is helping a lot.  Its going to help a lot of souls.  I am so close.   At the risk of making you do this:

So if you didnt put any fields in Set, but used the Inner Join, would it just go thru and reassign all the fields,  catching any updates of course along the way?  Like would this work?  

[PotatoMato]

Hi, @roattw. I think it would do nothing as you don't have any fields on the "set". The Join block is like a condition where in ID upon insert and the existing ID on the table2 should be the same to update. You still need to insert the fields on the "set" so it will know which field/s to update.

 

Hope this helps  

 

-Potato

[roattw]

Im so close on this.  All triggers fire.  

Changes in Directory table update to Alumni Table
Person marked in Directory as Inactive moved to Inactive table

Im left with one "data restriction error" (item B below) on update when person marked as Inactive.  All the triggers still fire and the records end up where they need to be.  Im pretty sure its the Delete From trigger because If I remove that trigger (item D below)  the record update works without the data restriction message.

Basic Update record page with a Details option (A).  Termination date unchecks Active (B).  But when Update Record made results in data restrition error C.  Update set to Go Back to Results page.  I think the data restriction error AND the No Records Found may be a clue.  Change any other field and update it goes back to data results summary page (A).  Mark Inactive then it cant go back to that initial data summary page (A).  Its like it tries to go back to a summary results view but because the record you just edited is no longer there (removed by trigger) it cant go back to that state.  But the triggers all work.  Dont think ts a failure to pass data, think its a failure to get back to get resulkts set after an update because the record is removed on update.

Dont think its calculated values or formulas because removing Delete From trigger fixes the data restriction error - passing all calculated values and formulas to thier respective tables.