Views, stored procedures, functions and triggers

Package queries as views, orchestrate multi-step work in routines, and decide honestly whether a trigger belongs in the database or in the application.

Views

CREATE OR REPLACE VIEW v_open_orders AS
SELECT o.id, o.status, o.total, c.name AS customer_name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status IN ('new', 'paid');

SELECT * FROM v_open_orders WHERE total > 100;

CREATE VIEW v_status_summary AS
SELECT status, COUNT(*) AS n FROM orders GROUP BY status;
-- not updatable: it aggregates

SHOW CREATE VIEW v_open_ordersG

• A view is a stored query, not a stored result. It costs nothing extra at read time beyond the query it contains, unless the algorithm forces a temporary table.
• A view is updatable when it maps to a single table with no aggregation, DISTINCT, UNION or derived column. Otherwise writes must go to the base tables.
• By default a view runs with the privileges of its definer. That is convenient, and it also means a view can grant indirect access to data the caller could not read directly. Use SQL SECURITY INVOKER when that matters.
• Views do not make a bad query fast. Check EXPLAIN against the view, not just against the statement inside it.

Procedures and functions

DELIMITER //
CREATE PROCEDURE ship_order (IN p_order_id BIGINT UNSIGNED, OUT p_ok TINYINT)
BEGIN
  DECLARE v_status VARCHAR(20);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET p_ok = 0;
    ROLLBACK;
  END;

  START TRANSACTION;
  SELECT status INTO v_status FROM orders WHERE id = p_order_id FOR UPDATE;

  IF v_status = 'paid' THEN
    UPDATE orders SET status = 'shipped' WHERE id = p_order_id;
    SET p_ok = 1;
    COMMIT;
  ELSE
    SET p_ok = 0;
    ROLLBACK;
  END IF;
END //
DELIMITER ;

CALL ship_order(1042, @ok);
SELECT @ok;

DELIMITER is a client directive, not SQL the server understands. It exists so the client does not stop reading at the first semicolon inside the routine body. Use the same delimiter in scripts that recreate the routine, or the load will fail halfway.

• A procedure returns values through OUT parameters or result sets; a function returns one value and can be called inside a query.
• An EXIT HANDLER turns an unexpected error into a defined outcome. Without one, a failure halfway through leaves the caller with an open transaction.
• Routines are interpreted statement by statement. A loop over rows is orders of magnitude slower than the equivalent set-based statement, so keep procedures for orchestration rather than for data crunching.

Triggers and the maintenance trade-off

DELIMITER //
CREATE TRIGGER trg_orders_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  IF NEW.status <> OLD.status THEN
    INSERT INTO order_status_log (order_id, old_status, new_status, changed_at)
    VALUES (NEW.id, OLD.status, NEW.status, CURRENT_TIMESTAMP(3));
  END IF;
END //
DELIMITER ;

SHOW TRIGGERS;
DROP TRIGGER trg_orders_audit;

• A trigger runs inside the transaction of the statement that fired it, so a failing trigger rolls the whole statement back — including the change the caller thought it made.
• Row-level triggers fire once per row. A bulk update of a million rows runs the trigger a million times, which is easy to miss when reviewing the statement.
• Triggers are invisible from application code. Debugging a missing row is far harder when a trigger, rather than the service, removed it.
• MySQL has no INSTEAD OF triggers on tables, and a table cannot carry two triggers with the same timing and event.
• Keep rules that change often, need tests, or must be observable in the application. Keep in the database the invariants that every writer must respect, including ad-hoc SQL.

FAQ

Related

Writing data safely: DML and transactions JSON, generated columns and window functions

Last refreshed 2026-09-18.