PostgreSQL Triggers

Overview

Triggers are functions that are automatically executed when specific events (INSERT, UPDATE, DELETE, TRUNCATE) occur. Triggers can be used for data validation, audit logging, automatic calculations, and more.

Trigger Types

Type	Description
BEFORE	Fires before the operation is executed
AFTER	Fires after the operation is executed
INSTEAD OF	Replaces the operation (views only)
FOR EACH ROW	Fires once per affected row
FOR EACH STATEMENT	Fires once per statement

Creating Triggers

Basic Steps

1. Create the trigger function
2. Create the trigger

Trigger Function

CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger logic
    RETURN NEW;  -- For BEFORE triggers, return NEW or NULL
END;
$$ LANGUAGE plpgsql;

Create Trigger Syntax

CREATE TRIGGER trigger_name
    {BEFORE | AFTER | INSTEAD OF}
    {INSERT | UPDATE | DELETE | TRUNCATE}
    ON table_name
    [FOR EACH {ROW | STATEMENT}]
    [WHEN (condition)]
    EXECUTE FUNCTION trigger_function_name();

Trigger Examples

Auto-Update Timestamp

-- Create trigger function
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

Audit Logging

-- Audit table
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(50),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger function
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, new_data, changed_by)
        VALUES (TG_TABLE_NAME, 'INSERT', row_to_json(NEW), current_user);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_by)
        VALUES (TG_TABLE_NAME, 'UPDATE', row_to_json(OLD), row_to_json(NEW), current_user);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, changed_by)
        VALUES (TG_TABLE_NAME, 'DELETE', row_to_json(OLD), current_user);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for table
CREATE TRIGGER users_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger();

Data Validation

-- Validate email format
CREATE OR REPLACE FUNCTION validate_email()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email format: %', NEW.email;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_email
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION validate_email();

Automatic Calculation

-- Auto-calculate order total
CREATE OR REPLACE FUNCTION calculate_order_total()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders
    SET total_amount = (
        SELECT COALESCE(SUM(quantity * unit_price), 0)
        FROM order_items
        WHERE order_id = NEW.order_id
    )
    WHERE id = NEW.order_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_order_total
    AFTER INSERT OR UPDATE OR DELETE ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION calculate_order_total();

Special Variables in Triggers

Variable	Description
NEW	New row data for INSERT/UPDATE
OLD	Old row data for UPDATE/DELETE
TG_NAME	Trigger name
TG_TABLE_NAME	Table name where trigger is defined
TG_OP	Operation type (INSERT/UPDATE/DELETE/TRUNCATE)
TG_WHEN	Trigger timing (BEFORE/AFTER/INSTEAD OF)
TG_LEVEL	Trigger level (ROW/STATEMENT)

Conditional Triggers

Use WHEN clause to specify trigger conditions:

-- Only fire when status changes
CREATE TRIGGER status_change_trigger
    AFTER UPDATE ON orders
    FOR EACH ROW
    WHEN (OLD.status IS DISTINCT FROM NEW.status)
    EXECUTE FUNCTION notify_status_change();

-- Only fire when price changes
CREATE TRIGGER price_change_trigger
    BEFORE UPDATE ON products
    FOR EACH ROW
    WHEN (OLD.price <> NEW.price)
    EXECUTE FUNCTION log_price_change();

Managing Triggers

View Triggers

-- View triggers on a table
SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers
WHERE event_object_table = 'users';

-- Using psql command
\dS+ users

Disable and Enable Triggers

-- Disable single trigger
ALTER TABLE users DISABLE TRIGGER users_audit;

-- Disable all triggers
ALTER TABLE users DISABLE TRIGGER ALL;

-- Enable triggers
ALTER TABLE users ENABLE TRIGGER users_audit;
ALTER TABLE users ENABLE TRIGGER ALL;

Drop Triggers

-- Drop trigger
DROP TRIGGER trigger_name ON table_name;

-- Drop if exists
DROP TRIGGER IF EXISTS trigger_name ON table_name;

INSTEAD OF Triggers (Views)

-- Create view
CREATE VIEW user_profiles AS
SELECT u.id, u.username, p.bio, p.avatar
FROM users u
LEFT JOIN profiles p ON u.id = p.user_id;

-- Create INSTEAD OF trigger for the view
CREATE OR REPLACE FUNCTION update_user_profile()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE users SET username = NEW.username WHERE id = NEW.id;
    UPDATE profiles SET bio = NEW.bio, avatar = NEW.avatar
    WHERE user_id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_profile_trigger
    INSTEAD OF UPDATE ON user_profiles
    FOR EACH ROW
    EXECUTE FUNCTION update_user_profile();

Event Triggers

PostgreSQL also supports database-level event triggers:

-- Log DDL operations
CREATE OR REPLACE FUNCTION log_ddl_command()
RETURNS event_trigger AS $$
BEGIN
    INSERT INTO ddl_log (command_tag, object_type, schema_name, object_identity)
    SELECT tg_tag, object_type, schema_name, object_identity
    FROM pg_event_trigger_ddl_commands();
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER log_ddl
    ON ddl_command_end
    EXECUTE FUNCTION log_ddl_command();

Best Practices

1. Keep it simple: Trigger functions should be concise and efficient
2. Avoid recursion: Be careful of triggers triggering other triggers
3. Error handling: Use EXCEPTION blocks to handle errors
4. Performance considerations: Triggers affect DML operation performance
5. Document: Record the purpose and behavior of triggers
6. Test thoroughly: Test triggers in various scenarios