20-stored-procedures-triggers.md

Chapter 20: Stored Procedures & Triggers

Stored procedures and triggers allow you to embed logic directly into the database engine. This is the "programmable" side of SQL, enabling automation, complex data validation, and unbreakable audit trails.

20.1 User-Defined Functions (UDFs)

You can create custom functions using PL/pgSQL, PostgreSQL's procedural language. This allows you to use variables, IF/ELSE logic, and loops.

CREATE OR REPLACE FUNCTION categorize_review(msg TEXT)
RETURNS TEXT AS $$
BEGIN
    IF msg IS NULL THEN RETURN 'No Comment';
    ELSIF LENGTH(msg) < 50 THEN RETURN 'Short';
    ELSE RETURN 'Detailed';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Use it in a query
SELECT review_id, categorize_review(review_comment_message) FROM order_reviews;

20.2 Audit Triggers

A trigger is a function that automatically executes (or "fires") in response to specific events on a table, such as INSERT, UPDATE, or DELETE.

This is the standard way to implement audit logging:

1. Create an Audit Table: To store history.
2. Create a Trigger Function: To handle the logic of logging changes.
3. Attach the Trigger: To the main table.

-- Trigger Function
CREATE OR REPLACE FUNCTION log_city_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.customer_city IS DISTINCT FROM NEW.customer_city THEN
        INSERT INTO customer_audit_log(customer_id, old_city, new_city)
        VALUES (OLD.customer_id, OLD.customer_city, NEW.customer_city);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the Trigger
CREATE TRIGGER trg_audit_city
AFTER UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION log_city_change();

20.3 Why Use Procedures and Triggers?

• Encapsulation: Keep complex business logic near the data.
• Security: Ensure certain actions (like logging) happen regardless of which application connects to the DB.
• Efficiency: Reduce network round-trips by performing multi-step operations entirely on the server.

---

Exercises

1. Build a function that categorizes products into 'Cheap', 'Mid-Range', or 'Expensive' based on price.
2. What are the "magic variables" OLD and NEW inside a trigger function?
3. Explain a scenario where a trigger might be better than handling logic in your backend code.
4. Why must a trigger function always RETURN NEW or RETURN OLD?

Solutions

-- Exercise 1
CREATE FUNCTION cat_price(p NUMERIC) RETURNS TEXT AS $$
BEGIN
    IF p < 50 THEN RETURN 'Cheap';
    ELSIF p < 200 THEN RETURN 'Mid-Range';
    ELSE RETURN 'Expensive';
    END IF;
END; $$ LANGUAGE plpgsql;

-- Exercise 2
-- 'OLD' contains the row's values BEFORE the update/delete. 'NEW' contains the values AFTER the insert/update.

-- Exercise 3
-- Audit logging is a perfect case. If you have multiple applications (Web, Mobile, Admin Scripts) connecting to the same DB, a trigger ensures changes are logged no matter which app made the update.

-- Exercise 4
-- The database uses the returned value to decide what to actually write to the disk. If you return NULL in a BEFORE trigger, the operation is canceled!