Triggers_intro

Chapter 31: Triggers - Introduction

Automated Database Actions

---

31.1 Understanding Triggers

Triggers are database objects that automatically execute a specified function when certain events occur (INSERT, UPDATE, or DELETE).

                    Trigger Overview
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                      How Triggers Work                              │
    │                                                                      │
    │   Event occurs:                                                      │
    │   ┌─────────────┐                                                   │
    │   │ INSERT/UPDATE│ ──► Trigger fires ──► Function executes       │
    │   │ /DELETE     │      (BEFORE/AFTER)                             │
    │   └─────────────┘                                                   │
    │                                                                      │
    │   Trigger can:                                                       │
    │   ────────────                                                      │
    │   • Modify data (BEFORE trigger)                                    │
    │   • Validate data                                                   │
    │   • Audit changes                                                   │
    │   • Enforce business rules                                          │
    │   • Update related tables                                           │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

    Timing Options:
    ───────────────
    • BEFORE  → Executes before the operation
    • AFTER   → Executes after the operation
    • INSTEAD OF → For views (instead of the operation)

    Event Options:
    ─────────────
    • INSERT
    • UPDATE
    • DELETE
    • TRUNCATE

---

31.2 Creating Basic Triggers

-- Create audit log table
CREATE TABLE audit_log (
    audit_id SERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    action VARCHAR(10),
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT NOW()
);

-- Create trigger function
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, action, 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, action, 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, action, old_data, changed_by)
        VALUES (TG_TABLE_NAME, 'DELETE', row_to_json(OLD), current_user);
        RETURN OLD;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

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

-- Test the trigger
INSERT INTO users (username, email) VALUES ('test_user', 'test@example.com');
UPDATE users SET email = 'new_email@example.com' WHERE username = 'test_user';
DELETE FROM users WHERE username = 'test_user';

-- Check audit log
SELECT * FROM audit_log ORDER BY changed_at DESC;

---

31.3 BEFORE Triggers

BEFORE triggers can modify data before it’s inserted into the table.

-- Create table for tracking product prices
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    discount_price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Trigger function to auto-calculate discount
CREATE OR REPLACE FUNCTION set_discount_price()
RETURNS TRIGGER AS $$
BEGIN
    -- Auto-set discount price to 10% off
    NEW.discount_price := NEW.price * 0.90;

    -- Auto-update timestamp
    NEW.updated_at := NOW();

    -- Validate price
    IF NEW.price <= 0 THEN
        RAISE EXCEPTION 'Price must be positive';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create BEFORE INSERT trigger
CREATE TRIGGER products_before_insert
BEFORE INSERT ON products
FOR EACH ROW EXECUTE FUNCTION set_discount_price();

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

-- Test
INSERT INTO products (product_name, price)
VALUES ('Laptop', 999.99);

SELECT * FROM products;
-- discount_price will be 899.99 (10% off)

---

31.4 AFTER Triggers

AFTER triggers are used for actions that need to happen after the data change is complete.

-- Create order history table
CREATE TABLE order_history (
    history_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    action VARCHAR(20),
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    changed_at TIMESTAMP DEFAULT NOW()
);

-- Create notification table
CREATE TABLE notifications (
    notification_id SERIAL PRIMARY KEY,
    user_id INTEGER,
    message TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    read BOOLEAN DEFAULT FALSE
);

-- Trigger function for order status changes
CREATE OR REPLACE FUNCTION track_order_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        -- Record history
        INSERT INTO order_history (order_id, action, old_status, new_status)
        VALUES (NEW.order_id, 'STATUS_CHANGE', OLD.status, NEW.status);

        -- Send notification to user
        INSERT INTO notifications (user_id, message)
        VALUES (
            NEW.user_id,
            'Your order #' || NEW.order_id || ' status changed from ' ||
            COALESCE(OLD.status, 'N/A') || ' to ' || NEW.status
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create AFTER trigger
CREATE TRIGGER order_status_trigger
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION track_order_changes();

-- Test
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
SELECT * FROM order_history;
SELECT * FROM notifications;

---

31.5 Trigger Variables

PostgreSQL provides special variables for trigger functions.

-- Understanding trigger variables
CREATE OR REPLACE FUNCTION trigger_variables_demo()
RETURNS TRIGGER AS $$
DECLARE
    v_info TEXT;
BEGIN
    v_info := 'Trigger: ' || TG_NAME || E'\n' ||
              'Table: ' || TG_TABLE_NAME || E'\n' ||
              'Schema: ' || TG_TABLE_SCHEMA || E'\n' ||
              'Operation: ' || TG_OP || E'\n' ||
              'Level: ' || TG_LEVEL;

    RAISE NOTICE '%', v_info;

    -- OLD and NEW depend on operation
    IF TG_OP = 'INSERT' THEN
        RAISE NOTICE 'New row: %', row_to_json(NEW);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        RAISE NOTICE 'Old: %, New: %', row_to_json(OLD), row_to_json(NEW);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        RAISE NOTICE 'Deleted: %', row_to_json(OLD);
        RETURN OLD;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to test
CREATE TRIGGER test_trigger_variables
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION trigger_variables_demo();

-- Test it
INSERT INTO products (product_name, price) VALUES ('Test', 10.00);

---

31.6 INSTEAD OF Triggers (for Views)

INSTEAD OF triggers allow you to make views updatable.

-- Create a view
CREATE VIEW user_order_summary AS
SELECT
    u.user_id,
    u.username,
    COUNT(o.order_id) as order_count,
    COALESCE(SUM(o.total), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username;

-- Create trigger function for view
CREATE OR REPLACE FUNCTION instead_of_view_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO users (username, email)
        VALUES (NEW.username, NEW.email || '@default.com');
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE users
        SET username = NEW.username
        WHERE user_id = OLD.user_id;
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM users WHERE user_id = OLD.user_id;
        RETURN OLD;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create INSTEAD OF trigger on view
CREATE TRIGGER user_summary_instead_of
INSTEAD OF INSERT OR UPDATE OR DELETE ON user_order_summary
FOR EACH ROW EXECUTE FUNCTION instead_of_view_trigger();

-- Now you can "insert" into the view
INSERT INTO user_order_summary (username, order_count, total_spent)
VALUES ('new_user', 0, 0);

SELECT * FROM users WHERE username = 'new_user';

---

31.7 Conditional Triggers

Use WHEN clause to trigger only under certain conditions.

-- Trigger that only fires for specific conditions
CREATE TABLE price_changes (
    change_id SERIAL PRIMARY KEY,
    product_id INTEGER,
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2),
    changed_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION track_price_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO price_changes (product_id, old_price, new_price)
    VALUES (NEW.product_id, OLD.price, NEW.price);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Only trigger when price changes significantly (>10%)
CREATE TRIGGER significant_price_change
AFTER UPDATE ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price
      AND ABS(NEW.price - OLD.price) / OLD.price > 0.10)
EXECUTE FUNCTION track_price_changes();

-- Test with small change (won't trigger)
UPDATE products SET price = 1000.00 WHERE product_id = 1;
-- 0.001% change - won't trigger

-- Test with large change (will trigger)
UPDATE products SET price = 1200.00 WHERE product_id = 1;
-- ~20% change - will trigger

SELECT * FROM price_changes;

---

Summary

Timing	Use Case
BEFORE	Modify data before insert, validate
AFTER	Audit, notifications, related actions
INSTEAD OF	Make views updatable

---

Next: Chapter 32: Creating & Managing Triggers