Creating Triggers: Automating Audit Logs in PostgreSQL

Previously in this course, we explored Introduction to Stored Procedures: Automating Logic in PostgreSQL to encapsulate complex business operations into callable units. While procedures require manual invocation, Triggers act as "silent sentinels"—database objects that execute automatically in response to specific events like INSERT, UPDATE, or DELETE.

In this lesson, we will use Triggers and PL/pgSQL to build an automated audit system for our store project, ensuring that every price change in our products table is recorded in an audit trail without requiring application-level code updates.

Defining a Database Trigger

A trigger is essentially an event-driven function. Unlike a stored procedure, you don't call a trigger directly. Instead, you register it with a table to "fire" whenever a specified data modification occurs.

Triggers are split into two parts:

1. The Trigger Function: A block of code (using PL/pgSQL) that contains the logic you want to run.
2. The Trigger Definition: The configuration that tells PostgreSQL when to run that function (e.g., BEFORE UPDATE or AFTER INSERT).

Creating a Trigger Function for Audit Logs

To automate our audit logs, we first need a table to store the history of changes. We'll create product_price_audit to track when a price changes and what the old value was.

SQL
CREATE TABLE product_price_audit (
    audit_id SERIAL PRIMARY KEY,
    product_id INT,
    old_price NUMERIC,
    new_price NUMERIC,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now, let's create the PL/pgSQL function. This function uses the special OLD and NEW records, which are provided by PostgreSQL inside trigger functions to access the data before and after the modification.

SQL
CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Only log if the price actually changed
    IF (OLD.price <> NEW.price) THEN
        INSERT INTO product_price_audit(product_id, old_price, new_price)
        VALUES (OLD.id, OLD.price, NEW.price);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Attaching the Trigger

With the function ready, we connect it to the products table. We want this to run AFTER every UPDATE on the price column.

SQL
CREATE TRIGGER trg_price_change
AFTER UPDATE OF price ON products
FOR EACH ROW
EXECUTE FUNCTION log_price_change();

By using FOR EACH ROW, the trigger fires once for every single row modified by an UPDATE statement. This is essential for ensuring that bulk updates are tracked accurately.

Hands-on Exercise

To confirm your trigger is working, follow these steps:

1. Update the price of a product in your products table: UPDATE products SET price = 29.99 WHERE id = 1;
2. Query your audit table to see if the record was created: SELECT * FROM product_price_audit;
3. Try updating the product name (but not the price). Does a new row appear in the audit table? (It shouldn't, because we added the IF (OLD.price <> NEW.price) check).

Common Pitfalls

• Recursive Triggers: Be careful not to create a trigger that updates the same table it is listening to, as this can cause an infinite loop. Always verify your conditions.
• Performance Overhead: Every time a trigger fires, it executes code. If you have a table with millions of rows, complex trigger logic can slow down your INSERT or UPDATE operations. Keep trigger functions lean.
• Forgetting RETURN: Every AFTER trigger function must return a value (usually NEW or NULL). If you omit this, your trigger will fail at runtime.

Frequently Asked Questions

Can a trigger modify the data being updated? Yes, if you use a BEFORE trigger, you can modify the NEW record before it is actually saved to the disk. This is useful for data normalization or validation.

What is the difference between BEFORE and AFTER triggers? BEFORE triggers are used to validate or modify data before it hits the table. AFTER triggers are ideal for auditing or side effects (like updating related summary tables) because the data has already been committed successfully.

Can I disable a trigger? Yes, you can use ALTER TABLE products DISABLE TRIGGER trg_price_change; for maintenance windows or bulk imports where you don't want the audit log firing.

Recap

In this lesson, we moved beyond manual data management by implementing Triggers. By creating a PL/pgSQL function and attaching it to our products table, we successfully implemented Automation for our audit logs. This ensures that our store database maintains a reliable history of pricing changes, adding a layer of data integrity that is entirely managed within the database layer.

Up next: Working with Arrays — learning how to store and manipulate list-based data within your columns.