How to Create Postgres Triggers

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

Introduction

In this article we will explain what Postgres Triggers are and how to create them. We’ll quickly go over some basic prerequisites then explain what they are followed by how to create them.

Prerequisite

Ensure to install and configure the following for your OS:

Basic PostgreSQL is required, to be able to follow along with the tutorial.

What is a Postgres Trigger

A Postgres trigger is a function being performed automatically whenever a process is taking place within the table. This process can be any of the following operation: DELETE, UPDATE, INSERT or TRUNCATE operation.

A Postgres trigger is also known as a user-defined function that is associated with a table. A trigger function must be created prior to creating a trigger and right after that we bind it to a target table. It is worthy to note that a trigger will be automatically invoked whenever an event occurs within a table wherein a user-defined function needs to be invoked explicitly.

Below are some key points to know about Postgres Trigger.

  • The trigger will be dropped together with the table that they are associated with.
  • Triggers for the same event will be invoked against their name alphabetically.
  • Whenever the WHEN clause equates to true then those Postgres statements for that particular rows will be executed. On the other hand the Postgres statements were executed against all rows if no WHEN clause was provided.
  • Only use the tablename and not the database.tablename to modify a table that exist on the same database or view to which we bind the trigger.

Below is the basic syntax of a Postgres trigger.

1
2
3
4
5
CREATE  TRIGGER <your_trigger_name> [BEFORE|AFTER|INSTEAD OF] <your_event_name>
ON <the_table_name>
[
 -- Your trigger logic goes here....
];

The ‘‘ in the above syntax can be on of the following: TRUNCATE, INSERT, UPDATE and delete operation on the defined ‘‘.

Creating Sample Dataset

In this section, we create a sample dataset that we will be using for our demo.

First log in to the Postgres shell.

  • We create a database using the below command.
1
create database animaldb
  • Create two tables for the database with the following structure:

animals table

1
2
3
4
5
CREATE TABLE IF NOT EXISTS animals (
    id SERIAL PRIMARY KEY,
    name CHARACTER VARYING(100),
    color CHARACTER VARYING(100)
    );

audit table

1
2
3
4
CREATE TABLE IF NOT EXISTS audit (
    animal_id INT,
    create_date TEXT
    );

Postgres Trigger Example

In this section, we will show how to create a basic trigger and how to invoke them in Postgres.

First, we define a simple function by using the following syntax.

1
2
3
4
5
6
CREATE OR REPLACE FUNCTION auditFunction() RETURNS TRIGGER AS $sample_table$
   BEGIN
      INSERT INTO AUDIT(animal_id, create_date) VALUES (NEW.ID, CURRENT_TIMESTAMP);
      RETURN NEW;
   END;
$sample_table$ LANGUAGE plpgsql;

The above syntax is a function that will create an entry to the audit table.

then we now create a basic trigger and we will name it sample_trigger, we use the following syntax.

1
2
CREATE TRIGGER sample_trigger AFTER INSERT ON animals
FOR EACH ROW EXECUTE PROCEDURE auditFunction();

The above syntax creates a trigger named ‘sample_trigger’ that will invoke a the ‘auditFunction()’ function that we created earlier whenever we successfully insert a new record in the animal table.

  • We then insert a sample record in the animals table, using the following query.
1
2
INSERT INTO animals (id, name, color)
VALUES (1,'cat','black');

We get the following output:

1
2
3
4
 id | name | color
----+------+-------
  1 | cat  | black
(1 row)

We can verify if a new entry was created in the ‘audit’.

The output looks like the following:

1
2
3
4
 animal_id |          create_date
-----------+-------------------------------
         1 | 2020-03-08 13:47:40.911677+08
(1 row)

Conclusion

We hoped you found this tutorial on Postgres triggers helpful and you can apply what you’ve learned here to your specific situation. This is just one of many PostgreSQL tutorials we have in our knowledge-base so if you found this helpful please come back if you have other PostgreSQL questions.

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.