When to Use Server-Side Logic

Stored procedures and triggers run inside MySQL — reducing network round trips and centralizing logic. However, they are harder to version-control, test, and debug than application code.

Use when: batch ETL, consistent audit logging, legacy systems requiring DB-side logic
Avoid when: complex business rules, rapid iteration, microservices with independent deploy cycles

Stored Procedures 

  DELIMITER //
CREATE PROCEDURE get_user_orders(
    IN p_user_id INT,
    IN p_limit INT
)
BEGIN
    SELECT o.id, o.total, o.status, o.created_at
    FROM orders o
    WHERE o.user_id = p_user_id
    ORDER BY o.created_at DESC
    LIMIT p_limit;
END //
DELIMITER ;

CALL get_user_orders(42, 10);

IN, OUT, and INOUT Parameters 

  DELIMITER //
CREATE PROCEDURE count_by_status(
    IN p_status VARCHAR(20),
    OUT p_count INT
)
BEGIN
    SELECT COUNT(*) INTO p_count FROM orders WHERE status = p_status;
END //
DELIMITER ;

CALL count_by_status('pending', @cnt);
SELECT @cnt;

Stored Functions 

  DELIMITER //
CREATE FUNCTION order_item_count(p_order_id INT) RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE cnt INT;
    SELECT COUNT(*) INTO cnt FROM order_items WHERE order_id = p_order_id;
    RETURN cnt;
END //
DELIMITER ;

SELECT id, order_item_count(id) AS items FROM orders LIMIT 10;

Functions return a single value and cannot modify data (in strict SQL mode).

Error Handling 

  DELIMITER //
CREATE PROCEDURE safe_transfer(
    IN p_from INT,
    IN p_to INT,
    IN p_amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE accounts SET balance = balance - p_amount
    WHERE id = p_from AND balance >= p_amount;

    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds or invalid account';
    END IF;

    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
    COMMIT;
END //
DELIMITER ;

Triggers

Triggers fire automatically on INSERT, UPDATE, or DELETE.

AFTER INSERT — Audit Trail 

  DELIMITER //
CREATE TRIGGER trg_orders_audit_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, action, new_total, changed_at)
    VALUES (NEW.id, 'INSERT', NEW.total, NOW());
END //
DELIMITER ;

BEFORE UPDATE — Validation 

  DELIMITER //
CREATE TRIGGER trg_orders_validate_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    IF NEW.total < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Total cannot be negative';
    END IF;
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

NEW = new row values; OLD = previous row values (UPDATE/DELETE only).

Trigger Limitations

  • One trigger per event per table (MySQL 8.0 allows multiple)
  • Cannot call stored procedures that modify the same table (recursion risk)
  • Hidden side effects complicate debugging
  • Performance overhead on high-write tables

Cursors (Procedural Loops) 

  DELIMITER //
CREATE PROCEDURE apply_bulk_discount(IN p_pct DECIMAL(5,2))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM products WHERE category = 'clearance';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    START TRANSACTION;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_id;
        IF done THEN LEAVE read_loop; END IF;
        UPDATE products SET price = price * (1 - p_pct/100) WHERE id = v_id;
    END LOOP;
    CLOSE cur;
    COMMIT;
END //
DELIMITER ;

Prefer set-based SQL over cursors when possible — cursors are row-by-row and slow.

Managing Procedures and Triggers 

  SHOW PROCEDURE STATUS WHERE Db = 'myapp';
SHOW CREATE PROCEDURE get_user_orders\G

SHOW TRIGGERS FROM myapp;
DROP TRIGGER IF EXISTS trg_orders_audit_insert;
DROP PROCEDURE IF EXISTS get_user_orders;

Store definitions in migration files:

  -- migrations/V004__order_audit_trigger.sql
DROP TRIGGER IF EXISTS trg_orders_audit_insert;
-- CREATE TRIGGER ...

Best Practices

  1. Keep procedures focused — one responsibility per procedure
  2. Always include error handlers with ROLLBACK
  3. Use SIGNAL for business rule violations
  4. Document parameters and side effects
  5. Test triggers with integration tests against a real MySQL instance

Common Mistakes

Mistake Consequence
Business logic only in triggers Hidden failures, ORM bypass issues
No error handler in procedure Partial commits, unclear failures
Cursor for bulk updates Minutes instead of seconds
Trigger modifies same table MySQL error or infinite recursion

Troubleshooting

“Trigger already exists” — use DROP TRIGGER IF EXISTS in migrations.

“Can’t update table in stored function/trigger” — restructure to audit a different table or use AFTER trigger with INSERT to log table.

Binary logging blocks creation:

  SET GLOBAL log_bin_trust_function_creators = 1;
-- Or create as SUPER user in dev; use DEFINER carefully in production

Production Scenario

A compliance team requires immutable audit logs for financial tables. AFTER INSERT/UPDATE/DELETE triggers write to audit_log with OLD and NEW values as JSON. The audit table is append-only (no UPDATE/DELETE grants for application users). Quarterly, audit data is exported to cold storage.

Procedures and triggers are powerful — deploy them deliberately, document them thoroughly, and prefer application logic when team velocity matters more than round-trip latency.