How to Create Postgres Triggers

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

Introduction

In this article we’ll be discussing the syntax and a demo of how to create Postgres triggers. We have some basic prerequisites then quickly we’ll dive into the benefits of using Postgres triggers followed by how to create them and then a demo.

Prerequisite

Ensure to install and configure the following for your OS:

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

Benefits of using Postgres Triggers

Below are the common benefits that an individual or company may get from using triggers.

  • Triggers help to boost performance in a client-server environment by running all rules within the server before returning the results.
  • Triggers promotes ease of maintenance as it can easily be adjusted against a policy requirement via changing the associated trigger program instead of the individual application program.
  • Triggers promotes faster application development via removing the need to code individual trigger for each database application.

How to Create Postgres Trigger

In this section we will show you the simple steps on how to create trigger in Postgres.

  • First, We create a trigger function via the statement CREATE FUNCTION.
  • Then the trigger function will be associated to a table via the statement CRATE TRIGGER.

Creating Trigger Function

As mentioned in the previous section, we will create a trigger function that takes no arguments and returns a value of trigger type.

Below is the basic syntax of defining a trigger function.

1
2
CREATE FUNCTION trigger_function_name()
   RETURNS trigger AS

It is worthy to note that Postgres provides us with other local variables that starts with the characters ‘TG_’.

After defining the trigger function we can not bind it to one ore more events such as UPDATE, INSERT and DELETE operation.

Creating trigger

In this section we will be showing you how to create a Postgres trigger via CREATE TRIGGER statement.

Below is the basic form of the CREATE TRIGGER syntax.

1
2
3
4
5
CREATE [CONSTRAINT] TRIGGER <name>
{BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
   ON <table>
   [FOR [EACH] {ROW | STATEMENT}]
       EXECUTE PROCEDURE <function>
  • The <name> will be the name of the trigger and must be unique to other triggers associated with the same table.
  • ‘AFTER’ is the only clause that can be specified as the constraint trigger.
  • The [BEFORE | AFTER | INSTEAD OF] controls the timing of the execution of the function.
  • event is one of the INSERT, UPDATE and DELETE operation that can invoke the trigger.
  • <table> is where the trigger is associated with.
  • ‘FOR EACH ROW’ defines the manner how does the trigger procedure be invoked, is it every row affected via trigger event or once for every SQL statement. Should either of the condition is not set then the default will be the FOR EACH STATEMENT.
  • <function> is the user-supplied function that is defined as not to require arguments and will return a type of trigger.

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 test below command.

1
create database kidsdb

Create two tables for the database with the following structure

kids table

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

Then we insert the following sample records.

1
2
3
4
5
6
7
INSERT INTO kids (id, name, age)
VALUES
(1, 'abishai', 4),
(2, 'yeshua', 9),
(3, 'ivan', 10),
(4, 'yvonne', 13),
(5, 'solo', 8);

kids_audit table

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS kids_audit (
    id SERIAL PRIMARY KEY,
    kids_id INT,
    modified_on TIMESTAMP(6) NOT NULL,
    kids_name CHARACTER VARYING(100)
    );

The above table will keep track on the changes made against the kids table.

Postgres Trigger Example

First, we create a function called name_changes_log().

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION name_changes_log()
  RETURNS TRIGGER AS
$BODY$
BEGIN
   IF NEW.name <> OLD.name THEN
       INSERT INTO kids_audit(kids_id,kids_name,modified_on)
       VALUES(OLD.id,OLD.name,now());
   END IF;

   RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

Let’s discuss the above function further.

  • The above function will insert the following values in the kids_audit table (OLD.ID, OLD.name and the timestamp).

Second, we are now going to associate the trigger function to the table kids and we will name it ‘name_last_modified’. Prior to the modification of the name field’s value the function trigger will automatically log the changes.

1
2
3
4
5
CREATE TRIGGER name_last_modified
  BEFORE UPDATE
  ON kids
  FOR EACH ROW
  EXECUTE PROCEDURE name_changes_log();

Finally we update one record in the kids table to invoke the trigger. We use the following query:

1
2
3
UPDATE kids
SET name = 'Josh'
WHERE ID = 5;

We can query our kids table to see the changes using this query : `SELECT * FROM kids;’

Let’s take a look at the output:

1
2
3
4
5
6
7
8
 id |  name   | age
----+---------+-----
  1 | abishai |   4
  2 | yeshua  |   9
  3 | ivan    |  10
  4 | yvonne  |  13
  5 | Josh    |   8
(5 rows)

Notice that the record with id equals to 5 was updated from ‘solo’ to ‘Josh’, this means that there must be an entry that log’s this changes in the ‘kids_audit’ table. Lets query that table.

Let’s take a look at the output:

1
2
3
4
5
kidsdb=# select * from kids_audit;
 id | kids_id |        modified_on         | kids_name
----+---------+----------------------------+-----------
  1 |       5 | 2020-03-09 21:20:21.498124 | solo
(1 row)

Indeed, the changes were logged as expected.

Conclusion

We hope you found this tutorial on how to create Postgres triggers helpful in your specific application. We have many more Postgres articles like this one so be sure to check out our knowledge base if you have further 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.