Stored Procedures and Triggers in MySQL: Complete Guide

Databases are not just about storing data—they also allow you to automate and encapsulate business logic. Stored procedures and triggers are two powerful features in MySQL that help you achieve this.

1. Stored Procedures

What is a Stored Procedure?

A stored procedure is a set of SQL statements that you can save and reuse. Think of it as a “function” inside your database. Once created, you can call it multiple times with different parameters.

Key Points:

• Stored in the database.
• Accepts input parameters and can return output.
• Helps reduce client-server traffic.
• Centralizes logic in the database.

Benefits of Stored Procedures

1. Reusability: Write once, call many times.
2. Performance: Reduces the amount of SQL sent over the network.
3. Security: Can restrict direct access to tables; users call procedures instead.
4. Maintainability: Changes are centralized; no need to modify application code.

Syntax of Stored Procedures

CREATE PROCEDURE procedure_name (IN param1 INT, OUT param2 VARCHAR(50))
BEGIN
    -- SQL statements
    SELECT * FROM employees WHERE id = param1;
    SET param2 = 'Procedure executed';
END;

//EXAMPLE

DELIMITER $$

CREATE PROCEDURE insert_dummy_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 100 DO
        INSERT INTO students (name, age, email)
        VALUES (CONCAT('Student', i), FLOOR(18 + RAND()*5), CONCAT('student', i, '@example.com'));
        SET i = i + 1;
    END WHILE;
END $$

DELIMITER ;

CALL insert_dummy_data();

//FOR SELECT

SHOW PROCEDURE STATUS WHERE Db = 'study_db';

//FOR DROP

DROP PROCEDURE IF EXISTS insert_dummy_students;

• IN – Input parameter
• OUT – Output parameter
• INOUT – Both input and output

Example: Simple Stored Procedure

DELIMITER $$

CREATE PROCEDURE GetEmployeeByID(IN empID INT)
BEGIN
    SELECT * FROM employees WHERE id = empID;
END$$

DELIMITER ;

-- Call the procedure
CALL GetEmployeeByID(101);

Explanation:
This procedure retrieves employee details based on the id provided.

Advanced Stored Procedure Example

DELIMITER $$

CREATE PROCEDURE IncreaseSalary(IN empID INT, IN increment DECIMAL(10,2))
BEGIN
    UPDATE employees
    SET salary = salary + increment
    WHERE id = empID;
END$$

DELIMITER ;

-- Call the procedure
CALL IncreaseSalary(101, 5000);

Explanation:
This procedure increases the salary of a specific employee by the given amount.

IN, OUT, and INOUT parameters mean in Stored Procedures Read More

2. Triggers

What is a Trigger?

A trigger is a set of actions that automatically executes in response to certain events on a table, such as INSERT, UPDATE, or DELETE.

Key Points:

• Executed automatically; you don’t call it manually.
• Useful for enforcing rules or auditing changes.
• Can execute before or after an operation.

Benefits of Triggers

1. Automation: Automatically perform tasks like logging changes.
2. Data Integrity: Enforce business rules at the database level.
3. Auditing: Track changes to important tables.
4. Consistency: Ensure related changes happen simultaneously.

Syntax of Triggers

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END;

• BEFORE – Trigger runs before the operation.
• AFTER – Trigger runs after the operation.
• FOR EACH ROW – Executes for every row affected.

Example: Simple Trigger

DELIMITER $$

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END$$

DELIMITER ;

Explanation:
Whenever a new employee is added, this trigger automatically sets the created_at timestamp.

Example: Auditing Trigger

DELIMITER $$

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit(emp_id, old_salary, new_salary, changed_at)
    VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END$$

DELIMITER ;

Explanation:
Whenever an employee’s salary is updated, this trigger records the change in an audit table.

3. Differences Between Stored Procedures and Triggers

4. Best Practices

1. Keep Procedures Small: Avoid large monolithic procedures.
2. Use Transactions: Ensure data consistency for complex procedures.
3. Avoid Overusing Triggers: Too many triggers can slow down performance.
4. Document Your Logic: Both procedures and triggers should be clearly documented.
5. Security: Limit who can create or execute stored procedures.

5. Conclusion

Stored procedures and triggers are essential tools for robust, efficient, and automated database management.

• Stored Procedures centralize business logic and reduce client-server load.
• Triggers automate reactions to data changes, ensuring consistency and auditing.

By mastering these, you can make your MySQL applications faster, safer, and smarter.

🔹 Stored Procedure vs Function

🔹 Stored Procedure vs SQL Query

🔹 Stored Procedure vs Trigger

✅ Summary:

• Use Stored Procedure for reusable business logic.
• Use Function for returning computed values inside queries.
• Use SQL Query for one-time execution.
• Use Trigger for automatic actions when data changes.