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
orSTATEMENT
. - 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)
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)
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)
Output:
inventory_id | product_id | warehouse_id | quantity
--------------+------------+--------------+----------
2 | 2 | 2 | 150
Code 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)
PostgreSQL issues the following error:
ERROR: Insufficient inventory for product_id 2, warehouse_id 2
CONTEXT: PL/pgSQL function validate_inventory() line 13 at RAISE
Code 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)
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.