Stored Procedures & Triggers

So far every query has travelled from your program to MySQL and back. MySQL can also store code on the server under a name. A stored procedure is a named block of SQL that you run with CALL. A stored function returns one value and can be used inside a SELECT. A trigger runs by itself whenever a row is inserted, updated or deleted in a particular table.

In this lesson you will write all three for the practice database, and learn when logic belongs in the database and when it does not.

Your first procedure and DELIMITER

A procedure body sits between BEGIN and END, and every statement inside it ends with a semicolon. That causes a problem in the mysql command-line client: it sends a statement to the server as soon as it sees a ;, so it would send half a procedure. The client command DELIMITER changes the end marker for a while:

DELIMITER //

CREATE PROCEDURE low_stock_products()
BEGIN
  SELECT product_id, product_name, stock
  FROM products
  WHERE stock < 20
  ORDER BY stock;
END //

DELIMITER ;

CALL low_stock_products();

product_id | product_name    | stock
-----------+-----------------+------
8          | HD Webcam       | 0
12         | Graphics Tablet | 12
3          | 27-inch Monitor | 18
(3 rows)

DELIMITER // tells the client "a statement now ends at //", so the whole CREATE PROCEDURE reaches the server in one piece, and DELIMITER ; switches back. The procedure is now saved in the database, and anyone with permission can run it from any program.

DELIMITER belongs to the client, not to SQL. phpMyAdmin has a "Delimiter" box under its SQL editor, and from PHP you send CREATE PROCEDURE ... as one statement with no DELIMITER at all.

Parameters: IN and OUT

Procedures take parameters. An IN parameter carries a value into the procedure (this is the default). An OUT parameter carries a value back to the caller, who passes a user variable, a name starting with @ that lives until the connection closes. A p_ prefix keeps parameter names apart from column names.

DELIMITER //

CREATE PROCEDURE city_orders(IN p_city VARCHAR(40))
BEGIN
  SELECT c.first_name, o.order_id, o.order_date, o.status
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  WHERE c.city = p_city
  ORDER BY o.order_date;
END //

CREATE PROCEDURE order_total(IN p_order_id INT, OUT p_total DECIMAL(10,2))
BEGIN
  SELECT SUM(quantity * unit_price) INTO p_total
  FROM order_items
  WHERE order_id = p_order_id;
END //

DELIMITER ;

CALL city_orders('Rohtak');

CALL order_total(110, @total);
SELECT @total;

first_name | order_id | order_date | status
-----------+----------+------------+----------
Aarav      | 101      | 2025-01-05 | delivered
Aarav      | 104      | 2025-02-02 | delivered
Vikram     | 108      | 2025-03-15 | shipped
Aarav      | 115      | 2025-05-20 | pending
(4 rows)

@total
--------
32797.00
(1 row)

SELECT ... INTO p_total stores the result in the parameter instead of returning rows; for an order that does not exist, @total becomes NULL. An INOUT parameter does both jobs: it brings a value in and sends a changed value back.

From PHP, prepare 'CALL city_orders(?)', execute it with ['Rohtak'] and fetch the rows like any query (see CRUD with Prepared Statements).

Variables and IF

Inside a procedure, DECLARE creates a local variable that exists only while the procedure runs. DECLARE lines must come first, right after BEGIN. SET changes a variable, and IF ... ELSEIF ... ELSE ... END IF chooses what to do. This procedure gives a customer a loyalty level based on what they have spent:

DELIMITER //

CREATE PROCEDURE customer_level(IN p_customer_id INT, OUT p_level VARCHAR(10))
BEGIN
  DECLARE v_spent DECIMAL(10,2) DEFAULT 0;

  SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0) INTO v_spent
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.customer_id = p_customer_id
    AND o.status <> 'cancelled';

  IF v_spent >= 20000 THEN
    SET p_level = 'Gold';
  ELSEIF v_spent >= 5000 THEN
    SET p_level = 'Silver';
  ELSE
    SET p_level = 'Bronze';
  END IF;
END //

DELIMITER ;

