Chapter 18: Stored Procedures, Functions, and Triggers
This is where SQL becomes almost like a real programming language!
Up until now, we’ve been writing queries directly every time we need something. But in real-world applications (especially big systems like e-commerce, banking, or hospital software), you don’t want to send the same complex SQL over and over again from your application code.
That’s where Stored Procedures, Functions, and Triggers come in:
- Stored Procedures → Like saved mini-programs you can call by name
- Functions → Like procedures, but they return a value (can be used inside SELECT)
- Triggers → Automatic code that runs by itself when something happens (INSERT, UPDATE, DELETE)
We’ll use our bookshop database and go very deep with realistic examples.
1. Stored Procedures – CREATE PROCEDURE
A stored procedure is a saved block of SQL code that you can call like a function.
Basic Syntax (MySQL / MariaDB):
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
DELIMITER // CREATE PROCEDURE procedure_name (parameters) BEGIN -- SQL statements END // DELIMITER ; |
Example 1 – Simple procedure: Get all books by an author
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DELIMITER // CREATE PROCEDURE GetBooksByAuthor(IN p_author_id INT) BEGIN SELECT b.title, b.price, b.stock, a.name AS author_name FROM books b INNER JOIN authors a ON b.author_id = a.author_id WHERE b.author_id = p_author_id ORDER BY b.price DESC; END // DELIMITER ; |
How to call it:
SQL
|
0 1 2 3 4 5 6 |
CALL GetBooksByAuthor(1); -- All books by Ruskin Bond (author_id = 1) |
Example 2 – Procedure with multiple parameters + OUT parameter
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
DELIMITER // CREATE PROCEDURE GetAuthorStats( IN p_author_id INT, OUT p_total_books INT, OUT p_total_stock INT, OUT p_avg_price DECIMAL(10,2) ) BEGIN SELECT COUNT(*), SUM(stock), ROUND(AVG(price), 2) INTO p_total_books, p_total_stock, p_avg_price FROM books WHERE author_id = p_author_id; END // DELIMITER ; |
Call it:
SQL
|
0 1 2 3 4 5 6 7 8 |
CALL GetAuthorStats(3, @books, @stock, @avg); -- Chetan Bhagat SELECT @books AS total_books, @stock AS total_stock, @avg AS avg_price; |
Result might be:
| total_books | total_stock | avg_price |
|---|---|---|
| 2 | 215 | 289.50 |
2. Functions – CREATE FUNCTION
A function is similar to a procedure but must return a single value — so you can use it inside SELECT, WHERE, etc.
Syntax:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DELIMITER // CREATE FUNCTION function_name (parameters) RETURNS return_type BEGIN -- logic RETURN value; END // DELIMITER ; |
Example 1 – Function: Calculate discounted price
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DELIMITER // CREATE FUNCTION GetDiscountedPrice( p_book_id INT, p_discount_percent DECIMAL(5,2) ) RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN DECLARE original_price DECIMAL(10,2); SELECT price INTO original_price FROM books WHERE book_id = p_book_id; RETURN ROUND(original_price * (1 - p_discount_percent/100), 2); END // DELIMITER ; |
Use it in a query:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT title, price AS original_price, GetDiscountedPrice(book_id, 15) AS price_after_15_percent_off FROM books LIMIT 5; |
Example 2 – Function: Get book age in years
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DELIMITER // CREATE FUNCTION GetBookAgeInYears(p_book_id INT) RETURNS INT DETERMINISTIC BEGIN DECLARE pub_year INT; SELECT YEAR(published_date) INTO pub_year FROM books WHERE book_id = p_book_id; RETURN YEAR(CURDATE()) - pub_year; END // DELIMITER ; |
Use it:
SQL
|
0 1 2 3 4 5 6 7 |
SELECT title, GetBookAgeInYears(book_id) AS age_in_years FROM books; |
Key difference: Procedures vs Functions
- Procedure: CALL it, can have OUT parameters, no return value in SELECT
- Function: Can be used inside SELECT / WHERE, must RETURN one value
3. Triggers – Automatic Code That Runs on Events
A trigger is SQL code that automatically executes when something happens:
- BEFORE INSERT / UPDATE / DELETE
- AFTER INSERT / UPDATE / DELETE
Common uses:
- Automatically update last_updated timestamp
- Prevent invalid data (e.g., negative stock)
- Log changes (audit trail)
- Auto-calculate totals
Syntax:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name FOR EACH ROW BEGIN -- code using NEW. and OLD. to access values END; |
Example 1 – BEFORE INSERT: Prevent negative stock
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DELIMITER // CREATE TRIGGER before_insert_books BEFORE INSERT ON books FOR EACH ROW BEGIN IF NEW.stock < 0 THEN SET NEW.stock = 0; END IF; END // DELIMITER ; |
Example 2 – AFTER UPDATE: Update last_updated automatically
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DELIMITER // CREATE TRIGGER after_update_books AFTER UPDATE ON books FOR EACH ROW BEGIN UPDATE books SET last_updated = CURRENT_TIMESTAMP WHERE book_id = NEW.book_id; END // DELIMITER ; |
Better version (BEFORE UPDATE – more efficient):
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DELIMITER // CREATE TRIGGER before_update_books BEFORE UPDATE ON books FOR EACH ROW BEGIN SET NEW.last_updated = CURRENT_TIMESTAMP; END // DELIMITER ; |
Example 3 – AFTER INSERT: Log new book addition
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- First create a log table CREATE TABLE book_audit_log ( log_id INT AUTO_INCREMENT PRIMARY KEY, book_id INT, action VARCHAR(50), changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); DELIMITER // CREATE TRIGGER after_insert_book_log AFTER INSERT ON books FOR EACH ROW BEGIN INSERT INTO book_audit_log (book_id, action) VALUES (NEW.book_id, 'NEW BOOK ADDED'); END // DELIMITER ; |
Example 4 – BEFORE DELETE: Prevent deletion if stock > 0
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DELIMITER // CREATE TRIGGER before_delete_books BEFORE DELETE ON books FOR EACH ROW BEGIN IF OLD.stock > 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete book with positive stock!'; END IF; END // DELIMITER ; |
Full Realistic Example – Putting It All Together
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
-- 1. Procedure to add a new book safely DELIMITER // CREATE PROCEDURE AddNewBook( IN p_title VARCHAR(255), IN p_author_id INT, IN p_price DECIMAL(10,2), IN p_stock INT ) BEGIN INSERT INTO books (title, author_id, price, stock, is_available) VALUES (p_title, p_author_id, p_price, p_stock, 1); SELECT LAST_INSERT_ID() AS new_book_id; END // DELIMITER ; -- 2. Call it CALL AddNewBook('New Bestseller', 3, 399.00, 200); -- 3. Function to get total value of stock for an author DELIMITER // CREATE FUNCTION GetAuthorStockValue(p_author_id INT) RETURNS DECIMAL(12,2) DETERMINISTIC BEGIN DECLARE total_value DECIMAL(12,2); SELECT SUM(price * stock) INTO total_value FROM books WHERE author_id = p_author_id; RETURN COALESCE(total_value, 0); END // DELIMITER ; -- Use it SELECT GetAuthorStockValue(1) AS total_stock_value_for_ruskin; -- 4. Trigger to auto-set is_available based on stock DELIMITER // CREATE TRIGGER before_update_stock_check BEFORE UPDATE ON books FOR EACH ROW BEGIN IF NEW.stock <= 0 THEN SET NEW.is_available = 0; ELSE SET NEW.is_available = 1; END IF; END // DELIMITER ; |
