What are database drivers? A practical introduction to PostgreSQL with examples

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

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

In this tutorial, you’ll learn what triggers are, how they work, when to use them, and when to avoid them. You’ll work through practical examples using PostgreSQL, but the basic concepts apply to most relational databases.

Table of Contents

Conditions

To follow along with the examples, you will need:

• Basic knowledge of SQL (SELECT, INSERT, UPDATE, DELETE)

• A running PostgreSQL instance (version 12 or later)

• Like a SQL client psqlpgAdmin, or DBeaver

If you don’t have PostgreSQL installed, you can use a free cloud-hosted instance from Services. Neon or Sopa base to follow along.

How do triggers work?

At a high level, a trigger has three parts:

1. The incident: Which action activates the trigger (INSERT, UPDATE, DELETE, or TRUNCATE)

2. Timing: when the trigger fires relative to the event (before or after)

3. Function: What logic executes when the trigger fires.

The typical flow is this: a user or application performs an operation on a table, the database checks to see if there are any triggers associated with the operation, and if a match is found, the database automatically executes the trigger function.

You can think of triggers as event listeners for your database. Just like JavaScript addEventListener A database trigger watches for a click or keypress, watching for row-level changes on the table.

How to create your first stimulus

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

Let’s make a concrete example. Say you have one. products Table and you want to set automatically. updated_at Timestamp each 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 TRIGGER Within the type function body, you have access to two main variables: NEW (post-operation row) and OLD (row before operation).

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

This function sets updated_at Runs whenever the column at the current timestamp. It returns again. NEWwhich tells PostgreSQL to proceed with the modified row.

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 – Stimulus is associated with it. products The table

• FOR EACH ROW – The function runs once for each row affected by the update.

• EXECUTE FUNCTION set_updated_at() – Call function

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 will see it. updated_at is automatically updated at the time of the update operation, even though you did not explicitly set it in your query. This is the stimulus doing its job.

BEFORE vs. AFTER triggers

The trigger time determines when the function executes relative to the actual data change.

Before the stimuli Run before inserting, updating, or deleting a row. They are useful when you want to modify or verify incoming data. Since the change hasn’t taken effect yet, you can change it. NEW Queue or even return and cancel the operation entirely. NULL.

After the triggers Run after row replacement on table. These are useful for side effects such as logging, sending notifications, or updating relational tables. At that point, the change has already taken place, so you can’t edit the row – but you can read both. OLD And NEW To see what has changed.

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

How to create audit log with AFTER trigger

One of the most common uses of triggers is audit logging – keeping a record of every change made to an important table. Let’s make 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 some important things going on here. gave TG_OP A variable is a special string that PostgreSQL provides inside a trigger function. It tells you which operation activated the trigger: 'INSERT', 'UPDATE'or 'DELETE'. It lets you handle different operations with a single function.

gave RETURN COALESCE(NEW, OLD) Finally ensures that the function returns the correct row. For INSERT and UPDATE operations, NEW exists and returns. For delete operations, NEW It’s empty, then 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();

Notes AFTER INSERT OR UPDATE OR DELETE Syntax You can attach the same trigger to multiple events, which keeps your setup clean.

Step 4 – Test it.

-- Insert a new product
INSERT INTO products (name, price) VALUES ('USB-C Hub', 29.99);

-- Update the price
UPDATE products SET price = 24.99 WHERE name="USB-C Hub";

-- Delete the product
DELETE FROM products WHERE name="USB-C Hub";

-- Check the audit log
SELECT * FROM product_audit ORDER BY changed_at;

You will see three rows. product_audit (One for each operation) Old and new values ​​are automatically recorded. No application code is required.

How to use BEFORE trigger for validation

Triggers can also enforce business rules at the database level. Suppose you want to prevent any product from having a negative value.

CREATE OR REPLACE FUNCTION prevent_negative_price()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Product price cannot be negative. Got: %', NEW.price;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_price
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION prevent_negative_price();

Try it now:

INSERT INTO products (name, price) VALUES ('Faulty Item', -10.00);
-- ERROR: Product price cannot be negative. Got: -10.00

