PostgreSQL BEFORE INSERT Trigger

Summary: in this tutorial, you’ll learn how to use the PostgreSQL BEFORE INSERT trigger to automatically execute a function before inserting data into a table.

An Overview of PostgreSQL BEFORE INSERT Trigger #

A trigger is a database object associated with a table and is fired automatically when an event occurs on the table.

A BEFORE INSERT trigger is fired before an INSERT statement is executed. When the trigger fires, it automatically calls a user-defined function to perform a specific task.

For example, the trigger function can validate data across tables to ensure data integrity before inserting data into a table.

In the trigger function, you can access the row that will be inserted via the NEW record variable. To retrieve a specific column value from this row, you use dot notation like this:

NEW.column_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To create a BEFORE INSERT trigger, you use the following steps:

Step 1. Create a trigger function that will be invoked before the insert event occurs:

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

Step 2. Create a new trigger using the CREATE TRIGGER statement and associate the trigger function with the trigger:

CREATE TRIGGER trigger_name
BEFORE INSERT
ON table_name
FOR EACH { ROW | STATEMENT }
EXECUTE FUNCTION trigger_function();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the trigger name after the CREATE TRIGGER keyword.
• Next, use the BEFORE INSERT trigger timing to indicate that the trigger should fire before an insert event.
• Then, specify the table name to which the trigger belongs in the ON clause.
• After that, define the row-level or statement-level trigger using the FOR EACH ROW or STATEMENT.
• Finally, provide the name of the function you want to execute when the trigger fires in the EXECUTE FUNCTION clause.

PostgreSQL BEFORE INSERT Trigger Example #

We’ll create a trigger that validates an inventory transaction in the transactions table:

that does not result in a negative quantity in the inventories table:

First, create a trigger function that validates the inventory based on the transaction:

CREATE OR REPLACE FUNCTION validate_inventory()
RETURNS TRIGGER 
AS $$
DECLARE
    v_quantity INT;
BEGIN
    -- Get the current inventory level
    SELECT quantity INTO v_quantity
    FROM inventories
    WHERE product_id = NEW.product_id 
      AND warehouse_id = NEW.warehouse_id;

    -- If the transaction is an issue, ensure it does not lead 
    -- to negative inventory
    IF NEW.type = 'issue' THEN
        IF v_quantity IS NULL OR v_quantity < NEW.quantity THEN
            RAISE EXCEPTION 'Insufficient inventory for product_id %, warehouse_id %', 
                            NEW.product_id, NEW.warehouse_id;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create a trigger on the transactions table, which associates with the validate_inventory function:

CREATE TRIGGER before_insert_transaction
BEFORE INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION validate_inventory();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, get the inventory quantity of the product id 2 at the warehouse id 2:

SELECT * 
FROM inventories
WHERE product_id = 2 AND 
      warehouse_id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 inventory_id | product_id | warehouse_id | quantity
--------------+------------+--------------+----------
            2 |          2 |            2 |      150Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fourth, attempt to create a transaction that issues 200 pieces of product id 2 at warehouse id 2:

INSERT INTO transactions (
    product_id,
    warehouse_id,
    user_id,
    type,
    quantity,
    transaction_date
  )
VALUES
  (2, 2, 1, 'issue', 200, '2025-01-15');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

PostgreSQL issues the following error:

ERROR: Insufficient inventory for product_id 2, warehouse_id 2
CONTEXT: PL/pgSQL function validate_inventory() line 13 at RAISECode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, create a valid transaction that issues a valid quantity:

INSERT INTO transactions (product_id, warehouse_id, user_id, type, quantity, transaction_date)
VALUES (2, 2, 1, 'issue', 20, '2025-01-15');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Note that you can create a AFTER INSERT trigger to update the inventory quantity in the inventories table.

Summary #

• Use BEFORE INSERT triggers to execute a function automatically before inserting a new row into a table.

Quiz #