Trigger Functions and Event Listeners

In this lesson, you will learn how to automate data validation and create audit trails by intercepting database modifications with reactive trigger functions.

The Concept of Reactive Logic

Up until now, we’ve relied on application code or manual queries to enforce business rules and keep logs. However, relying on the application layer can be risky—what if someone runs a direct UPDATE via a terminal?

A Trigger Function is a special type of PL/pgSQL function that acts as a "listener" for specific database events (like INSERT, UPDATE, or DELETE). When the defined event occurs, the database automatically pauses the transaction, executes your function, and then proceeds (or aborts) based on the function's output.

Anatomy of an Audit Trigger

Imagine you need to log every time a user changes their salary. Instead of writing manual logging code in every application function, you create a trigger that captures the "old" state of the row automatically.

CREATE OR REPLACE FUNCTION log_salary_change() 
RETURNS TRIGGER AS $$
BEGIN
    -- 'OLD' is a special record containing the data before the update
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO audit_log (user_id, old_salary, new_salary, changed_at)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
    RETURN NEW; -- Necessary to commit the change
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_salary_change();

Why This Matters

• Encapsulation: The validation logic lives inside the data layer. Whether a web app, a CLI tool, or a report updates the employees table, the audit record is created without fail.
• Validation: You can use a BEFORE trigger to check data integrity. If a value is invalid, you can raise an exception (RAISE EXCEPTION), which effectively blocks the transaction from ever hitting the storage files.
• Performance: Because these functions run within the database engine’s memory context, they are significantly faster than making extra network round-trips from your application server to record logs.

By moving logic into trigger functions, you transform your database from a passive storage container into an active participant that enforces its own rules.

Key Takeaway: Trigger functions allow you to define persistent, event-driven reactive logic that secures and audits data changes regardless of which client initiates the transaction.