Triggers and Event-Driven Programming

60 min

Triggers are database objects that automatically execute functions when specified events occur on tables. Triggers enable event-driven programming within the database, allowing you to respond to data changes automatically. This capability is essential for implementing audit trails, data validation, maintaining derived data, and enforcing business rules at the database level. Understanding triggers enables you to build robust, self-maintaining database systems.

PostgreSQL supports triggers for INSERT, UPDATE, DELETE, and TRUNCATE operations. Triggers can fire BEFORE or AFTER the triggering event, and can be defined to fire FOR EACH ROW or FOR EACH STATEMENT. BEFORE triggers can modify the row being inserted/updated or prevent the operation entirely. AFTER triggers are useful for logging, notifications, or maintaining related data. The timing and granularity you choose depends on your use case.

Trigger functions receive special variables (NEW, OLD, TG_OP, TG_TABLE_NAME) that provide context about the triggering event. NEW contains the new row for INSERT/UPDATE operations, OLD contains the old row for UPDATE/DELETE operations, TG_OP indicates the operation type, and TG_TABLE_NAME provides the table name. These variables enable trigger functions to make decisions based on the data being changed and the type of operation.

Audit triggers are common use cases for triggers, automatically logging all changes to critical tables. Audit logs typically record who made the change, when it occurred, what changed (old vs new values), and the operation type. Audit triggers enable compliance, debugging, and security analysis. JSONB is often used in audit logs to store flexible change data without requiring schema changes for new columns.

Data validation triggers can enforce business rules that are too complex for CHECK constraints. For example, triggers can validate that related records exist, ensure data consistency across tables, or implement custom validation logic. Validation triggers can raise exceptions to prevent invalid data, or they can automatically correct data. Understanding when to use triggers vs constraints helps you implement appropriate data integrity mechanisms.

Best practices for triggers include keeping trigger functions simple and fast, avoiding triggers that call external services (which can slow down transactions), and being careful about trigger chains (triggers that cause other triggers to fire). Triggers should be well-documented and tested thoroughly, as they execute automatically and can be difficult to debug. Understanding trigger execution order and potential side effects is essential for reliable trigger implementations.

Key Concepts

Triggers automatically execute functions on table events.
Triggers can fire BEFORE or AFTER INSERT/UPDATE/DELETE operations.
Trigger functions receive NEW, OLD, and context variables.
Triggers are useful for audit trails, validation, and data maintenance.
Triggers execute within transactions and can roll back operations.

Learning Objectives

Master

Creating triggers for INSERT, UPDATE, and DELETE operations
Writing trigger functions that handle different operation types
Implementing audit logging with triggers
Using triggers for data validation and business rules

Develop

Understanding event-driven database programming
Designing maintainable trigger-based systems
Implementing database-level business logic

Tips

Use BEFORE triggers to modify data before it's saved.
Use AFTER triggers for logging and notifications.
Return NEW for INSERT/UPDATE, OLD for DELETE in trigger functions.
Use TG_OP to determine operation type: IF TG_OP = 'INSERT' THEN ...

Common Pitfalls

Creating trigger chains that cause infinite loops or performance issues.
Not handling all operation types, missing some audit events.
Making triggers too complex, causing performance problems.
Not testing triggers thoroughly, causing unexpected behavior in production.

Summary

Triggers enable event-driven programming in the database.
Triggers can fire BEFORE or AFTER data changes.
Trigger functions receive context about the triggering event.
Triggers are essential for audit trails and data validation.

Exercise

Create triggers for audit logging and data validation.

-- Create an audit log table
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    record_id INTEGER,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(50)
);

-- Create a function for audit logging
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, record_id, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, record_id, old_data, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, record_id, old_data)
        VALUES (TG_TABLE_NAME, TG_OP, OLD.id, to_jsonb(OLD));
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create trigger on orders table
CREATE TRIGGER orders_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- Test the trigger
INSERT INTO orders (customer_id, order_date, total_amount, status)
VALUES (999, CURRENT_DATE, 150.00, 'pending');

UPDATE orders SET status = 'shipped' WHERE customer_id = 999;

Exercise Tips

Use conditional triggers: CREATE TRIGGER ... WHEN (condition).
Create separate trigger functions for different operations for clarity.
Use to_jsonb(NEW) and to_jsonb(OLD) to store complete row data in audit logs.
Test triggers: INSERT/UPDATE/DELETE test data to verify trigger behavior.

Triggers and Event-Driven Programming