Introduction to PostgreSQL BEFORE INSERT trigger

A trigger is a database object that automatically calls a function when an event such asINSERT,UPDATE, andDELETE statement occurs on the associated table.

ABEFORE INSERT trigger is activated before anINSERT event occurs on a table. To create aBEFORE INSERT trigger, you follow these steps:

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

CREATE OR REPLACE FUNCTION trigger_function()   RETURNS TRIGGER   LANGUAGE PLPGSQLAS$$BEGIN   -- trigger logic   -- ...   RETURN NEW;END;$$

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

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

CREATE TRIGGER trigger_nameBEFORE INSERTON table_nameFOR EACH {ROW |STATEMENT}EXECUTE FUNCTION trigger_function();

PostgreSQL BEFORE INSERT trigger example

First, create a table calledinventory to store inventory data:

CREATE TABLE inventory(    product_idINT PRIMARY KEY,    quantityINT NOT NULL DEFAULT 0);

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

CREATE TABLE inventory_stat(    total_qtyINT);

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

CREATE OR REPLACE FUNCTION update_total_qty()RETURNS TRIGGERLANGUAGE PLPGSQLAS$$DECLARE   p_row_countINT;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;$$;

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 aBEFORE INSERT trigger associated with theinventory table:

CREATE TRIGGER inventory_before_insertBEFORE INSERTON inventoryFOR EACH ROWEXECUTE FUNCTION update_total_qty();

Fifth, insert a row into the inventory table:

INSERT INTO inventory(product_id, quantity)VALUES(1,100)RETURNING*;

Output:

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

Sixth, retrieve data from theinventory_stat table:

SELECT * FROM inventory_stat;

Output:

total_qty-----------       100(1 row)

Seventh, insert another row into theinventory table:

INSERT INTO inventory(product_id, quantity)VALUES(2,200)RETURNING*;

Output:

product_id | quantity------------+----------          2 |      200(1 row)

Eighth, retrieve the data from theinventory_stat table:

total_qty-----------       300(1 row)

Summary

• ABEFORE INSERT trigger is activated before anINSERT event occurs on a table.

Last updated onMarch 28, 2024

On this page

• Introduction to PostgreSQL BEFORE INSERT trigger
• PostgreSQL BEFORE INSERT trigger example
• Summary