Postgres Triggers: Write PL/pgSQL Trigger Functions (2026)

A Postgres trigger function is a special kind of PL/pgSQL function that automatically executes in response to certain events on a table or view.

Let’s see one in action. Imagine we have an orders table and we want to ensure that the order_total is always calculated correctly when an order_items record is inserted or updated.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    order_total DECIMAL(10, 2) DEFAULT 0.00
);

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT,
    item_price DECIMAL(10, 2) -- Price at the time of order
);

-- Sample data
INSERT INTO products (product_name, price) VALUES
('Laptop', 1200.00),
('Keyboard', 75.00),
('Mouse', 25.00);

Now, let’s create a trigger function to update the orders.order_total.

CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
DECLARE
    item_total DECIMAL(10, 2);
BEGIN
    -- Calculate the total for the current item row
    item_total := NEW.quantity * NEW.item_price;

    -- Update the order_total in the parent orders table
    UPDATE orders
    SET order_total = order_total + item_total
    WHERE order_id = NEW.order_id;

    RETURN NEW; -- For INSERT or UPDATE triggers, return the new row
END;
$$ LANGUAGE plpgsql;

This function is designed to be called after an order_items row is inserted or updated. We’ll attach it to the order_items table:

CREATE TRIGGER order_items_after_insert_update
AFTER INSERT OR UPDATE ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_order_total();

Now, let’s test it. First, create an order:

INSERT INTO orders (customer_name) VALUES ('Alice');
-- Let's assume the new order_id is 1

Then add items to that order:

INSERT INTO order_items (order_id, product_id, quantity, item_price)
VALUES (1, 1, 2, (SELECT price FROM products WHERE product_id = 1)); -- Laptop at 1200.00

-- The trigger fires here. It calculates 2 * 1200.00 = 2400.00
-- and updates orders.order_total for order_id 1 to 2400.00.

INSERT INTO order_items (order_id, product_id, quantity, item_price)
VALUES (1, 2, 1, (SELECT price FROM products WHERE product_id = 2)); -- Keyboard at 75.00

-- The trigger fires again. It calculates 1 * 75.00 = 75.00
-- and adds it to the existing order_total. So, 2400.00 + 75.00 = 2475.00.

After these inserts, if you SELECT * FROM orders WHERE order_id = 1;, you’ll see order_total is 2475.00.

The problem this solves is maintaining data consistency across related tables without requiring manual application logic for every database operation. It centralizes this logic within the database itself.

Internally, when an INSERT or UPDATE occurs on order_items, Postgres checks if a trigger is defined for that event. If so, it executes the associated trigger function. The FOR EACH ROW clause means the function runs once for every row affected by the statement.

Inside the trigger function, NEW is a special record variable that holds the new version of the row being inserted or updated. OLD is another special variable holding the old version (available for UPDATE and DELETE triggers). The RETURNS TRIGGER declaration signifies that the function is intended to be used as a trigger. The LANGUAGE plpgsql specifies the procedural language used.

The exact levers you control are:

Trigger Event: INSERT, UPDATE, DELETE (or combinations).
Trigger Timing: BEFORE or AFTER the event. BEFORE triggers can modify NEW or OLD values, or even cancel the operation by returning NULL. AFTER triggers are for actions that depend on the operation having successfully completed.
Trigger Granularity: FOR EACH ROW (as shown) or FOR EACH STATEMENT (runs once per SQL statement, not per row).
Trigger Condition: A WHEN clause can be added to the CREATE TRIGGER statement to make the trigger fire only if a specific condition is met.

What most people don’t realize is that BEFORE triggers can actually prevent an INSERT, UPDATE, or DELETE from happening. If a BEFORE trigger function returns NULL, the operation on that specific row is skipped entirely, and the trigger function is not executed again for that row. This is super useful for validation or complex conditional logic before data is committed.

The next concept you’ll likely encounter is handling DELETE operations and ensuring the order_total is decremented correctly, or perhaps implementing BEFORE triggers for data validation.