Overview and Definition
Triggered Actions allow data manipulation and calculations by constructing application logic with a visual interface. These Triggered Actions will execute on specified events to perform actions on data in your tables.
Triggered Actions can be executed through three data change events:
- adding a new record(s)
- deleting existing record(s)
- modifying existing records
A Triggered Actions tab to create trigger events is available above each table. One table can have multiple triggers associated to it, however only one instance of each event can be enabled at any given time per table.
Note: When implementing triggers, we recommend you work on a copy of your table(s) to test the functions before modifying live data.
You can access Triggers from inside Caspio Bridge in two ways:
- Hover over the table you wish to create a trigger on, then select More, then Triggered Actions.
- Or select the table by clicking on the table name. This will open the Datasheet tab for the table. Then select the tab labeled Triggered Actions.
Creating a Trigger
Complete the following steps to create a new Triggered Action:
- From the Triggered Actions tab of the table you wish to base the trigger on, click New Triggered Action to begin.
- On the following screen, you can select events on a table that can fire a trigger. You can choose between Insert, Update, and/or Delete as options for the event for which the Triggered Action will be executed.
- The next step will be to choose an action that should run when the Triggered Action executes. When creating or modifying triggers, the left panel contains visual blocks available to use to build your Triggered Action.
Select the Actions from the left hand menu, and drag-and-drop the Action block that corresponds to the operation you would like to perform. Action blocks are constructed using an SQL-like logic builder, which support INSERT, UPDATE, DELETE constructions, table(s) joining, and data grouping and aggregations. The screenshot below shows available Actions:
- Add new record(s) – INSERT INTO
- Remove existing record(s) – DELETE FROM
- Change existing record(s) – UPDATE
By clicking on the gear icon in the Action block, you can access options to limit number of records affected with Top, combine fields from one or more tables with Join, and limit records with criteria with Where.
- Other data selection, data manipulation and logic functions are available via the left menu bar. These additional blocks can be dragged-and-dropped to complete building the Triggered Action.
|Data||Selecting and defining of constants to be used in the Triggered Action including SELECT statements.|
|Logic||Conditional and comparison type blocks to modify trigger behavior including IF/THEN/ELSE and AND/OR functions.|
|Text||String manipulation and static constants including substring, trim and concatenate operations.|
|Number||Mathematical operators and static constants including basic arithmetic and random numbers.|
|Date||Date modification operators and static dates including timestamp and date difference.|
- To complete your trigger, click on Validate in the menu at the top of the screen.
- If there are any errors on validation, you will need to fix these before being able to save your Triggered Action.
- Once the validation completes without error, click on Save from the top menu.
- Name your trigger and then select either Save or Save & Enable.
Additional Notes about Triggered Actions
- Triggered Actions currently are limited to a maximum of one nested level.
- The #inserted table represents data that is being inserted or updated in the table.
- The #deleted table represents data that is being deleted from the table.
- 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.
- If cascade update and/or delete are enabled with referential integrity options, these events will not execute Triggered Actions via update or delete events.
- Triggered Actions currently will not run from import of data through either manual import or DataHub tasks.
- Up to 10,000 records can be updated from a Triggered Action event.
- Only one Triggered Action can be Active per event type in a table. If you attempt to enable a Triggered Action with the same event, the other Triggered Action will be disabled.
Testing Triggered Action on a Copy of a Table
It is recommended when initially creating a Triggered Action to create a duplicate of the table(s) that will be modified by the trigger. This is to prevent accidental modification or deletion of live data. Once the Triggered Action has been tested, you can then recreate the trigger using the live table . It is also recommended to export the live data so that in case of loss of data, you can import the data to recover information that may be lost. The overall best practice in creating Triggered Actions is to avoid enabling them on live data until you have tested the behavior and have verified it is functioning as you desired. The alternative option would be to export your entire application prior to creating the Triggered Action so that in the event of any loss, the application can be fully re-imported and place back into the state it was prior to the trigger.
A single Triggered Action may contain multiple Actions. These multiple actions will execute in a top down hierarchy. In the example below, the Action of Update Items will be the first to run, and the Insert Into Change Log, will be the final Action to be run. By combining multiple Actions, you can manipulate data in multiple tables on a single executed Triggered Action.
Comments can be added for each action block in a Trigger. Adding a comment to summarize the purpose of each Action makes it easier to determine the use of each when multiple Actions exist in a single Triggered Action.
Watch the video below to see a demonstration of creation of a Triggered Action: