PostgreSQL Trigger: Create, Drop Example

Details

What are Postgresql Triggers?

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.

In this PostgreSQL Tutorial, you will learn the following:

How Trigger used in POSRGREQL?

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.

Creating a 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
];

For 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 defined 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_Audit table. This will be as a result of the trigger that we have created on the Price table. Let us check this: 

SELECT * FROM Price_Audits;

This will return the following:

The trigger worked successfully.

Listing triggers

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.

Dropping triggers

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 delete the trigger named example_trigger on table Price, we run the following command:

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's see how all three actions performed using pgAdmin.

Creating Triggers

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

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

Step 3) To create the table Price_Audits, type the query editor:

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

Step 4) Click the Execute button.

Step 5) 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 following code to create the trigger price_trigger: 

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

Step 7)

  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) Let us check the contents of the Price_Audits table:

Listing triggers

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

SELECT tgname FROM pg_trigger

This returns the following:

Dropping Triggers

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

DROP TRIGGER example_trigger IF EXISTS
ON Company

Summary:

Download the Database used in this Tutorial

 

YOU MIGHT LIKE: