PostgreSQL BEFORE INSERT Trigger

Summary: in this tutorial, you will learn how to create a PostgreSQL BEFORE INSERT trigger associated with a table.

Introduction to PostgreSQL BEFORE INSERT trigger

A trigger is a database object that automatically calls a function when an event such as INSERT, UPDATE, and DELETE statement occurs on the associated table.

A BEFORE INSERT trigger is activated before an INSERT event occurs on a table. To create a BEFORE INSERT trigger, you follow these steps:

First, define a trigger function that will execute before the INSERT event occurs:

CREATE OR REPLACE FUNCTION trigger_function()
   RETURNS TRIGGER
   LANGUAGE PLPGSQL
AS
$$
BEGIN
   -- trigger logic
   -- ...
   RETURN NEW;
END;
$$Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

At the end of the function, you need to place the RETURN NEW statement

Second, create a BEFORE INSERT trigger and associate a trigger function with it:

CREATE TRIGGER trigger_name
BEFORE INSERT
ON table_name
FOR EACH {ROW | STATEMENT}
EXECUTE FUNCTION trigger_function();Code language: SQL (Structured Query Language) (sql)

PostgreSQL BEFORE INSERT trigger example

First, create a table called inventory to store inventory data:

CREATE TABLE inventory(
    product_id INT PRIMARY KEY,
    quantity INT NOT NULL DEFAULT 0
);Code language: SQL (Structured Query Language) (sql)

Second, create a table called inventory_stat that stores the total quantity of all products:

CREATE TABLE inventory_stat(
    total_qty INT
);Code language: SQL (Structured Query Language) (sql)

Third, define a function that increases the total quantity in the inventory_stat before a row is inserted into the inventory table:

CREATE OR REPLACE FUNCTION update_total_qty()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
DECLARE
   p_row_count INT;
BEGIN
   SELECT COUNT(*) FROM inventory_stat
   INTO p_row_count;
   
   IF p_row_count > 0 THEN
      UPDATE inventory_stat 
      SET total_qty = total_qty + NEW.quantity;
   ELSE
      INSERT INTO inventory_stat(total_qty)
      VALUES(new.quantity);
   END IF;
   RETURN NEW;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If the inventory_stat table has no rows, the function inserts a new row with the quantity being inserted into the inventory table. Otherwise, it updates the existing quantity.

Fourth, define a BEFORE INSERT trigger associated with the inventory table:

CREATE TRIGGER inventory_before_insert
BEFORE INSERT
ON inventory
FOR EACH ROW
EXECUTE FUNCTION update_total_qty();Code language: SQL (Structured Query Language) (sql)

Fifth, insert a row into the inventory table:

INSERT INTO inventory(product_id, quantity)
VALUES(1, 100)
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 product_id | quantity
------------+----------
          1 |      100
(1 row)

Sixth, retrieve data from the inventory_stat table:

SELECT * FROM inventory_stat;Code language: SQL (Structured Query Language) (sql)

Output:

 total_qty
-----------
       100
(1 row)Code language: SQL (Structured Query Language) (sql)

Seventh, insert another row into the inventory table:

INSERT INTO inventory(product_id, quantity)
VALUES(2, 200)
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 product_id | quantity
------------+----------
          2 |      200
(1 row)Code language: SQL (Structured Query Language) (sql)

Eighth, retrieve the data from the inventory_stat table:

 total_qty
-----------
       300
(1 row)Code language: SQL (Structured Query Language) (sql)

Summary

• A BEFORE INSERT trigger is activated before an INSERT event occurs on a table.