The entry is rejected in its entirety. The row never makes it into the table. This is powerful because the rule applies at the database level regardless of which application or script sends the query.

Row-level vs. statement-level triggers

Use all the triggers you’ve seen so far. FOR EACH ROWwhich means the function runs once for each affected row. If you update 100 rows in a query, the trigger function runs 100 times.

PostgreSQL is also supported. FOR EACH STATEMENT Triggers, which run once per SQL statement regardless of how many rows are affected.

CREATE OR REPLACE FUNCTION log_bulk_update()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'A bulk operation was performed on the products table';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_bulk_update_notice
AFTER UPDATE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_update();

Statement-level triggers are less common, but they are useful for tasks like refreshing a material view or sending a single notification after a batch update instead of one notification per row.

important: In statement-level motivations, NEW And OLD The variables are not available because the trigger is not associated with a specific row.

References to new and old variables

Here’s a quick reference for when. NEW And OLD Row level triggers are available in:

Understanding when each variable is available will save you from runtime errors in your trigger functions.

How to manage motivation.

As you add more triggers to your database, you’ll need to know how to inspect, disable, and remove them.

How to list all triggers on a table

SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers
WHERE event_object_table="products";

How to Temporarily Disable a Trigger

-- Disable a specific trigger
ALTER TABLE products DISABLE TRIGGER trigger_product_audit;

-- Disable all triggers on a table
ALTER TABLE products DISABLE TRIGGER ALL;

This is useful during large data transfers where you want to skip trigger execution for performance reasons.

How to re-enable the trigger

ALTER TABLE products ENABLE TRIGGER trigger_product_audit;

How to drop a trigger

DROP TRIGGER IF EXISTS trigger_product_audit ON products;

Note that releasing the trigger does not drop the corresponding function. If you don’t need it anymore you’ll need to drop the function separately:

DROP FUNCTION IF EXISTS log_product_changes();

When to use triggers

Triggers work well for specific use cases. Here are the scenarios where they are a strong choice:

• Audit logging: Automatically recording who changed what and when, as you saw earlier in this tutorial.

• Recovery of derived data: Keeping calculated columns, counters, or summary tables consistent with source data.

• Data validation: Enforcing business rules that are outside of check constraints, such as cross-table validation.

• Automatic time stamping: Setting up created_at And updated_at fields without depending on the application layer.

When to Avoid Stimulants

Motivations are powerful, but they come with trade-offs. Here are cases where you should think twice before using them:

• Complex business logic: If the logic involves calling external APIs, sending emails, or setting up multistep workflows, it belongs to your application layer. Stimuli should remain light.

• Performance-sensitive bulk operations: Row-level triggers on tables that receive frequent bulk inserts or updates can create significant overhead. If you’re inserting millions of rows, those triggers fire millions of times.

• Cascading stimuli: When one trigger action fires another trigger, which fires another, debugging becomes extremely difficult. If you find yourself creating a chain of triggers, rethink the design.

• Logic that developers need to discover easily.: Triggers are sometimes called “hidden logic” because they execute automatically without being expressed in the application code. If your team often asks “Why did this column change?” And the answer is always “there’s a trigger”, a sign that the logic can be more discoverable if stored procedures are explicitly called in your application layer or.

A good rule of thumb: if the logic is tightly coupled to the data and must always be executed regardless of which client or service touches the table, a trigger is appropriate. If the logic depends on the context of the application (such as the current user session, attribute flags, or external state), it belongs in the application.

The result

In this tutorial, you learned what database triggers are and how they work in PostgreSQL. You have created three functional triggers: an automatic timestamp updater, a complete audit logging system, and a data validation guard. You have BEFORE and AFTER triggers, row-level and statement-level triggers, and when NEW And OLD Variables are available.

Triggers are a powerful tool for keeping your data consistent and enforcing your business rules at the database level. Use them for focused, data-centric operations, and keep the logic simple.

If you found this tutorial useful, you can contact me. LinkedIn And x.