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:

• To check if the service is running, you can use this command:

• The output of this command should look like the following:

• 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

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:

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:

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

The result of this should look something like this:

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:

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:

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:

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.

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:

The result should look something like this:

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

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.