TimescaleDB CREATE TRIGGER

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

  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:

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 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:

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.