PostgreSQL Triggers

Key Trigger Concepts

BEFORE INSERT, AFTER UPDATE

ON INSERT OR UPDATE

FOR EACH ROW

EXECUTE FUNCTION func()

🔹 Creating a Basic Trigger

Triggers require two components: a trigger function that contains the logic, and a trigger definition that specifies when to execute the function. The function must return a trigger type.

-- Create a table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    updated_at TIMESTAMP
);

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

-- Create trigger
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Trigger created successfully.
Now every UPDATE will automatically set updated_at timestamp.

🔹 BEFORE vs AFTER Triggers

BEFORE triggers execute before the data modification and can modify the NEW values. AFTER triggers execute after the change is committed and are useful for logging or cascading operations.

-- BEFORE trigger (can modify data)
CREATE OR REPLACE FUNCTION validate_price()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Price cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

-- AFTER trigger (for logging)
CREATE TABLE product_log (
    id SERIAL PRIMARY KEY,
    product_id INTEGER,
    action VARCHAR(10),
    changed_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_product_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO product_log (product_id, action)
    VALUES (NEW.id, TG_OP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER product_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_changes();

🔹 Trigger Variables

PostgreSQL provides special variables within trigger functions to access context information. These variables help you determine what operation triggered the function and access old and new row values.

-- Using trigger variables
CREATE OR REPLACE FUNCTION track_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- TG_OP: Operation type (INSERT, UPDATE, DELETE)
    -- TG_TABLE_NAME: Name of the table
    -- OLD: Row before change (UPDATE/DELETE)
    -- NEW: Row after change (INSERT/UPDATE)
    
    IF TG_OP = 'INSERT' THEN
        RAISE NOTICE 'New product added: %', NEW.name;
    ELSIF TG_OP = 'UPDATE' THEN
        RAISE NOTICE 'Product % changed from % to %', 
            NEW.name, OLD.price, NEW.price;
    ELSIF TG_OP = 'DELETE' THEN
        RAISE NOTICE 'Product deleted: %', OLD.name;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

TG_OP        - Operation type
TG_TABLE_NAME - Table name
NEW          - New row data
OLD          - Old row data
TG_WHEN      - BEFORE or AFTER

🔹 Conditional Triggers

You can add WHEN conditions to triggers to execute them only when specific criteria are met. This improves performance by avoiding unnecessary function calls for irrelevant changes.

-- Trigger only when price changes significantly
CREATE TRIGGER price_change_alert
AFTER UPDATE ON products
FOR EACH ROW
WHEN (ABS(NEW.price - OLD.price) > 100)
EXECUTE FUNCTION notify_price_change();

-- Trigger only for specific column updates
CREATE TRIGGER name_change_log
AFTER UPDATE OF name ON products
FOR EACH ROW
EXECUTE FUNCTION log_name_change();

🔹 Managing Triggers

PostgreSQL provides commands to view, disable, enable, and drop triggers. These management operations help you control trigger behavior during maintenance or troubleshooting without deleting them permanently.

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

-- Disable a trigger
ALTER TABLE products DISABLE TRIGGER set_timestamp;

-- Enable a trigger
ALTER TABLE products ENABLE TRIGGER set_timestamp;

-- Drop a trigger
DROP TRIGGER IF EXISTS set_timestamp ON products;

-- Drop trigger function
DROP FUNCTION IF EXISTS update_timestamp();

🔹 Common Use Cases

Triggers are powerful tools for automating database operations and maintaining data consistency. They excel at tasks that need to happen automatically whenever data changes occur in your tables.