Stored Procedures, Functions and Triggers

Databases can run code too. A stored procedure is a named block of SQL you call with parameters; a function returns a value you can use inside queries; a trigger runs automatically when rows change. They're common in enterprise systems, banking and reporting – and frequently asked about in interviews.

DELIMITER: a client detail

A procedure body contains semicolons, but the mysql client normally ends a statement at the first ;. DELIMITER $$ tells the client to use $$ instead while you define the routine. (Workbench and most GUI tools handle this for you.)

A stored procedure

DELIMITER $$
CREATE PROCEDURE customer_orders(IN p_customer_id INT)
BEGIN
  SELECT o.order_id, o.order_date, o.status, SUM(i.quantity * i.unit_price) AS total
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.customer_id = p_customer_id
  GROUP BY o.order_id, o.order_date, o.status
  ORDER BY o.order_date;
END $$
DELIMITER ;

CALL customer_orders(1);

+----------+------------+-----------+--------+
| order_id | order_date | status    | total  |
+----------+------------+-----------+--------+
|      101 | 2025-08-02 | delivered | 839.00 |
|      103 | 2025-08-19 | delivered | 800.00 |
|      109 | 2025-10-21 | shipped   | 540.00 |
+----------+------------+-----------+--------+

OUT parameters, variables and IF

DELIMITER $$
CREATE PROCEDURE restock(IN p_product_id INT, IN p_qty INT, OUT p_new_stock INT, OUT p_message VARCHAR(60))
BEGIN
  DECLARE v_exists INT;
  SELECT COUNT(*) INTO v_exists FROM products WHERE product_id = p_product_id;

  IF v_exists = 0 THEN
    SET p_new_stock = NULL, p_message = 'No such product';
  ELSEIF p_qty <= 0 THEN
    SET p_new_stock = NULL, p_message = 'Quantity must be positive';
  ELSE
    UPDATE products SET stock = stock + p_qty WHERE product_id = p_product_id;
    SELECT stock INTO p_new_stock FROM products WHERE product_id = p_product_id;
    SET p_message = 'Restocked';
  END IF;
END $$
DELIMITER ;

CALL restock(3, 20, @stock, @msg);
SELECT @stock AS new_stock, @msg AS message;
CALL restock(99, 5, @stock, @msg);
SELECT @stock AS new_stock, @msg AS message;

+-----------+-----------+
| new_stock | message   |
+-----------+-----------+
|        20 | Restocked |
+-----------+-----------+
+-----------+-----------------+
| new_stock | message         |
+-----------+-----------------+
|      NULL | No such product |
+-----------+-----------------+

@stock and @msg are session variables that receive the OUT values. Inside the procedure, DECLARE creates local variables and SELECT … INTO fills them.

User-defined functions

A function returns one value and can be used anywhere an expression can:

DELIMITER $$
CREATE FUNCTION price_with_gst(p_price DECIMAL(10,2), p_rate DECIMAL(4,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN ROUND(p_price * (1 + p_rate / 100), 2);
END $$
DELIMITER ;

SELECT name, price, price_with_gst(price, 18) AS with_gst
FROM products
WHERE category_id = 1
ORDER BY price;

+---------------------+---------+----------+
| name                | price   | with_gst |
+---------------------+---------+----------+
| Wireless Mouse      |  599.00 |   706.82 |
| USB-C Hub           | 1299.00 |  1532.82 |
| Laptop Stand        | 1499.00 |  1768.82 |
| Mechanical Keyboard | 2499.00 |  2948.82 |
+---------------------+---------+----------+

DETERMINISTIC promises the same inputs always give the same output; MySQL requires such a characteristic when binary logging is on.

Triggers

A trigger runs automatically BEFORE or AFTER an INSERT, UPDATE or DELETE on a table, once per affected row. OLD and NEW refer to the row before and after the change.

CREATE TABLE price_audit (
  product_id INT, old_price DECIMAL(10,2), new_price DECIMAL(10,2), changed_at DATETIME
);

DELIMITER $$
CREATE TRIGGER trg_price_audit
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price <> OLD.price THEN
    INSERT INTO price_audit VALUES (OLD.product_id, OLD.price, NEW.price, '2025-11-07 10:00:00');
  END IF;
END $$
DELIMITER ;

UPDATE products SET price = 649.00 WHERE product_id = 1;
UPDATE products SET stock = stock - 1 WHERE product_id = 2;     -- price unchanged: no audit row
SELECT * FROM price_audit;

+------------+-----------+-----------+---------------------+
| product_id | old_price | new_price | changed_at          |
+------------+-----------+-----------+---------------------+
|          1 |    599.00 |    649.00 | 2025-11-07 10:00:00 |
+------------+-----------+-----------+---------------------+

(A real audit trigger would use NOW(); a fixed time keeps this example's output stable.)

Should logic live in the database?

Practice

Summary