Views, Triggers & Procedures

Views & Automation

• Order Summary View: CREATE VIEW v_order_summary AS SELECT o.id, u.email, o.status, o.total, COUNT(oi.id) AS item_count, o.created_at FROM orders o JOIN users u ON o.user_id = u.id JOIN order_items oi ON o.id = oi.order_id GROUP BY o.id, u.email — query the view like a table: SELECT * FROM v_order_summary
• Product Catalog View: CREATE VIEW v_product_catalog AS SELECT p.*, c.name AS category_name, COALESCE(AVG(r.rating), 0) AS avg_rating, COUNT(r.id) AS review_count FROM products p LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN reviews r ON p.id = r.product_id WHERE p.is_active = true GROUP BY p.id, c.name — pre-joined product data for frontend
• Stock Update Trigger: CREATE TRIGGER trg_update_stock AFTER INSERT ON order_items FOR EACH ROW EXECUTE FUNCTION fn_reduce_stock(); — automatically decrease product stock when an order item is inserted
• Stock Function: CREATE FUNCTION fn_reduce_stock() RETURNS TRIGGER AS $$ BEGIN UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id; IF (SELECT stock FROM products WHERE id = NEW.product_id) < 0 THEN RAISE EXCEPTION 'Insufficient stock for product %', NEW.product_id; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql
• Order Total Procedure: CREATE PROCEDURE sp_calculate_order_total(p_order_id INTEGER) LANGUAGE plpgsql AS $$ BEGIN UPDATE orders SET subtotal = (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = p_order_id), tax = subtotal * 0.08, total = subtotal + tax + shipping WHERE id = p_order_id; END; $$
• Audit Log Trigger: CREATE TRIGGER trg_order_audit AFTER UPDATE ON orders FOR EACH ROW WHEN (OLD.status IS DISTINCT FROM NEW.status) EXECUTE FUNCTION fn_log_status_change(); — track every status change with timestamp and old/new values