Managing_triggers

Chapter 32: Creating & Managing Triggers

Advanced Trigger Management

---

32.1 Managing Triggers

Learn how to list, disable, enable, and drop triggers.

-- List all triggers on a table
SELECT
    trigger_name,
    event_manipulation,
    action_statement,
    action_timing,
    action_reference_old_row,
    action_reference_new_row
FROM information_schema.triggers
WHERE event_object_table = 'users';

-- Alternative: using pg_catalog
SELECT
    t.tgname as trigger_name,
    c.relname as table_name,
    p.proname as function_name,
    CASE t.tgtype & 1
        WHEN 1 THEN 'ROW'
        ELSE 'STATEMENT'
    END as level,
    CASE t.tgtype & 66
        WHEN 2 THEN 'BEFORE'
        WHEN 64 THEN 'INSTEAD OF'
        ELSE 'AFTER'
    END as timing,
    CASE
        WHEN t.tgtype & 4 = 4 THEN 'INSERT'
        WHEN t.tgtype & 8 = 8 THEN 'DELETE'
        WHEN t.tgtype & 16 = 16 THEN 'UPDATE'
    END as event_manipulation
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE c.relname = 'users';

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

-- Enable a trigger
ALTER TABLE users ENABLE TRIGGER users_audit;

-- Enable all triggers on a table
ALTER TABLE users ENABLE TRIGGER ALL;

-- Disable all triggers on a table
ALTER TABLE users DISABLE TRIGGER ALL;

---

32.2 Trigger Dependencies

Manage trigger dependencies and handle them properly.

-- Check trigger dependencies
SELECT
    d.refobjid::regclass as dependent_object,
    d.refobjname as dependent_name,
    d.refobjtype as dependent_type
FROM pg_depend d
JOIN pg_trigger t ON d.objid = t.oid
WHERE t.tgname = 'users_audit';

-- Drop trigger with CASCADE (drops dependent objects)
-- DROP TRIGGER users_audit ON users CASCADE;

-- Drop trigger with RESTRICT (fails if dependencies exist)
-- DROP TRIGGER users_audit ON users RESTRICT;

-- Rename trigger
ALTER TRIGGER users_audit ON users RENAME TO users_audit_new;

-- Recreate trigger with different function
CREATE OR REPLACE FUNCTION new_audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    -- New implementation
    INSERT INTO audit_log (table_name, action, new_data)
    VALUES (TG_TABLE_NAME, TG_OP,
        CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)
        ELSE row_to_json(NEW) END);

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Recreate trigger with new function
DROP TRIGGER IF EXISTS users_audit_new ON users;
CREATE TRIGGER users_audit_new
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION new_audit_trigger_func();

---

32.3 Multiple Triggers on Same Table

You can have multiple triggers for the same event.

-- First trigger: audit logging
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, new_data)
    VALUES (TG_TABLE_NAME, TG_OP,
        CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)
        ELSE row_to_json(NEW) END);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

-- Second trigger: validate data
CREATE OR REPLACE FUNCTION validate_order_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.total < 0 THEN
        RAISE EXCEPTION 'Order total cannot be negative';
    END IF;

    IF NEW.user_id IS NULL THEN
        RAISE EXCEPTION 'Order must have a user';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_validate
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION validate_order_trigger();

