Creating a PostgreSQL Trigger with a When Condition

Summary: in this tutorial, you will learn how to create a conditional trigger that fires only when a condition is true.

In PostgreSQL, a trigger is a database object that automatically executes a function when INSERT, UPDATE, DELETE, or TRUNCATE event occurs on a table.

Sometimes, you want the trigger to be activated only when a specific condition is met. To do that, you specify a boolean condition in the WHEN clause of the CREATE TRIGGER statement, like so:

CREATE TRIGGER trigger_name
ON table_name
WHEN condition
EXECUTE FUNCTION function_name(arguments);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the condition is a boolean expression. If the condition is true, the trigger is fired; otherwise, the trigger will not be activated.

In row-level triggers, you can access the old/new values of columns of the row within the condition. However, in statement-level triggers, you do not have access to column values.

PostgreSQL Trigger When Condition example

First, create a table called orders to store order data:

CREATE TABLE orders (
    order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    total_amount NUMERIC NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create another table called customer_stats to store the total spent amount by customers:

CREATE TABLE customer_stats (
    customer_id INT PRIMARY KEY,
    total_spent NUMERIC NOT NULL DEFAULT 0
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, create an AFTER INSERT trigger that inserts a row into the customer_stats table when a new row is inserted into the orders table:

CREATE OR REPLACE FUNCTION insert_customer_stats()
RETURNS TRIGGER 
AS $$
BEGIN
   INSERT INTO customer_stats (customer_id)
   VALUES (NEW.customer_id);
   RETURN NULL;
END;
$$ LANGUAGE plpgsql;

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

Fourth, define an AFTER UPDATE trigger on the orders table with a condition:

CREATE OR REPLACE FUNCTION update_customer_stats()
RETURNS TRIGGER 
AS 
$$
BEGIN
    IF NEW.status = 'completed' THEN
        -- Update the total_spent for the customer
        UPDATE customer_stats
        SET total_spent = total_spent + NEW.total_amount
        WHERE customer_id = NEW.customer_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_customer_stats_trigger
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status <> 'completed' AND NEW.status = 'completed')
EXECUTE FUNCTION update_customer_stats();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The AFTER UPDATE trigger fires only when the status of the row changes from non-completed state to completed.

Fifth, insert some rows into the orders table:

INSERT INTO orders (customer_id, total_amount, status)
VALUES
    (1, 100, 'pending'),
    (2, 200, 'pending');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The AFTER INSERT trigger fires and insert rows into the customer_stats table.

Sixth, change the order statuses of customer id 1 and 2 to completed:

UPDATE order
SET status = 'completed'
WHERE customer_id IN (1,2);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The AFTER UPDATE trigger fires and updates the total_spent column in the customer_stats table.

Finally, retrieve the data from the customer_stats table:

SELECT * FROM customer_stats;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 customer_id | total_spent
-------------+-------------
           1 |         100
           2 |         200
(2 rows)Code language: plaintext (plaintext)

Summary

• Specify a condition in the WHEN clause of the CREATE TRIGGER statement to instruct PostgreSQL to fire the trigger when the condition is true.