CALL customer_level(8, @sneha);
CALL customer_level(2, @priya);
CALL customer_level(4, @ananya);
SELECT @sneha, @priya, @ananya;

@sneha | @priya | @ananya
-------+--------+--------
Gold   | Silver | Bronze
(1 row)

Sneha has spent ₹32,797 and Priya ₹16,899; Ananya's only order was cancelled. The v_ prefix matters: if a variable had the same name as a column, MySQL would use the variable, which causes confusing bugs.

Stored functions

A procedure is run with CALL. A stored function returns a single value with RETURN, so you can use it inside any query, just like ROUND() or UPPER(). When you create a function, describe what it does:

By default, MySQL refuses to create a function that declares none of DETERMINISTIC, NO SQL or READS SQL DATA, because such functions are unsafe for replication and recovery from the binary log. MySQL does not check your claims, so be honest:

DELIMITER //

CREATE FUNCTION with_gst(p_price DECIMAL(10,2), p_rate INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
  RETURN ROUND(p_price * (100 + p_rate) / 100, 2);
END //

CREATE FUNCTION customer_spent(p_customer_id INT)
RETURNS DECIMAL(10,2)
NOT DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN (
    SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.customer_id = p_customer_id
      AND o.status <> 'cancelled'
  );
END //

DELIMITER ;

SELECT product_name, price, with_gst(price, 18) AS price_with_gst
FROM products
WHERE category = 'Audio';

SELECT first_name, city, customer_spent(customer_id) AS spent
FROM customers
WHERE state = 'Haryana'
ORDER BY spent DESC;

product_name                | price   | price_with_gst
----------------------------+---------+---------------
Noise-Cancelling Headphones | 8999.00 | 10618.82
Bluetooth Speaker           | 2499.00 | 2948.82
(2 rows)

first_name | city     | spent
-----------+----------+---------
Aarav      | Rohtak   | 14944.00
Isha       | Gurugram | 8695.00
Vikram     | Rohtak   | 4298.00
(3 rows)

with_gst() only does arithmetic, so it is DETERMINISTIC and NO SQL. customer_spent() reads changing tables, so it is NOT DETERMINISTIC and READS SQL DATA, and customer_level could now simply SET v_spent = customer_spent(p_customer_id);. Careful: in a big SELECT, a function that queries tables runs once for every row.

Triggers

A trigger belongs to one table and fires for one kind of change: BEFORE or AFTER an INSERT, UPDATE or DELETE. FOR EACH ROW means it runs once per affected row. Inside, NEW.column is the row being inserted (or the new values in an update) and OLD.column is the row before an update or delete.

The shop needs two rules for order_items. Before a row is inserted, check that the quantity makes sense and that enough stock exists. After it is inserted, take the quantity out of stock:

DELIMITER //

CREATE TRIGGER order_items_check
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
  DECLARE v_stock INT;

  IF NEW.quantity < 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Quantity must be at least 1';
  END IF;

  SELECT stock INTO v_stock
  FROM products
  WHERE product_id = NEW.product_id
  FOR UPDATE;

  IF v_stock < NEW.quantity THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not enough stock for this product';
  END IF;
END //

CREATE TRIGGER order_items_reduce_stock
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
  UPDATE products
  SET stock = stock - NEW.quantity
  WHERE product_id = NEW.product_id;
END //

DELIMITER ;

SIGNAL SQLSTATE '45000' raises your own error. SQLSTATE 45000 means "unhandled user-defined exception", and the message is what the program sees. FOR UPDATE locks the product row until the transaction ends, so two buyers cannot both take the last item (see Transactions).

Now Meera orders two Bluetooth Speakers, and then tries to add an HD Webcam, which has no stock:

INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (116, 6, '2025-06-01', 'pending');

INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (23, 116, 7, 2, 2499.00);

SELECT product_name, stock FROM products WHERE product_id = 7;

INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (24, 116, 8, 1, 2199.00);

product_name      | stock
------------------+------
Bluetooth Speaker | 38
(1 row)

ERROR 1644 (45000): Not enough stock for this product

The stock went from 40 to 38 without any UPDATE in the application code, and the webcam row was refused. When a trigger raises an error, MySQL cancels the whole statement, including any changes earlier triggers made for it. If one INSERT adds three items and the third fails, none of them is saved and no stock changes.

Listing and removing stored programs

These statements show what is stored in the shop database:

SHOW PROCEDURE STATUS WHERE Db = 'shop';
SHOW FUNCTION STATUS WHERE Db = 'shop';
SHOW CREATE PROCEDURE customer_level;
SHOW TRIGGERS FROM shop;

The SHOW statements print many columns. For a short list, query information_schema, the built-in database that describes all the others:

SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'shop'
ORDER BY ROUTINE_TYPE, ROUTINE_NAME;

ROUTINE_NAME       | ROUTINE_TYPE
-------------------+-------------
customer_spent     | FUNCTION
with_gst           | FUNCTION
city_orders        | PROCEDURE
customer_level     | PROCEDURE
low_stock_products | PROCEDURE
order_total        | PROCEDURE
(6 rows)

DROP removes a stored program. IF EXISTS avoids an error when it is already gone:

DROP PROCEDURE IF EXISTS low_stock_products;
DROP FUNCTION IF EXISTS with_gst;
DROP TRIGGER IF EXISTS order_items_check;

To change a procedure or function body in MySQL, drop it and create it again. MariaDB, which XAMPP installs, also accepts CREATE OR REPLACE PROCEDURE, but MySQL does not.

Logic in the database or in the app?

Stored programs are useful, but they are not the default place for business logic. Weigh both sides:

A good rule: keep business logic in your application, and use the database to protect the data itself. Prefer constraints first (NOT NULL, foreign keys, CHECK (quantity > 0) from Constraints & Keys), and use a trigger only for rules a constraint cannot express, such as keeping stock in step with sales.

Common mistakes

• Forgetting DELIMITER in the mysql client. The client sends the procedure at the first ; and reports a syntax error.
• Leaving the delimiter as //. Switch back with DELIMITER ;, or your next query waits for //.
• A variable named like a column. WHERE stock = stock compares the column with itself; use p_ and v_ prefixes.
• Declaring a function DETERMINISTIC when it reads changing data. MySQL trusts you, and replication can go wrong.
• Hiding important logic in triggers. Nobody sees them in the application code; keep them small, few and documented.

Key points

• Procedures are saved on the server and run with CALL; IN parameters bring values in and OUT parameters send them back to @variables.
• In the mysql client, wrap the body with DELIMITER // ... DELIMITER ;; other tools and PHP do not need it.
• DECLARE, SET and IF ... ELSEIF ... END IF give procedures local variables and decisions.
• Stored functions RETURN one value for use in queries and must be declared DETERMINISTIC, NO SQL or READS SQL DATA.
• BEFORE triggers validate with SIGNAL; AFTER triggers keep related tables in step; NEW and OLD hold the row values.
• Keep business logic mostly in the application and use stored programs for data integrity and heavy data work.

DELIMITER //

CREATE PROCEDURE cancel_order(IN p_order_id INT)
BEGIN
  DECLARE v_status VARCHAR(20);

  SET v_status = (SELECT status FROM orders WHERE order_id = p_order_id);

  IF v_status IS NULL OR v_status <> 'pending' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Only pending orders can be cancelled';
  END IF;

  UPDATE products p
  JOIN (
    SELECT product_id, SUM(quantity) AS qty
    FROM order_items
    WHERE order_id = p_order_id
    GROUP BY product_id
  ) AS returned ON returned.product_id = p.product_id
  SET p.stock = p.stock + returned.qty;

  UPDATE orders SET status = 'cancelled' WHERE order_id = p_order_id;
END //

DELIMITER ;

CALL cancel_order(115);
SELECT product_id, product_name, stock FROM products WHERE product_id IN (5, 7);
SELECT status FROM orders WHERE order_id = 115;

CALL cancel_order(101);

product_id | product_name      | stock
-----------+-------------------+------
5          | Laptop Stand      | 76
7          | Bluetooth Speaker | 41
(2 rows)

status
---------
cancelled
(1 row)

ERROR 1644 (45000): Only pending orders can be cancelled