PostgreSQL AFTER INSERT Trigger

Summary: in this tutorial, you’ll learn how to use the PostgreSQL AFTER INSERT trigger to automatically execute a function after an insert event occurs on a table.

An Overview of PostgreSQL AFTER INSERT Trigger #

A trigger is a database object that automatically calls a user-defined function or procedure in response to an event on a particular table or view.

An AFTER INSERT trigger is fired after an INSERT statement is executed against a table. You can access the inserted row using the NEW variable in an AFTER INSERT trigger.

Additionally, you can access the column values of the inserted row via the NEW variable using the dot notation:

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

To create an AFTER INSERT trigger, you follow these steps:

Step 1. Create a trigger function that will be called when the trigger 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)

The RETURN NEW is optional because the PostgreSQL already completes the insert. In this case, PostgreSQL will ignore the return value.

Step 2. Create a new trigger using the CREATE TRIGGER statement:

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

PostgreSQL AFTER INSERT Trigger Example #

We’ll use an AFTER INSERT trigger to update the inventories table automatically:

after inserting a new row into the transactions table:

First, create a new trigger function that update the inventory based on the row inserted into the transactions table:

CREATE OR REPLACE FUNCTION update_inventory()
RETURNS TRIGGER
AS
$$
BEGIN
    -- Attempt to update the inventory
    UPDATE inventories
    SET quantity = quantity + CASE
        WHEN NEW.type = 'receipt' THEN NEW.quantity
        WHEN NEW.type = 'issue' THEN -NEW.quantity
        ELSE 0
    END
    WHERE product_id = NEW.product_id AND warehouse_id = NEW.warehouse_id;

    -- If no rows were updated, handle missing inventory row
    IF NOT FOUND THEN
        IF NEW.type = 'receipt' THEN
            INSERT INTO inventories (product_id, warehouse_id, quantity)
            VALUES (NEW.product_id, NEW.warehouse_id, NEW.quantity);
        ELSE
            RAISE EXCEPTION 
                'Cannot issue stock: Inventory record does not exist 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 an AFTER INSERT trigger for the transactions table:

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

Try it

Third, check the inventory of the product id 1 in the warehouse id 1:

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

Try it

Output:

 product_id | warehouse_id | quantity
------------+--------------+----------
          1 |            1 |      100Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fourth, insert a new row into the transactions table:

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

Try it

The INSERT statement will fire the trigger after_transaction_insert that calls the update_inventory function to update inventory for the inventories table.

Finally, check the inventory of the product 1 in the warehouse 1:

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

Try it

Output:

 product_id | warehouse_id | quantity
------------+--------------+----------
          1 |            1 |      120Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the AFTER INSERT triggers for a table to execute a function or procedure after inserting a new row into a table.

Quiz #