TimescaleDB CREATE TRIGGER

Introduction

If you’re using TimescaleDB to store and manage data that changes with time, you may wonder if you can implement triggers in this time-series database. The answer is “yes”: TimescaleDB supports the table objects that are supported by PostgreSQL, which includes indexes, data types and triggers. You can use the PostgreSQL CREATE TRIGGER statement in TimescaleDB to set up a new trigger in your database. In this article, we’ll discuss the TimescaleDB CREATE TRIGGER statement and look at some simple examples of its use.

Prerequisite

Before you continue with this tutorial, be sure to install and configure the following for your operating system:

• PostgreSQL 11

• TimescaleDB

Some basic PostgreSQL knowledge is required in order to follow along with the tutorial.

TimescaleDB Trigger

As we mentioned earlier, TimescaleDB fully supports PostgreSQL triggers and their related operations, such as dropping, altering and creating triggers. In a TimescaleDB environment, these operations are performed against a hypertable— an abstracted structure that looks like a single table to users but is actually a “virtual view” of multiple tables, or chunks, that house the data. A hypertable will automatically propagate any changes made by the operations to all associated chunks.

We can create a trigger using a two-step approach:

1. Create a function
2. Create a trigger

Let’s take a closer look at each of these steps:

Creating a Function

Creating a trigger function in TimescaleDB is identical to the process of creating a trigger function in PostgreSQL. A trigger function takes no argument and returns a type of trigger.

Shown below is the basic syntax for creating a function:

In this syntax, we call the command CREATE FUNCTION and define a name for the function. We then return a value of a trigger type.

After we define the trigger function, we can bind the trigger to the following events:

• UPDATE
• INSERT
• DELETE

Creating a Trigger

Now that we know how to create a function, we’ll show you how to create a trigger that will call the function.

Here’s the basic syntax for creating a trigger:

There’s a lot going on in this statement, so let’s look at it more closely:

• First, we call the CREATE TRIGGER statement.
• The represents the given name for the trigger. This name should be unique from any other existing triggers associated with the hypertable.
• We specify the name of the table name_of_table, with which the trigger will be associated.
• We use the BEFORE or AFTER clause to define when the trigger will be executed against the event.
• Finally, the name_of_function represents the name of the user-defined function, which we created in the previous section. This function should not require arguments and should return a trigger.

Sample Dataset

We’ll be using the following sample dataset in our examples:

We’ll also create another table that will store the changes made against the above dataset:

Creating Trigger Example

We’ll follow the two-step approach we described earlier to create a trigger.

Creating a Function Example

Our first step will be to create a function:

This function will insert the following values into the ‘youtube_audit’ table’s columns: id, title, likes and data_modified.

Creating a Trigger Example

Now that we have defined our function, we’ll create the trigger that will invoke the function:

Executing the Function and Trigger Example

In order to fire our trigger and execute the function, we need to update one of our rows. We’ll use the following statement:

The result of this statement will look something like this:

Now, let’s see if the trigger was able to execute the function. If it was successful, it should result in a new record stored in the table ‘youtube_audit_log’. We should see something like this:

We can see that a log entry was generated showing the old title along with the timestamp indicating when the record was modified.

Conclusion

When you use TimescaleDB to store and manage time-series data, you have the ability to use many features and functionalities supported by PostgreSQL, including triggers. In this article, we learned how to use the TimescaleDB CREATE TRIGGER statement. With our step-by-step instructions to guide you, you’ll be able to utilize triggers in your own TimescaleDB database.