Triggers

Database triggers are designed to execute a function stack any time a specific operation occurs inside of a database table. Triggers are available on any paid plan at no additional cost.

Learn what a database trigger is, and some best practices for when to employ them

Learn how to use triggers in your Xano back-end

Review some real-world examples on using database triggers

What is a database trigger?

A database trigger is a function stack, just like the function stack available in the No-Code API Builder, that allows execution any time a specific operation happens in the database. You can choose to enable a database trigger at one or more of the following events.

  • A record is added

  • A record is deleted

  • A record is edited

  • The table is truncated (all records are cleared)

All of the functions available in the No-Code API Builder are available to use in database triggers. The biggest difference, beyond when they are executed, is that triggers do not have a configurable response like your standard APIs do. They are intended to process data behind the scenes after a database event.

When to use Triggers

Triggers can be used in a few different ways, and can be thought of as something similar to the Post Process function, or could potentially be a replacement for some of your background tasks. The difference between using Post Process and a trigger is that the function stack is not called from an API, but instead triggered by the corresponding event in the database. For a background task, this means that you could leverage a trigger instead of querying your database for updated items at regular intervals, which can in some cases greatly increase the efficiency and speed of your application as a whole.

Another major benefit to utilizing Triggers is that they will execute based on actions taken via the database view as well as those coming from an API.

This makes triggers a crucial tool in implementing functionality such as:

  • Triggering front-end updates via an API when something changes in your database

  • Sending proactive communication to your users

  • Sending proactive communication to internal stakeholders

  • Automatically deleting referenced data when a record is deleted

How to use Triggers

Triggers can be accessed from the database table view of the table you want to apply a trigger to by clicking on Triggers in the three-dot menu at the top-right.

From the panel that opens, you can quickly review and access any triggers you've created for that table, or create a new one.

Click + Add database trigger to start building a new trigger. Review the below table for a description of what each option means.

PanelDescription

  • Name

    • Give the trigger a unique name

  • Description

    • Provide a description for what the trigger will do

  • Active

    • Enable or disable the trigger

  • Data Sources

    • Choose the data source(s) the trigger should run on, or leave blank to execute on all data sources

    • Triggers will auto-set the data source you are currently active in, but you can also use a Set Data Source function in the trigger function stack if needed.

  • Actions

    • Inserts - Run when adding records

    • Updates - Run when editing records

    • Deletes - Run when deleting records

    • Truncates - Run when clearing the table

  • Custom Filter

    • Use the same expression builder you find in the Query All Records function to filter the records that are actioned on by the database trigger, such as "only run this trigger when the user's name is Chris"

Please note that triggers will only be executed on your live branch, but do have the ability to work against multiple data sources.

Database triggers cannot trigger other triggers to prevent infinite loops. Therefore, if a trigger modifies a record in a table with another trigger set to run on record updates, the second trigger will not be activated by the modification initiated by the first trigger.

Once you've configured your trigger the way that you want (don't worry, you can change these later), click "Save". You'll see your new trigger added to the list. Click on the newly created trigger to edit the function stack.

The trigger will contain four inputs by default.

  • new is the new version of the record if an edit is performed, or simply just the new record added on an add record

  • old is the previous version of the record before the edit is performed, the contents of the deleted record, or blank on adds

  • action is the action that triggered execution (insert, update, delete, truncate)

  • datasource is the datasource the trigger has executed on

You can add functions to your database trigger by clicking the blue + sign when hovering over the function stack.

Once you've populated your function stack, and you're ready to test the trigger, you can use the debugger by clicking Run & Debug at the top and provide some sample inputs, or you can publish your changes to immediately allow the trigger to begin executing based on the events you specify.

Examples

For the following examples, we'll be working with a simple set of tables to track orders of certain products by our users. Our order table keeps track of all open orders. Product contains all product information for items we can offer to our users. User contains basic user data, such as their name and email.

Key Concepts

