Tracking Data Changes in Tables Using Triggered Actions

4 minutes to read

In Caspio, you can create a basic history of data changes using Triggered actions to record insert, update, and delete events in a table. This technique supports internal data reviews, improves visibility into recent edits, and helps with simple tracking needs such as monitoring project changes or inventory adjustments.

While not a substitute for formal audit trail functionality, this method may be helpful in scenarios where light monitoring is sufficient. If you require regulatory compliance or robust data integrity controls, consider Caspio’s Compliance Plan, which includes compliant audit trails and advanced logging features specifically designed for audit and regulatory requirements.

In the steps below, we use a table that stores project data to demonstrate how change tracking works. You can apply this method to any table in Caspio. Since this setup requires a second table to store the changes, we will refer to your original data table as the primary table.

Steps:

Create a new table that will store a log of the changes in your primary table.
This table will hold the data about the inserted, updated, or deleted records. For example:
- Project ID: The ID of the record being modified
- Project_Name: The name of the project being modified
- Project_Description: The description of the project being modified
- Date_of_Modification: The date and time the action occurred (timestamp)
- Action: Insert, update, or delete action types.
Open the new table and go to the Triggered actions tab.
Create individual triggered actions, mapping the relevant primary table fields to the target fields. For each of: insert, update, and delete actions in your table, perform the following steps:
1. In the trigger editor, in the left panel, click Actions and drag the INSERT INTO block to the trigger editor area.
2. From the Run actions on dropdown list, select the event type: Insert, Update, or Delete.
3. In the INSERT INTO block, click the settings gear icon and check the SELECT option. In the SELECT block settings, remove the WHERE condition by clearing its checkbox.
  For Delete action only: Change the SELECT FROM setting to #deleted.
4. In the INSERT INTO block, add the fields that you want to include in your change tracking by clicking Add.
5. Map the fields in the change tracking table to the fields in your primary table. In the trigger editor left panel, click Data and drag the Field blocks to the trigger editor area inside the INSERT INTO block.
6. To include the modification date, in the trigger editor left panel click Date and drag the Timestamp block into the editor area within the INSERT INTO section, mapping it to the selected field.
7. To include the action type, in the trigger editor left panel click Text and drag a text block to the editor area within the INSERT INTO section, mapping it to the selected field. Enter the text to reflect the action type in the target table: “Insert”, “Update”, or “Delete”, depending on the selection in substep b.
8. Perform these substeps again for the remaining action types, so that you have three Run action on blocks: one each for insert, update, and delete.
Test the change tracking setup by performing insert, update, and delete actions on your primary table. In the change tracking table, verify that the correct actions are being logged, and that all relevant fields are populated with accurate data.

Result:

Tips for using your change log effectively:

Optimize the change tracking table: The log may grow quickly, especially if a large number of changes occur. Implement periodic clean-up strategies or data archiving procedures if necessary to avoid performance issues.

Regularly review the tracked changes: Regularly monitor your history records to ensure that your app’s data is updated appropriately, especially for apps that handle sensitive data.