How to Create Postgres Triggers
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.
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
WHENclause 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
WHENclause was provided.
- Only use the
tablenameand not the
database.tablenameto 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.
CREATE TRIGGER <your_trigger_name> [BEFORE|AFTER|INSTEAD OF] <your_event_name>
-- Your trigger logic goes here....
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.
create database animaldb
- Create two tables for the database with the following structure:
CREATE TABLE IF NOT EXISTS animals (
id SERIAL PRIMARY KEY,
name CHARACTER VARYING(100),
color CHARACTER VARYING(100)
CREATE TABLE IF NOT EXISTS audit (
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.
CREATE OR REPLACE FUNCTION auditFunction() RETURNS TRIGGER AS $sample_table$
INSERT INTO AUDIT(animal_id, create_date) VALUES (NEW.ID, CURRENT_TIMESTAMP);
$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.
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.
INSERT INTO animals (id, name, color)
We get the following output:
id | name | color
1 | cat | black
We can verify if a new entry was created in the ‘audit’.
The output looks like the following:
animal_id | create_date
1 | 2020-03-08 13:47:40.911677+08
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