Unlocking Database Automation: A Practical Guide to PostgreSQL Triggers

If you’ve ever needed your database to automatically respond to changes – like logging every update to a sensitive table, enforcing a business rule before an insert, or syncing derived data after a delete – then triggers are the tool you’re looking for.

A database trigger is a function that the database executes automatically when a specific event occurs on a table. You don’t call it manually. Instead, you define the conditions, and the database handles the rest.

How Triggers Work

At a high level, a trigger has three parts:

• The event: what action activates the trigger (INSERT, UPDATE, DELETE, or TRUNCATE)
• The timing: when the trigger fires relative to the event (BEFORE or AFTER)
• The function: what logic runs when the trigger fires

Here’s the general flow: a user or application performs an operation on a table, the database checks if any triggers are associated with that operation, and if a match is found, the database executes the trigger function automatically.

How to Create Your First Trigger

In PostgreSQL, creating a trigger is a two-step process. You first create a trigger function, then you attach that function to a table with a CREATE TRIGGER statement.

Let’s build a concrete example. Say you have a products table and you want to automatically set the updated_at timestamp every time a row is modified.

Step 1 – Create the Table

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10, 2) NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);

Step 2 – Create the Trigger Function

A trigger function in PostgreSQL is a special function that returns the TRIGGER type. Inside the function body, you have access to two important variables: NEW (the row after the operation) and OLD (the row before the operation).

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 3 – Attach the Trigger to the Table

CREATE TRIGGER trigger_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Let’s break down each part of this statement:

• BEFORE UPDATE – the trigger fires before the update is applied to the table
• ON products – the trigger is associated with the products table
• FOR EACH ROW – the function runs once for every row affected by the update
• EXECUTE FUNCTION set_updated_at() – the function to call

BEFORE vs AFTER Triggers

The timing of a trigger determines when the function executes relative to the actual data change.

BEFORE triggers run before the row is inserted, updated, or deleted. They are useful when you want to modify or validate the incoming data. Since the change hasn’t been applied yet, you can alter the NEW row or even cancel the operation entirely by returning NULL.

AFTER triggers run after the row change has been committed to the table. They are useful for side effects like logging, sending notifications, or updating related tables. At this point, the change is already done, so you can’t modify the row – but you can read both OLD and NEW to see what changed.

Here’s a rule of thumb: use BEFORE triggers when you need to change or reject data, and use AFTER triggers when you need to react to a completed change.

How to Build an Audit Log with an AFTER Trigger

One of the most common uses for triggers is audit logging – keeping a record of every change made to an important table. Let’s build one.

Step 1 – Create an Audit Table

CREATE TABLE product_audit (
audit_id SERIAL PRIMARY KEY,
product_id INT NOT NULL,
action VARCHAR(10) NOT NULL,
old_price NUMERIC(10, 2),
new_price NUMERIC(10, 2),
changed_by TEXT DEFAULT current_user,
changed_at TIMESTAMP DEFAULT NOW()
);

Step 2 – Create the Audit Trigger Function

CREATE OR REPLACE FUNCTION log_product_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = ‘UPDATE’ THEN
INSERT INTO product_audit (product_id, action, old_price, new_price)
VALUES (OLD.id, ‘UPDATE’, OLD.price, NEW.price);
ELSIF TG_OP = ‘DELETE’ THEN
INSERT INTO product_audit (product_id, action, old_price)
VALUES (OLD.id, ‘DELETE’, OLD.price);
ELSIF TG_OP = ‘INSERT’ THEN
INSERT INTO product_audit (product_id, action, new_price)
VALUES (NEW.id, ‘INSERT’, NEW.price);
END IF;

RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

There are a few important things happening here. The TG_OP variable is a special string that PostgreSQL provides inside trigger functions. It tells you which operation activated the trigger: ‘INSERT’, ‘UPDATE’, or ‘DELETE’. This lets you handle different operations with a single function.

The RETURN COALESCE(NEW, OLD) at the end ensures the function returns the correct row. For INSERT and UPDATE operations, NEW exists and is returned. For DELETE operations, NEW is null, so OLD is returned instead.

Step 3 – Attach the Trigger

CREATE TRIGGER trigger_product_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_changes();

Notice the AFTER INSERT OR UPDATE OR DELETE syntax. You can bind a single trigger to multiple events, which keeps your setup clean.

Step 4 – Test It

INSERT INTO products (name, price) VALUES (‘Wireless Keyboard’, 49.99);

— Wait a moment, then update the row

UPDATE products SET price = 44.99 WHERE name = ‘Wireless Keyboard’;

SELECT name, price, created_at, updated_at FROM products;

You’ll see that updated_at has been automatically updated to the time of the UPDATE operation, even though you didn’t explicitly set it in your query. That’s the trigger doing its job.