MENU


Stored Routines, Triggers, and Views

MySQL can store code beside the tables. Stored programs, written in BEGIN ... END blocks with variables, loops and handlers, come in four kinds: a procedure you CALL, a function used in expressions, a trigger fired by row changes, and an event run on a clock. A view stores a named SELECT. The listings run in order against shop. mysqldump saves routines and events only with --routines and --events (Section 3.17).

3.15.1 Stored Procedures and the Delimiter Problem

A stored procedure is a named block of statements run with CALL; it takes parameters, can return result sets and can change data. Its body contains semicolons, and the mysql client splits its input at every ;. Typed without the DELIMITER lines, the procedure below reaches the server in two pieces and fails twice with ERROR 1064 (42000): You have an error in your SQL syntax. DELIMITER // makes the client split on // instead until you switch back:

DELIMITER //

CREATE PROCEDURE customer_orders(IN p_email VARCHAR(255))

READS SQL DATA

BEGIN

SELECT o.id, o.status, o.ordered_at

FROM orders AS o JOIN customers AS c ON c.id = o.customer_id

WHERE c.email = p_email ORDER BY o.ordered_at;

END //

DELIMITER ;

CALL customer_orders('ana@example.com');

Listing 3.131: DELIMITER lets the client pass the whole body to the server

+----+---------+---------------------+

| id | status | ordered_at |

+----+---------+---------------------+

| 1 | shipped | 2026-03-02 10:15:00 |

| 4 | shipped | 2026-05-05 12:00:00 |

+----+---------+---------------------+

Output of Listing 3.131 The server never sees DELIMITER: PDO sends statements whole, so PHP (Section 4.16) and Laravel migrations (Section 5.9) omit it, and a one-statement body needs no BEGIN ... END (Section 3.15.2). Data-access labels such as READS SQL DATA are not checked. SQL SECURITY DEFINER, the default, runs the body with its creator's privileges, so a procedure can be an account's only door into a table (Section 3.16.9). A routine keeps the sql_mode in force when it was created.

3.15.2 Stored Functions and Determinism

A stored function returns one value and goes wherever an expression may: a select list, WHERE, ORDER BY or another routine. It cannot return a result set, commit, or call itself. Binary logging is on by default, and there the first attempt at an order total fails:

CREATE FUNCTION order_total(p_order INT UNSIGNED) RETURNS DECIMAL(10,2)

RETURN (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = p_order);

CREATE FUNCTION order_total(p_order INT UNSIGNED) RETURNS DECIMAL(10,2) READS SQL DATA

RETURN (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = p_order);

CREATE FUNCTION add_vat(p DECIMAL(10,2)) RETURNS DECIMAL(10,2) NO SQL DETERMINISTIC

RETURN ROUND(p * 1.08, 2);

CREATE FUNCTION stamp() RETURNS DATETIME DETERMINISTIC RETURN NOW();

SELECT id, order_total(id) AS total, add_vat(order_total(id)) AS with_vat,

stamp() = NOW() AS lie_accepted FROM orders WHERE order_total(id) > 88;

Listing 3.132: Stored functions, and the declaration that binary logging demands

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in ...

+----+--------+----------+--------------+

| id | total | with_vat | lie_accepted |

+----+--------+----------+--------------+

| 2 | 88.47 | 95.55 | 1 |

| 6 | 128.80 | 139.10 | 1 |

+----+--------+----------+--------------+

Output of Listing 3.132 With the binary log on, a function must declare itself DETERMINISTIC (same arguments, same result) or free of writes (NO SQL, READS SQL DATA), because statement-based replication reruns the call. MySQL takes your word: the nondeterministic stamp() was accepted, and the manual warns that such a lie can mislead the optimizer. SET GLOBAL log_bin_trust_function_creators = 1 lifts the check, but on a test server 9.7 answers with warning 1287, '@@log_bin_trust_function_creators' is deprecated and will be removed in a future release. The default ROW binlog format logs rows, not calls; label honestly and leave the variable alone.

3.15.3 Variables, Parameters, and Flow Control

