stored procedures, stored functions, delimiters, call, triggers, and scheduled events in MySQL.
Create reusable procedural SQL on the server.
DELIMITER $$
CREATE PROCEDURE get_user_by_email(IN p_email VARCHAR(255))
BEGIN
SELECT id, email, full_name
FROM users
WHERE email = p_email;
END $$
DELIMITER ;Changing the delimiter is the standard pattern when creating stored procedures in the mysql client.
CALL get_user_by_email('ava@example.com');`CALL` invokes stored procedures defined on the server.
DELIMITER $$
CREATE FUNCTION full_label(p_first VARCHAR(100), p_last VARCHAR(100))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
RETURN CONCAT(p_first, ' ', p_last);
END $$
DELIMITER ;Stored functions can be used inside SELECTs and other expressions.
SHOW PROCEDURE STATUS WHERE Db = 'app_db';Useful for inventorying existing routines during maintenance.
SHOW CREATE PROCEDURE get_user_by_email\GGreat for auditing or exporting routine definitions.
Automate row-level reactions and scheduled tasks.
DELIMITER $$
CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.email = LOWER(NEW.email);
END $$
DELIMITER ;Triggers can enforce lightweight normalization close to the data layer.
SHOW TRIGGERS;Useful when debugging hidden side effects during inserts, updates, or deletes.
CREATE EVENT ev_purge_expired_sessions
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM sessions WHERE expires_at < NOW();The event scheduler can automate recurring maintenance tasks inside the database.
SHOW EVENTS FROM app_db;Helpful when auditing background automation configured at the database layer.
DROP TRIGGER IF EXISTS trg_users_before_insert;Always verify dependencies before removing data-layer automation.