SQL Views, Stored Procedures & Triggers

Server-side SQL every data analyst should know — views, materialised views, stored procedures with error handling, UDFs, triggers, and transactions.

-- Simple view: clean column names and a calculated field
CREATE OR REPLACE VIEW vw_order_summary AS
SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    c.region,
    p.product_name,
    p.category,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price              AS line_revenue,
    oi.unit_price - p.cost_price             AS unit_margin,
    (oi.unit_price - p.cost_price) * 100.0
        / NULLIF(oi.unit_price, 0)           AS margin_pct
FROM orders          o
JOIN customers       c  ON o.customer_id  = c.customer_id
JOIN order_items     oi ON o.order_id     = oi.order_id
JOIN products        p  ON oi.product_id  = p.product_id;

-- Now analysts query the view instead of the raw join
SELECT region, SUM(line_revenue) AS revenue
FROM   vw_order_summary
WHERE  order_date >= '2026-01-01'
GROUP  BY region
ORDER  BY revenue DESC;

-- A view is updatable when it maps directly to one base table
-- and has no aggregations, DISTINCT, GROUP BY, UNION, or subqueries

CREATE OR REPLACE VIEW vw_active_customers AS
SELECT customer_id, customer_name, email, region
FROM   customers
WHERE  is_active = 1;

-- UPDATE through the view — modifies the underlying table
UPDATE vw_active_customers
SET    region = 'South'
WHERE  customer_id = 1042;

-- Complex views (JOINs, aggregations) are NOT updatable — read-only only

-- Drop a view
DROP VIEW IF EXISTS vw_order_summary;

-- List all views in the current database
-- MySQL:
SELECT TABLE_NAME, VIEW_DEFINITION
FROM   INFORMATION_SCHEMA.VIEWS
WHERE  TABLE_SCHEMA = DATABASE();

-- PostgreSQL:
SELECT viewname, definition
FROM   pg_views
WHERE  schemaname = 'public';

-- Create a materialised view (PostgreSQL syntax)
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date)  AS month,
    region,
    category,
    SUM(line_revenue)                AS total_revenue,
    COUNT(DISTINCT customer_id)      AS unique_customers,
    AVG(line_revenue)                AS avg_order_value
FROM   vw_order_summary
GROUP  BY 1, 2, 3;

-- Create an index on the materialised view for fast filtering
CREATE INDEX idx_mv_monthly_region ON mv_monthly_revenue(region);
CREATE INDEX idx_mv_monthly_month  ON mv_monthly_revenue(month);

-- Refresh the snapshot manually
REFRESH MATERIALIZED VIEW mv_monthly_revenue;

-- Refresh without locking (allows reads during refresh)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

-- Schedule automatic refresh using pg_cron (once installed):
SELECT cron.schedule(
    'refresh_monthly_revenue',
    '0 2 * * *',   -- every day at 2am
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue'
);

-- Drop
DROP MATERIALIZED VIEW IF EXISTS mv_monthly_revenue;

MySQL does not have native materialised views. The common pattern is to create a regular table, populate it with INSERT INTO … SELECT …, and refresh it via a scheduled stored procedure or event. BigQuery and Snowflake both support materialised views natively.

DELIMITER $$

CREATE PROCEDURE GetRegionSales(
    IN  p_region     VARCHAR(50),
    IN  p_start_date DATE,
    IN  p_end_date   DATE,
    OUT p_total      DECIMAL(15, 2)
)
BEGIN
    SELECT SUM(line_revenue)
    INTO   p_total
    FROM   vw_order_summary
    WHERE  region     = p_region
      AND  order_date BETWEEN p_start_date AND p_end_date;
END$$

DELIMITER ;

-- Call the procedure
CALL GetRegionSales('North', '2026-01-01', '2026-09-30', @total);
SELECT @total AS north_revenue;

DELIMITER $$

CREATE PROCEDURE ProcessMonthEndReport(IN p_year INT, IN p_month INT)
BEGIN
    DECLARE v_start_date DATE;
    DECLARE v_end_date   DATE;
    DECLARE v_row_count  INT DEFAULT 0;

    -- Build date range from parameters
    SET v_start_date = DATE(CONCAT(p_year, '-', LPAD(p_month, 2, '0'), '-01'));
    SET v_end_date   = LAST_DAY(v_start_date);

    -- Clear existing report data for this period
    DELETE FROM monthly_report_cache
    WHERE  report_month = v_start_date;

    -- Populate with fresh aggregation
    INSERT INTO monthly_report_cache
        (report_month, region, total_revenue, unique_customers, avg_order_value)
    SELECT
        v_start_date,
        region,
        SUM(line_revenue),
        COUNT(DISTINCT customer_id),
        AVG(line_revenue)
    FROM   vw_order_summary
    WHERE  order_date BETWEEN v_start_date AND v_end_date
    GROUP  BY region;

    SET v_row_count = ROW_COUNT();

    -- Log the run
    INSERT INTO procedure_run_log (proc_name, run_at, rows_affected, status)
    VALUES ('ProcessMonthEndReport', NOW(), v_row_count, 'SUCCESS');

    SELECT CONCAT('Processed ', v_row_count, ' rows for ',
                  DATE_FORMAT(v_start_date, '%M %Y')) AS result;
END$$

DELIMITER ;

CALL ProcessMonthEndReport(2026, 9);

DELIMITER $$