Parameters are IN (the default: a copy), OUT (starts as NULL; its final value lands in the caller's variable) or INOUT (both). Local variables come from DECLARE at the top of a BEGIN ... END block and die with it. User variables such as @fee belong to the session and carry OUT values back to the client. A bare name means a local variable before a column, so prefix parameters p_ and locals v_.

DELIMITER //

CREATE PROCEDURE shipping_quote(IN p_order INT UNSIGNED, OUT p_fee DECIMAL(6,2),

INOUT p_note VARCHAR(80))

BEGIN

DECLARE v_country CHAR(2);

SELECT c.country INTO v_country

FROM orders AS o JOIN customers AS c ON c.id = o.customer_id WHERE o.id = p_order;

CASE v_country

WHEN 'MY' THEN SET p_fee = 5.00;

WHEN 'US' THEN SET p_fee = 12.00;

END CASE;

IF order_total(p_order) >= 80 THEN

SET p_fee = 0, p_note = CONCAT(p_note, ' ships free');

END IF;

END //

DELIMITER ;

SET @note = 'Order 2:';

CALL shipping_quote(2, @fee, @note);

SELECT @fee, @note;

CALL shipping_quote(1, @fee, @note);

Listing 3.133: IN, OUT and INOUT parameters with CASE and IF

+------+---------------------+

| @fee | @note |

+------+---------------------+

| 0.00 | Order 2: ships free |

+------+---------------------+

ERROR 1339 (20000): Case not found for CASE statement

Output of Listing 3.133 Order 1's customer is in Brazil. A CASE statement with no matching branch raises error 1339 (a CASE expression returns NULL), so give it an ELSE. SELECT ... INTO wants one row: none leaves the variable unchanged with warning 1329, two raise error 1172. Loops: WHILE ... DO tests first, REPEAT ... UNTIL tests last, and a labeled LOOP runs until LEAVE label (Section 3.15.4), with ITERATE label starting the next pass.

3.15.4 Cursors, Conditions, and Handlers

A cursor walks a result set row by row, forward only. A handler reacts to a condition (an error number, an SQLSTATE, SQLEXCEPTION, SQLWARNING or NOT FOUND) and then continues (CONTINUE) or leaves the block (EXIT). SIGNAL raises your own condition, and RESIGNAL re-raises the one being handled. Declare variables, then cursors, then handlers. place_order walks a JSON cart (Section 3.9.5) with the checkout pattern of Section 3.14.2:

DELIMITER //

CREATE PROCEDURE place_order(IN p_customer INT UNSIGNED, IN p_cart JSON,

OUT p_order INT UNSIGNED)

BEGIN

DECLARE v_done BOOL DEFAULT FALSE;

DECLARE v_product, v_qty INT UNSIGNED;

DECLARE cart CURSOR FOR SELECT id, qty FROM JSON_TABLE(p_cart, '$[*]'

COLUMNS (id INT UNSIGNED PATH '$.id', qty INT UNSIGNED PATH '$.qty')) AS c;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

START TRANSACTION;

INSERT INTO orders (customer_id, ordered_at) VALUES (p_customer, NOW());

SET p_order = LAST_INSERT_ID();

OPEN cart;

cart_loop: LOOP

FETCH cart INTO v_product, v_qty;

IF v_done THEN LEAVE cart_loop; END IF;

UPDATE products SET stock = stock - v_qty WHERE id = v_product AND stock >= v_qty;

IF ROW_COUNT() = 0 THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Out of stock', MYSQL_ERRNO = 5001;

END IF;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)

SELECT p_order, id, v_qty, price FROM products WHERE id = v_product;

END LOOP;

CLOSE cart;

COMMIT;

END //

DELIMITER ;

CALL place_order(7, '[{"id": 5, "qty": 2}, {"id": 7, "qty": 1}]', @id);

CALL place_order(7, '[{"id": 1, "qty": 1}, {"id": 3, "qty": 1}]', @id);

SELECT @id AS order_id, order_total(@id) AS total, (SELECT COUNT(*) FROM orders) AS orders,

(SELECT GROUP_CONCAT(stock ORDER BY id) FROM products WHERE id IN (1, 5, 7)) AS stock_1_5_7;

Listing 3.134: place_order: a cursor, two handlers, SIGNAL and a transaction

ERROR 5001 (45000): Out of stock

+----------+-------+--------+-------------+

| order_id | total | orders | stock_1_5_7 |

+----------+-------+--------+-------------+

| 10 | 80.50 | 10 | 25,5,59 |

+----------+-------+--------+-------------+

