How to Create a Trigger in PostgreSQL by using the CREATE TRIGGER statement

Introduction

If you’re working with PostgreSQL, you’ll probably find it helpful to create certain triggers. A trigger is a stored procedure that executes whenever a particular database event occurs. For example, you can set up a trigger that will execute when a row is inserted in a specified table or when a certain column is updated in a table. Fortunately, it’s not difficult at all to create a trigger in PostgreSQL. In this article, we’ll show you how to create a PostgreSQL trigger using the CREATE TRIGGER statement.

Prerequisites

Before we go any further with our tutorial, let’s take a moment to discuss a few prerequisites that are necessary for this task:

  • You’ll need to have a basic understanding of PostgreSQL triggers and how they work.

  • You need to make sure that PostgreSQL server is properly installed, configured and running in the background. To start PostgreSQL server on a LINUX machine, use the following command:

sudo service postgresql start
  • To check if the service is running, you can use this command:
service postgresql status
  • The output of this command should look like the following:
â— postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1230 (code=exited, status=0/SUCCESS)

Aug 01 14:51:20 user-UX330UAK systemd[1]: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd[1]: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
  • To start, stop and restart PostgreSQL server on a Windows machine, you can use the following instructions:
  1. Open Control Panel
  2. Open Administrative Tools
  3. Open Services
  4. Locate the PostgreSQL Server service
  5. Start, Stop or Restart the service

Restarting PostgreSQL server in a Windows Machine

Creating a Sample Table

Now that we’ve reviewed the essential prerequisites, let’s create a sample table that we’ll be using in the later part of our tutorial.

We will be creating a new table named clients using the following statement:

CREATE TABLE clients(
id SERIAL PRIMARY KEY,
first_name VARCHAR(40) NOT NULL,
last_name VARCHAR(40) NOT NULL
);

We’ll also be creating a table named client_tracker which will be used to log every change made to the clients table. This table is created using the following statement:

CREATE TABLE client_tracker (
id SERIAL PRIMARY KEY,
client_id INT NOT NULL,
last_name VARCHAR(40) NOT NULL,
changed_on TIMESTAMP(6) NOT NULL
);

Let’s go ahead and add some records to table clients, using the following commands in sequence:

INSERT INTO clients (first_name, last_name)
VALUES ('yeshua', 'galisanao');

INSERT INTO clients (first_name, last_name)
VALUES ('abi', 'reaves');

The result of this should look something like this:

table clients with two records

Create a TRIGGER

A trigger function is similar to a typical function, though some key differences exist; for example, a trigger function doesn’t take any parameter or argument and will return a type of trigger as value.

Let’s look an an example that illustrates how to create a trigger function:

CREATE FUNCTION testtrigger_function()
RETURN TRIGGER AS

A trigger function can be created in any language, as long as the language being used is supported by PostgreSQL. In this tutorial, we’ll be using PL/pgSQL to create a trigger in PostgreSQL.

Trigger functions receive important information about the environment in which they’re called through a structure called TriggerData. For instance, the variables OLD and NEW contain information about the condition of a row in a table before and after some specified trigger event. TriggerData also contains other local variables that have TG_ as their prefix, such as TG_TABLE_NAME and TG_WHEN.

Certain trigger events, such as INSERT, UPDATE and DELETE, can be bound to a trigger function once the function is constructed.

PostgreSQL CREATE TRIGGER Statement

Now that we understand a bit more about trigger functions and how they work, let’s take a closer look at how to create a trigger statement that will bind our trigger function to an event. This is one of the key steps needed to create a PostgreSQL trigger using the CREATE TRIGGER statement.

The CREATE TRIGGER statement generates another trigger. The complete syntax of the CREATE TRIGGER can be complex with quite a few alternatives; for the purposes of our tutorial, we’ll use a simple form of the CREATE TRIGGER statement as shown below:

CREATE TRIGGER your_trigger_name
{BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
ON your_table_name
[FOR [EACH] {ROW | STATEMENT}]
EXECUTE PROCEDURE your_trigger_function

In the above statement, the triggering event could be UPDATE, INSERT, DELETE or TRUNCATE. We can define a trigger that fires prior to (BEFORE) or following (AFTER) an event. The INSTEAD OF clause is only used for the INSERT, UPDATE, or DELETE events in a view.

NOTE: PostgreSQL offers two types of triggers: statement level trigger and row level trigger. You can specify which type of trigger you’re using by adding the FOR EACH ROW clause or FOR EACH STATEMENT clause to your statement.

PostgreSQL Trigger Example

In this section, we’ll look at an example of how to create a trigger in PostgreSQL.

Remember that earlier in this tutorial, we created tables named clients and client_tracker. We’ll be using those tables now in our example.

We’ll begin by creating our first function called last_name_change_log:

CREATE OR REPLACE FUNCTION last_name_change_log()
RETURNS TRIGGER AS
$BODY$
BEGIN
IF NEW.last_name < OLD.last_name THEN
INSERT INTO client_tracker(client_id,last_name,changed_on)
VALUES(OLD.id,OLD.last_name,now());
END IF;

RETURN NEW;
END;
$BODY$

The function shown above will check if the last_name of a client changes; if so, it will insert the old last_name into the client_tracker table including the client_id, last_name and the time of change changed_on.

Next, we’ll bind our trigger function to the clients table. We’ll name the trigger change_last_name. This trigger function will be automatically invoked to log the change before the last_name column’s value is updated.

CREATE TRIGGER change_last_name
BEFORE UPDATE
ON clients
FOR EACH ROW
EXECUTE PROCEDURE last_name_change_log();

Now that we’ve set up our trigger and bound it to a table, let’s try updating the abi galisanao record. We’ll pretend that she gets married to someone with the last name of mendez. We’ll use the following query:

UPDATE clients SET last_name = "mendez" WHERE ID = 2;

The result should look something like this:

Results from updating PosgtreSQL table

Let’s verify if our trigger was invoked properly by checking the table client_tracker. We can do this using the following command:

SELECT * FROM client_tracker;

Results from PostgreSQL Trigger

Conclusion

A trigger is considered a special kind of stored procedure because it’s not invoked directly– it’s invoked automatically when a specific event occurs. It’s important to know how to create a trigger in PostgreSQL, since triggers can allow you to enforce business rules and derive certain column values. In this article, we learned how to create a PostgreSQL trigger using the CREATE TRIGGER statement; with these step-by-step instructions, you’ll be ready to create triggers in your own database setup.

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.