PostgreSQL Triggers: Create, List & Drop with Example

What is Trigger in PostgreSQL?

A PostgreSQL Trigger is a function that is triggered automatically when a database event occurs on a database object, for example, a table. Examples of database events that can activate a trigger include INSERT, UPDATE, DELETE, etc. Moreover, when you create a trigger for a table, the trigger will be dropped automatically when that table is deleted.

How Trigger is used in PostgreSQL?

A trigger can be marked with the FOR EACH ROW operator during its creation. Such a trigger will be called once for each row modified by the operation. A trigger can also be marked with the FOR EACH STATEMENT operator during its creation. This trigger will be executed only once for a specific operation.

PostgreSQL Create Trigger

To create a trigger, we use the CREATE TRIGGER function. Here is the syntax for the function:

CREATE TRIGGER trigger-name [BEFORE|AFTER|INSTEAD OF] event-name
ON table-name
[
 -- Trigger logic
];

The trigger-name is the name of the trigger.

The BEFORE, AFTER and INSTEAD OF are keywords that determine when the trigger will be invoked.

The event-name is the name of the event that will cause the trigger to be invoked. This can be INSERT, UPDATE, DELETE, etc.

The table-name is the name of the table on which the trigger is to be created.

If the trigger is to be created for an INSERT operation, we must add the ON column-name parameter.

The following syntax demonstrates this:

CREATE TRIGGER trigger-name AFTER INSERT ON column-name
ON table-name
[
 -- Trigger logic
];

PostgreSQL Create Trigger Example

We will use the Price table given below:

Price:

Let us create another table, Price_Audits, where we will log the changes made to the Price table:

CREATE TABLE Price_Audits (
   book_id INT NOT NULL,
    entry_date text NOT NULL
);

We can now define a new function named auditfunc:

CREATE OR REPLACE FUNCTION auditfunc() RETURNS TRIGGER AS $my_table$
   BEGIN
      INSERT INTO Price_Audits(book_id, entry_date) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$my_table$ LANGUAGE plpgsql;

The above function will insert a record into the table Price_Audits including the new row id and the time the record is created.

Now that we have the trigger function, we should bind it to our Price table. We will give the trigger the name price_trigger. Before a new record is created, the trigger function will be invoked automatically to log the changes. Here is the trigger:

CREATE TRIGGER price_trigger AFTER INSERT ON Price
FOR EACH ROW EXECUTE PROCEDURE auditfunc();

Let us insert a new record into the Price table:

INSERT INTO Price
VALUES (3, 400);

Now that we have inserted a record into the Price table, a record should also be inserted into the Price_Audits table because of the trigger we created. Let us check this:

SELECT * FROM Price_Audits;

This will return the following:

The trigger worked successfully.

PostgreSQL List Trigger

All triggers that you create in PostgreSQL are stored in the pg_trigger table. To see the list of triggers that you have on the database, query the table by running the SELECT command as shown below:

SELECT tgname FROM pg_trigger;

This returns the following:

The tgname column of the pg_trigger table denotes the name of the trigger.

PostgreSQL Drop Trigger

When a trigger is no longer needed, you can remove it just as easily. To drop a PostgreSQL trigger, we use the DROP TRIGGER statement with the following syntax:

DROP TRIGGER [IF EXISTS] trigger-name
ON table-name [ CASCADE | RESTRICT ];

The trigger-name parameter denotes the name of the trigger that is to be deleted.

The table-name denotes the name of the table from which the trigger is to be deleted.

The IF EXISTS clause attempts to delete a trigger that exists. If you attempt to delete a trigger that does not exist without using the IF EXISTS clause, you will get an error.

The CASCADE option will help you to drop all objects that depend on the trigger automatically.

If you use the RESTRICT option, the trigger will not be deleted if objects are depending on it.

For Example:

To drop the trigger named example_trigger on the table Company, run the following command:

DROP TRIGGER example_trigger IF EXISTS
ON Company;

Using pgAdmin

Now let us see how all three actions are performed using pgAdmin.

How To Create Trigger in PostgreSQL using pgAdmin

Here is how you can create a trigger in PostgreSQL using pgAdmin:

Step 1) Login to your pgAdmin account

Open pgAdmin and Login to your account using your credentials.

Step 2) Create a Demo Database

1. From the navigation bar on the left, click Databases.
2. Click Demo.

Step 3) Type the Query

To create the table Price_Audits, type the query in the editor:

CREATE TABLE Price_Audits (
   book_id INT NOT NULL,
    entry_date text NOT NULL
)

Step 4) Execute the Query

Click the Execute button.

Step 5) Run the Code for auditfunc

Run the following code to define the function auditfunc:

CREATE OR REPLACE FUNCTION auditfunc() RETURNS TRIGGER AS $my_table$
   BEGIN
      INSERT INTO Price_Audits(book_id, entry_date) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$my_table$ LANGUAGE plpgsql

Step 6) Run the Code to create trigger

Run the following code to create the trigger price_trigger:

CREATE TRIGGER price_trigger AFTER INSERT ON Price
FOR EACH ROW EXECUTE PROCEDURE auditfunc()

Step 7) Insert a new record

1. Run the following command to insert a new record into the Price table:

INSERT INTO Price
VALUES (3, 400)

2. Run the following command to check whether a record was inserted into the Price_Audits table:

SELECT * FROM Price_Audits

This should return the following:

Step 8) Check the table content

Let us check the contents of the Price_Audits table.

Listing Triggers using pgAdmin

Step 1) Run the following command to check the triggers in your database:

SELECT tgname FROM pg_trigger

This returns the following:

Dropping Triggers using pgAdmin

To drop the trigger named example_trigger on the table Company, run the following command:

DROP TRIGGER example_trigger IF EXISTS
ON Company

Download the Database used in this Tutorial

FAQs