Output of Listing 3.134 Order 10 went through. The second cart took a copy of product 1 and an order row before finding product 3 sold out, and the EXIT handler rolled both back. Without RESIGNAL the caller would see success; PHP receives a PDOException with this SQLSTATE and message (Section 4.16.3), and @id keeps its old value. In a handler, GET STACKED DIAGNOSTICS reads the error for logging. Beware: NOT FOUND also fires on a SELECT ... INTO that finds nothing, and START TRANSACTION inside a routine commits any transaction the caller had open.

3.15.5 Triggers and Their Limits

A trigger runs FOR EACH ROW of an INSERT, UPDATE or DELETE on one table, BEFORE or AFTER the row is written. OLD.col is the row as it was, NEW.col as it will be; only a BEFORE trigger may assign NEW.col, and a SIGNAL there cancels the change. Here cancelling an order restocks its books, and every stock change is audited:

CREATE TABLE stock_audit (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED,

old_stock INT, new_stock INT, changed_by VARCHAR(100), changed_at DATETIME DEFAULT NOW());

DELIMITER //

CREATE TRIGGER orders_guard BEFORE UPDATE ON orders FOR EACH ROW

IF OLD.status = 'shipped' AND NEW.status = 'cancelled' THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'A shipped order cannot be cancelled';

END IF //

CREATE TRIGGER orders_restock AFTER UPDATE ON orders FOR EACH ROW

IF NEW.status = 'cancelled' AND OLD.status <> 'cancelled' THEN

UPDATE products AS p JOIN order_items AS i ON i.product_id = p.id

SET p.stock = p.stock + i.quantity WHERE i.order_id = NEW.id;

END IF //

CREATE TRIGGER products_audit AFTER UPDATE ON products FOR EACH ROW

IF NEW.stock <> OLD.stock THEN

INSERT INTO stock_audit (product_id, old_stock, new_stock, changed_by)

VALUES (NEW.id, OLD.stock, NEW.stock, CURRENT_USER());

END IF //

DELIMITER ;

CALL place_order(8, '[{"id": 7, "qty": 3}]', @id);

UPDATE orders SET status = 'cancelled' WHERE id = @id;

UPDATE orders SET status = 'cancelled' WHERE id = 1;

SELECT product_id, old_stock, new_stock, changed_by FROM stock_audit;

Listing 3.135: A guard, a restock and an audit trigger

ERROR 1644 (45000): A shipped order cannot be cancelled

+------------+-----------+-----------+----------------+

| product_id | old_stock | new_stock | changed_by |

+------------+-----------+-----------+----------------+

| 7 | 59 | 56 | root@localhost |

| 7 | 56 | 59 | root@localhost |

+------------+-----------+-----------+----------------+

