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:
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:
- Create a function
- 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:
1 2 | CREATE FUNCTION name_of_function() RETURN TRIGGER AS |
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:
1 2 3 4 5 | CREATE TRIGGER <name> {BEFORE | AFTER | INSTEAD OF} {event [OR ]} ON <name_of_table> [FOR [EACH] {ROW | STATEMENT}] EXECUTE PROCEDURE name_of_function |
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
orAFTER
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | id | trending_date | title | channel_title | publish_date | views | likes | dislikes ----+---------------+-----------------------------------------------------------------------------------------------+------------------+------------------------+----------+---------+---------- 1 | 2017-11-14 | Eminem - Walk On Water (Audio) ft. Beyoncé | EminemVEVO | 2017-11-10 17:00:03+08 | 17158579 | 787425 | 43420 2 | 2017-11-14 | PLUSH - Bad Unboxing Fan Mail | iDubbbzTV | 2017-11-13 17:00:00+08 | 1014651 | 127794 | 1688 3 | 2017-11-14 | Racist Superman | Rudy Mancuso, King Bach & Lele Pons | Rudy Mancuso | 2017-11-12 19:05:24+08 | 3191434 | 146035 | 5339 4 | 2017-11-14 | I Dare You: GOING BALD!? | nigahiga | 2017-11-12 18:01:41+08 | 2095828 | 132239 | 1989 5 | 2017-11-14 | Ed Sheeran - Perfect (Official Music Video) | Ed Sheeran | 2017-11-09 11:04:14+08 | 33523622 | 1634130 | 21082 6 | 2017-11-14 | Jake Paul Says Alissa Violet CHEATED with LOGAN PAUL! DramaAlert Team 10 vs Martinez Twins! | DramaAlert | 2017-11-13 07:37:51+08 | 1309699 | 103755 | 4613 7 | 2017-11-14 | Vanoss Superhero School - New Students | VanossGaming | 2017-11-12 23:52:13+08 | 2987945 | 187464 | 9850 8 | 2017-11-14 | WE WANT TO TALK ABOUT OUR MARRIAGE | CaseyNeistat | 2017-11-13 17:13:01+08 | 748374 | 57534 | 2967 9 | 2017-11-14 | THE LOGANG MADE HISTORY. LOL. AGAIN. | Logan Paul Vlogs | 2017-11-12 20:19:24+08 | 4477587 | 292837 | 4123 10 | 2017-11-14 | Finally Sheldon is winning an argument about the existence of God | Sheikh Musa | 2017-11-10 14:10:46+08 | 505161 | 4135 | 976 (10 rows) |
We’ll also create another table that will store the changes made against the above dataset:
1 2 3 4 5 6 | CREATE TABLE IF NOT EXISTS youtube_audit_log( id SERIAL PRIMARY KEY, title TEXT, likes INTEGER, date_modified TIMESTAMP(6) NOT NULL ); |
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:
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE OR REPLACE FUNCTION title_update() RETURNS TRIGGER AS $BODY$ BEGIN IF NEW.title <> OLD.title THEN INSERT INTO youtube_audit_log(id, title,likes, date_modified) VALUES (OLD.id, OLD.title, OLD.likes, now()); END IF; RETURN NEW; END; $BODY$ LANGUAGE plpgsql; |
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:
1 2 3 4 5 | CREATE TRIGGER update_likes BEFORE UPDATE ON youtube FOR EACH ROW EXECUTE PROCEDURE title_update(); |
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:
1 2 3 | UPDATE youtube SET title = 'I Dare You: GOING BALD MATE' WHERE id = 4; |
The result of this statement will look something like this:
1 2 3 4 5 | youtubedb=# SELECT * FROM youtube WHERE id=4; id | trending_date | title | channel_title | publish_date | views | likes | dislikes ----+---------------+-----------------------------+---------------+------------------------+---------+--------+---------- 4 | 2017-11-14 | I Dare You: GOING BALD MATE | nigahiga | 2017-11-12 18:01:41+08 | 2095828 | 132239 | 1989 (1 ROW) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | youtubedb=# SELECT * FROM youtube; id | trending_date | title | channel_title | publish_date | views | likes | dislikes ----+---------------+-----------------------------------------------------------------------------------------------+------------------+------------------------+----------+---------+---------- 1 | 2017-11-14 | Eminem - Walk ON Water (Audio) ft. Beyoncé | EminemVEVO | 2017-11-10 17:00:03+08 | 17158579 | 787425 | 43420 2 | 2017-11-14 | PLUSH - Bad Unboxing Fan Mail | iDubbbzTV | 2017-11-13 17:00:00+08 | 1014651 | 127794 | 1688 3 | 2017-11-14 | Racist Superman | Rudy Mancuso, King Bach & Lele Pons | Rudy Mancuso | 2017-11-12 19:05:24+08 | 3191434 | 146035 | 5339 4 | 2017-11-14 | I Dare You: GOING BALD MATE | nigahiga | 2017-11-12 18:01:41+08 | 2095828 | 132239 | 1989 5 | 2017-11-14 | Ed Sheeran - Perfect (Official Music Video) | Ed Sheeran | 2017-11-09 11:04:14+08 | 33523622 | 1634130 | 21082 6 | 2017-11-14 | Jake Paul Says Alissa Violet CHEATED WITH LOGAN PAUL! DramaAlert Team 10 vs Martinez Twins! | DramaAlert | 2017-11-13 07:37:51+08 | 1309699 | 103755 | 4613 7 | 2017-11-14 | Vanoss Superhero School - NEW Students | VanossGaming | 2017-11-12 23:52:13+08 | 2987945 | 187464 | 9850 8 | 2017-11-14 | WE WANT TO TALK ABOUT OUR MARRIAGE | CaseyNeistat | 2017-11-13 17:13:01+08 | 748374 | 57534 | 2967 9 | 2017-11-14 | THE LOGANG MADE HISTORY. LOL. AGAIN. | Logan Paul Vlogs | 2017-11-12 20:19:24+08 | 4477587 | 292837 | 4123 10 | 2017-11-14 | Finally Sheldon IS winning an argument about the existence OF God | Sheikh Musa | 2017-11-10 14:10:46+08 | 505161 | 4135 | 976 (10 ROWS) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started