-- Third trigger: send notification
CREATE OR REPLACE FUNCTION notify_new_order_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO notifications (user_id, message)
    VALUES (
        NEW.user_id,
        'New order #' || NEW.order_id || ' created!'
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_notify
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_new_order_trigger();

---

32.4 Event Triggers

Event triggers fire on database-wide events, not table events.

-- Create event trigger function
CREATE OR REPLACE FUNCTION log_ddl_commands()
RETURNS EVENT TRIGGER AS $$
BEGIN
    RAISE NOTICE 'DDL Command: % on %',
        TG_EVENT,
        TG_TAG;
END;
$$ LANGUAGE plpgsql;

-- Create event trigger
CREATE EVENT TRIGGER ddl_log ON ddl_command_start
EXECUTE FUNCTION log_ddl_commands();

-- Event trigger for table creation
CREATE OR REPLACE FUNCTION notify_table_creation()
RETURNS EVENT TRIGGER AS $$
BEGIN
    IF TG_EVENT = 'CREATE' AND TG_TAG = 'CREATE TABLE' THEN
        RAISE NOTICE 'New table created: %',
            (pg_event_trigger_table_rewrite_oid()::regclass);
    END IF;
END;
$$ LANGUAGE plpgsql;

-- List event triggers
SELECT * FROM pg_event_trigger;

-- Drop event trigger
DROP EVENT TRIGGER IF EXISTS ddl_log;

---

32.5 Trigger Performance Considerations

    Trigger Performance Tips
    ========================================================================

    ✓ Best Practices:
    ─────────────────
    • Keep trigger functions simple and fast
    • Use BEFORE triggers for data modification
    • Use AFTER triggers for logging/notifications
    • Avoid long-running operations in triggers
    • Consider using NOTIFY for async processing

    ✗ Avoid:
    ───────────────
    • Complex queries in triggers
    • Triggers that call external services
    • Cascading triggers (trigger triggering other triggers)
    • Too many triggers on same event

    Alternative Approaches:
    ──────────────────────
    • Use rules instead of triggers for views
    • Use partitioning for large data operations
    • Use message queues for async processing

---

32.6 Debugging Triggers

-- Enable trigger debugging
-- Add logging to trigger function

CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Trigger: % | Table: % | Op: %',
        TG_NAME, TG_TABLE_NAME, TG_OP;

    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        RAISE NOTICE 'NEW: %', row_to_json(NEW);
    END IF;

    IF TG_OP IN ('UPDATE', 'DELETE') THEN
        RAISE NOTICE 'OLD: %', row_to_json(OLD);
    END IF;

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create debug trigger
CREATE TRIGGER debug_orders
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION debug_trigger();

-- Check trigger execution in pg_stat_activity
SELECT
    l.locktype,
    l.relation::regclass,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'orders'::regclass;

---

32.7 Complete Example: Complete Audit System

-- Complete audit system implementation
-- Tables for audit
CREATE TABLE audit_schema (
    audit_id BIGSERIAL PRIMARY KEY,
    schema_name TEXT NOT NULL,
    table_name TEXT NOT NULL,
    operation TEXT NOT NULL,
    changed_at TIMESTAMP DEFAULT NOW(),
    changed_by TEXT DEFAULT current_user
);

CREATE TABLE audit_changes (
    audit_id BIGINT REFERENCES audit_schema(audit_id),
    column_name TEXT,
    old_value TEXT,
    new_value TEXT
);

-- Audit function
CREATE OR REPLACE FUNCTION audit_all_changes()
RETURNS EVENT TRIGGER AS $$
DECLARE
    v_audit_id BIGINT;
    v_row RECORD;
    v_column TEXT;
BEGIN
    IF TG_EVENT = 'DROP' THEN
        INSERT INTO audit_schema (schema_name, table_name, operation)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, 'DROP')
        RETURNING audit_id INTO v_audit_id;
        RETURN NULL;
    END IF;

    -- Insert audit record
    INSERT INTO audit_schema (schema_name, table_name, operation)
    VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP)
    RETURNING audit_id INTO v_audit_id;

    -- For each changed row
    IF TG_OP = 'DELETE' THEN
        v_row := OLD;
    ELSE
        v_row := NEW;
    END IF;

    -- Get changed columns
    FOR v_column IN
        SELECT column_name::text
        FROM information_schema.columns
        WHERE table_schema = TG_TABLE_SCHEMA
          AND table_name = TG_TABLE_NAME
    LOOP
        IF TG_OP = 'UPDATE' THEN
            IF EXECUTE 'SELECT $1.' || v_column USING OLD
               <> EXECUTE 'SELECT $1.' || v_column USING NEW THEN
                INSERT INTO audit_changes (audit_id, column_name, old_value, new_value)
                VALUES (
                    v_audit_id,
                    v_column,
                    (SELECT to_jsonb(NEW)->>v_column),
                    (SELECT to_jsonb(OLD)->>v_column)
                );
            END IF;
        END IF;
    END LOOP;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create event trigger
CREATE EVENT TRIGGER audit_trigger
ON ddl_command_start
EXECUTE FUNCTION audit_all_changes();

-- Enable for all tables
ALTER EVENT TRIGGER audit_trigger ENABLE REPLICA;

---

Summary

Task	Command
List triggers	information_schema.triggers
Disable	ALTER TABLE DISABLE TRIGGER
Enable	ALTER TABLE ENABLE TRIGGER
Drop	DROP TRIGGER ON table
Rename	ALTER TRIGGER RENAME

---

Next: Chapter 33: Views - Virtual Tables