- Data modification actions:
- INSERT INTO – adding new record(s) to existing or new table
- DELETE FROM – removing existing record(s) from table
- UPDATE – changing existing record(s) in table
- Notification actions:
- SEND EMAIL
- SEND SMS
- on Insert – adding a new record(s)
- on Delete – deleting existing record(s)
- on Update – modifying existing records
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 Triggered Actions in two ways:
- Hover over the table you wish to create a trigger on, then click More > Triggered Actions.
- Select the table by clicking the table name. This will open the Datasheet tab for the table. Then select the Triggered Actions tab.
Creating a TriggerComplete the following steps to create a new Triggered Action:
- From the Triggered Actions tab of the table you want 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.
- 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 click or drag-and-drop the Action block that corresponds to the operation you would like to perform. Action blocks are constructed using a SQL-like logic builder, which support INSERT, UPDATE, DELETE constructions, table(s) joining, data grouping and aggregations, EMAIL and SMS notifications.
- For data modification in tables, select Action below:
- INSERT INTO
- DELETE FROM
- For notifications, select Action below:
- SEND EMAIL
- SEND SMS
By clicking the picker icon you can insert a field value in the message body or subject line. This is available only when SELECT statement is used to retrieve data from tables.
You can send email as Plain Text or HTML. In the HTML mode, you can launch WYSIWYG editor to create or edit your email content by clicking icon in the message block.
- Other calculation elements for data selection, data manipulation and defining application logic are available in the left panel. These additional blocks can be dragged-and-dropped to complete building the Triggered Action.
Element Type Description 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. Loops WHILE block to create sequences of instructions that are repeated as long as a given condition is true. FOR EACH block to create custom conditions that execute repeated actions for each record of a data source. 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. Variables User-defined variables for holding static values or results of calculations and table variables for temporarily storing a set of records, which can be used as the data source for other Action blocks.
- 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
- Depending on your Caspio plan, the maximum number of affected records and the nested level, which is the maximum number of Triggered Actions executed by the event that fired the Triggered Action, can vary. For more details, see Caspio plans and pricing.
- Maximum number of affected records – the sum of records which can be selected, updated, inserted, or deleted per event that fires the Triggered Actions or per task run.
- Maximum number of nested levels – the number of Triggered Actions can be initiated a Triggered Action executed by an event or a task. For examples, for a plan configured with 2 nested levels for Triggered Actions, when a record is inserted to a table A, both Triggered Actions in table A and B will be executed in the example below. For a plan configured with 1 nested level, only Triggered Action in table A will be executed.
Table Triggered Actions A Run on Insert, has Triggered Action to update Table B B Run on Update, has Triggered action to update Table C C No Triggered Actions
- 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.
- 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.
- Currently only US and Canada phone numbers are supported for sending SMS Notification. Other countries will be supported in the future releases.
- 100 SMS messages sent to US and Canada phone numbers each month are free. You can add more based on your expected usage. See our pricing page for details.