For these examples, you'll want to ensure you have an understanding of the following:

  • Database Operations (Query All Records, Get Record, Edit Record)

  • Working with Variables (Edit Variable)

  • Using Filters (we'll be using mathematical filters such as sum or add, and array modification filters such as push)

Example #1 - Calculating Order Totals

Calculating order totals is a very simple example of when you might consider using a trigger instead of adding additional steps to an API function stack or utilizing a background task. The advantage to using a trigger for this is mainly the potential for decreased database compute load when computing an order total.

Without Triggers

Without database triggers, performing this type of functionality would likely consist of something like the following flow:

  • When a user selects the first product they would like to order, an order record is created in our order table

  • Every time the user selects a new product afterwards, we have to:

    • Retrieve the existing record from the database

    • Use an array modification step to add the new product ID to the list of products in the order

    • Compute the total cost of the order up to that point by first querying the products table to get the prices for each product, and then summing the total of the price values in the next step

    • Edit the record in the products table to update the new order total

With Triggers

With database triggers, we can add a new trigger on our order table to take action any time a record is updated.

  • Our API is responsible for either creating a new order record or updating the record with the newly selected product

  • In the function stack of our database trigger, we first need to query the matching records from the products table. We can get the IDs from new.product_id, which is the array of product IDs added to the order. We'll also make sure to disable distinct record detection in the Output tab of this query, just in case the users add multiple of the same product.

  • The next and final step is to edit the record on the order table with the sum of all of the product prices.

  • We can now publish our changes to this trigger, and it will immediately begin keeping our order totals updated.

Example #2 - Performing a Cascading Delete

In this example, we'll be taking a look at what is known as a cascading delete, which means that when a record is deleted, we also want to make sure that all related data is removed at the same time. Performing this action via a trigger can help us ensure that our API function stacks remain as lean as possible.

In our application, when a user deletes their account, we also want to remove any orders that are associated with that user. So, we'll not only need to delete records from the user table, but also remove them from the order table. We want to use a trigger for this instead of our API because there may be times where we manually remove users from our database via the table view, and don't want to have to go through the order table manually to delete those records as well.

Without Triggers

  • First, we need to look up all of the orders from the user who wants to delete their account.

  • We'll then set up a For Each loop to loop through each of the order records found, and delete each of those records from the order table.

  • After the loop completes, we can remove the user record.

There is not a true disadvantage to using this approach -- however, it is important to note that performing this functionality without triggers ends up being more about good application hygiene. Triggers allow for a more traditional approach to solving this problem and ensure a more realtime action taken on the related data.

With Triggers

  • In the function stack for the trigger, the first thing we'll do is query the order table for records that are linked to the ID of the user that was just deleted. For a trigger with a delete action, this record will be contained in the old input, and we need to look up by the record ID, so we'll use old.id to find the records we need.

  • The next step will be to set up a For Each loop; the same one demonstrated in the Without Triggers example. This will loop through our order records that were found in step 1 and delete each one.

Example #3 - Surfacing High-Value Customers

In our application, we want to make sure that we have a real-time notification system in place so that we can surface internally any potentially high-value customers to our sales team. For this example, we'll be watching our order table for any orders that exceed a certain dollar amount, and sending ourselves an email when that happens.

Without Triggers

  • In the function stack for our task, we'll add a Query All Records step to query our user table for users who have a total_spend of over 50000.

  • Once we have that list of users, we'll use an External API Request function to make a call to a third-party mail service to notify internal stakeholders of these users.

It's a very simple function stack, and may not seem imperative to use triggers for this. However, let's think about actually running this task on a schedule. We want to make sure that we are able to action on these users as soon as possible, which means that we need this task to run as often as possible.

  • Hover over the Schedule section of your task, and click the blue + icon to add a schedule to this task. We're going to set this task to run every 5 seconds.

  • We can now enable and publish our task, and it will begin its scheduled runs.

In this scenario, while the function stack itself is simple, there are a couple of things we need to consider: the volume of users that this query has to search through and return, and the frequency at which we need it to do so. A query every 5 seconds on a table that is likely also seeing consistent writes is clearly not a very efficient process, and should be considered a poor implementation of this functionality. We can instead use triggers and almost fully eliminate any performance or hygeine concerns.

With Triggers

  • Because we're using triggers and not a background task, we don't need to worry about querying the database, and that means this function stack can be handled in a single step -- an external API request to our mail service to inform us of the high-value user.

Using a trigger for this function allows us to keep our database load low, and not have to concern ourselves with what normally would come along with querying a large table on such a frequent basis.

Last updated