Output of Listing 3.135 The first audit row comes from place_order, which knows nothing about auditing, the second from orders_restock firing products_audit. It all belongs to the triggering statement, so any error rolls the statement back. The limits:

  • A trigger cannot write to its own table (error 1442, Can't update table 'products' in stored function/trigger), and setting NEW.col in an AFTER trigger is error 1362.

  • There are no statement-level triggers, foreign-key actions such as ON DELETE CASCADE fire none, and a row-based replica applies logged rows without running its own.

  • FOLLOWS or PRECEDES orders triggers that share a timing and event. PHP code cannot see triggers, so keep them short and check SHOW TRIGGERS.

    • 3.15.6 Scheduled Events

    An event is a stored program on a schedule: AT a timestamp, or EVERY an interval between optional STARTS and ENDS. The global event_scheduler thread, ON by default, runs it as its DEFINER; toggle that thread only on a test server of your own (Section 3.1.6). This event cancels orders left unpaid for two weeks, first firing two seconds after creation:

    CREATE EVENT expire_pending

    ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 2 SECOND

    DO UPDATE orders SET status = 'cancelled'

    WHERE status = 'pending' AND ordered_at < NOW() - INTERVAL 14 DAY;

    DO SLEEP(3);

    SELECT e.STATUS, e.LAST_EXECUTED, o.status AS order_8 FROM information_schema.EVENTS AS e

    JOIN orders AS o ON o.id = 8 WHERE e.EVENT_NAME = 'expire_pending';

    Listing 3.136: A daily housekeeping event

    +---------+---------------------+-----------+

    | STATUS | LAST_EXECUTED | order_8 |

    +---------+---------------------+-----------+

    | ENABLED | 2026-09-23 08:34:34 | cancelled |

    +---------+---------------------+-----------+

    Output of Listing 3.136 SET GLOBAL event_scheduler = OFF pauses all events; a one-off event that fell due meanwhile ran when the scheduler came back ON, then dropped itself (ON COMPLETION NOT PRESERVE is the default). DISABLED is startup-only (error 1231 at runtime). Event errors reach only the error log, and replicas receive events as REPLICA_SIDE_DISABLED (Section 3.17), so a job runs once. Use cron and PHP (Section 1.9) for jobs that need files, mail or HTTP.

    3.15.7 Views, Algorithms, and Updatable Views

    A view is a stored SELECT queried like a table; its columns are fixed at creation, its rows computed on each use. ALGORITHM = MERGE folds the view into your query, so base-table indexes still apply; TEMPTABLE materializes it first; UNDEFINED, the default, prefers MERGE. A view is updatable when each row maps to one base-table row, which aggregates, DISTINCT, GROUP BY, HAVING, UNION and TEMPTABLE rule out:

    CREATE VIEW in_stock AS SELECT id, sku, title, price, stock FROM products WHERE stock > 0

    WITH CHECK OPTION;

    CREATE VIEW order_summary AS

    SELECT o.id, c.name, o.status, SUM(i.quantity * i.unit_price) AS total

    FROM orders AS o JOIN customers AS c ON c.id = o.customer_id

    JOIN order_items AS i ON i.order_id = o.id GROUP BY o.id;

    UPDATE in_stock SET price = 36.00 WHERE sku = 'BK-UX-01';

    UPDATE in_stock SET stock = 0 WHERE sku = 'BK-UX-01';

    UPDATE order_summary SET status = 'paid' WHERE id = 8;

    Listing 3.137: An updatable view with CHECK OPTION and an aggregate view

    ERROR 1369 (HY000): CHECK OPTION failed 'shop.in_stock'

    ERROR 1288 (HY000): The target table order_summary of the UPDATE is not updatable

    Output of Listing 3.137 The price change reached products, and information_schema.VIEWS lists in_stock as updatable. WITH CHECK OPTION refused a change that would push the row out of the view; CASCADED, the default, also enforces the views beneath it. CREATE MATERIALIZED VIEW is accepted but, outside HeatWave, makes an ordinary view (Section 3.10.9).By default a view runs with its DEFINER's privileges, so it can be granted as a filter. In a test, an account with only SELECT on customer_directory (id, name, country from customers) read its rows, while the same view declared SQL SECURITY INVOKER checked the caller's rights: ERROR 1356 (HY000): View 'shop.customer_directory_inv' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them.

    3.15.8 JavaScript Stored Programs

    Warning: JavaScript stored programs are a MySQL Enterprise Edition feature. They need the Multilingual Engine component (INSTALL COMPONENT 'file://component_mle'), which Community Edition does not ship, so the server this book installs can store JavaScript routines but not run them. Since 9.0, LANGUAGE JAVASCRIPT lets a routine body be JavaScript (ECMAScript 2024 in 9.7) between $$ quotes, with session.sql() for queries; 9.2 added CREATE LIBRARY and a transaction API, 9.4 WebAssembly libraries. On Community 9.7.2 the definition is stored, and only the call fails:

    CREATE FUNCTION gcd(a INT, b INT) RETURNS INT NO SQL LANGUAGE JAVASCRIPT AS $$

    let x = Math.abs(a), y = Math.abs(b);

    while (y) { [x, y] = [y, x % y]; }

    return x;

    $$;

    SELECT gcd(75, 220);

    Listing 3.138: A JavaScript function on a Community server ERROR 6001 (HY000): Language component: Not available. Output of Listing 3.138 The client needs no DELIMITER, since it reads $$ ... $$ as one quoted string. JavaScript suits parsing and validation code already written in it; on a LAMP stack, that code belongs in PHP.

    3.15.9 When to Use Routines and When to Keep Logic in PHP

    Put a rule in the database when it must hold for every writer (a batch job, a second application, the mysql prompt): constraints first, then triggers for audit trails, definer views and procedures as a narrow interface for a restricted account, and events for housekeeping. Keep fast-changing business logic in PHP, with its tests, debugger and code review. Routines are versioned only if migrations create them (Section 5.9), port poorly to MariaDB or PostgreSQL, and burn CPU on the hardest server to scale. Summing ROUND(p * 1.08, 2) over a million rows took about 0.5 s here, and through add_vat() about 3 s. place_order saves round trips and guards stock, so it earns its place; a per-row function in a report does not.