CREATE PROCEDURE SafeUpdatePrice(
    IN p_product_id INT,
    IN p_new_price  DECIMAL(10, 2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        INSERT INTO error_log (error_time, procedure_name, details)
        VALUES (NOW(), 'SafeUpdatePrice',
                CONCAT('Failed for product_id=', p_product_id));
        RESIGNAL;   -- re-raise so the caller knows it failed
    END;

    START TRANSACTION;

        -- Archive the old price
        INSERT INTO product_price_history (product_id, old_price, changed_at)
        SELECT product_id, price, NOW()
        FROM   products
        WHERE  product_id = p_product_id;

        -- Apply the new price
        UPDATE products
        SET    price = p_new_price,
               updated_at = NOW()
        WHERE  product_id = p_product_id;

        IF ROW_COUNT() = 0 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Product not found';
        END IF;

    COMMIT;
END$$

DELIMITER ;

-- MySQL: scalar UDF
DELIMITER $$

CREATE FUNCTION TierLabel(revenue DECIMAL(15,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE tier VARCHAR(20);
    IF     revenue >= 100000 THEN SET tier = 'Platinum';
    ELSEIF revenue >= 50000  THEN SET tier = 'Gold';
    ELSEIF revenue >= 10000  THEN SET tier = 'Silver';
    ELSE                          SET tier = 'Bronze';
    END IF;
    RETURN tier;
END$$

DELIMITER ;

-- Use inside a SELECT like any built-in function
SELECT
    customer_id,
    SUM(line_revenue) AS ltv,
    TierLabel(SUM(line_revenue)) AS tier
FROM   vw_order_summary
GROUP  BY customer_id;


-- PostgreSQL: same concept, different syntax
CREATE OR REPLACE FUNCTION tier_label(revenue NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN CASE
        WHEN revenue >= 100000 THEN 'Platinum'
        WHEN revenue >= 50000  THEN 'Gold'
        WHEN revenue >= 10000  THEN 'Silver'
        ELSE 'Bronze'
    END;
END;
$$;

-- Returns a table — use in FROM clause like a parameterised view
CREATE OR REPLACE FUNCTION get_top_customers(
    p_region   TEXT,
    p_top_n    INT DEFAULT 10
)
RETURNS TABLE (
    customer_id   INT,
    customer_name TEXT,
    total_revenue NUMERIC,
    order_count   BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        s.customer_id,
        c.customer_name,
        SUM(s.line_revenue)::NUMERIC,
        COUNT(DISTINCT s.order_id)
    FROM   vw_order_summary s
    JOIN   customers c USING (customer_id)
    WHERE  s.region = p_region
    GROUP  BY s.customer_id, c.customer_name
    ORDER  BY SUM(s.line_revenue) DESC
    LIMIT  p_top_n;
END;
$$;

-- Use in a query
SELECT * FROM get_top_customers('North', 5);

DELIMITER $$

CREATE TRIGGER validate_order_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- Reject negative order totals
    IF NEW.order_total < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Order total cannot be negative';
    END IF;

    -- Default status if not provided
    IF NEW.status IS NULL OR NEW.status = '' THEN
        SET NEW.status = 'pending';
    END IF;

    -- Set created_at automatically (even if app forgets)
    SET NEW.created_at = COALESCE(NEW.created_at, NOW());
END$$

DELIMITER ;

-- Track every price change with before/after values
DELIMITER $$

CREATE TRIGGER log_price_changes
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO price_audit_log (
            product_id, old_price, new_price,
            changed_at, changed_by
        )
        VALUES (
            OLD.product_id, OLD.price, NEW.price,
            NOW(), CURRENT_USER()
        );
    END IF;
END$$

DELIMITER ;

-- After this trigger exists, every UPDATE on products that changes price
-- automatically creates an audit record — no application code needed

DELIMITER $$

CREATE TRIGGER archive_deleted_customers
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO customers_archive (
        customer_id, customer_name, email, region,
        deleted_at, deleted_by
    )
    VALUES (
        OLD.customer_id, OLD.customer_name, OLD.email, OLD.region,
        NOW(), CURRENT_USER()
    );
END$$

DELIMITER ;

-- MySQL: list triggers on a table
SHOW TRIGGERS FROM your_database LIKE 'orders';

-- MySQL: full details
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,
       ACTION_TIMING, CREATED
FROM   INFORMATION_SCHEMA.TRIGGERS
WHERE  TRIGGER_SCHEMA = DATABASE();

-- Drop a trigger
DROP TRIGGER IF EXISTS log_price_changes;

-- Pattern: wrap multi-step operations in a transaction
START TRANSACTION;

    -- Step 1: deduct inventory
    UPDATE inventory
    SET    quantity_on_hand = quantity_on_hand - 5
    WHERE  product_id = 101;

    -- Step 2: insert the order
    INSERT INTO orders (customer_id, product_id, quantity, order_date)
    VALUES (2047, 101, 5, CURDATE());

    -- Step 3: log the sale
    INSERT INTO sales_log (order_id, logged_at)
    VALUES (LAST_INSERT_ID(), NOW());

-- Only commit if all three succeeded
COMMIT;

-- If anything failed (or you detect a problem), undo everything
-- ROLLBACK;


-- SAVEPOINT: partial rollback within a transaction
START TRANSACTION;

    INSERT INTO staging_orders SELECT * FROM raw_import;
    SAVEPOINT after_load;

    -- Validate
    IF (SELECT COUNT(*) FROM staging_orders WHERE total < 0) > 0 THEN
        ROLLBACK TO SAVEPOINT after_load;  -- undo only the bad rows
        -- handle error
    END IF;

COMMIT;