1️⃣3️⃣ MySQL Triggers & Stored Programs – Automate & Encapsulate Logic

MySQL allows you to encapsulate logic within the database using Triggers, Stored Procedures, and Stored Functions, minimizing repetitive code, enhancing consistency, and improving data integrity.

Introduction – Why Use Triggers and Stored Programs?

Triggers and stored programs allow developers to move business logic into the database, automating actions like logging, validation, or batch processing. This leads to:

Reduced application complexity
Better data integrity enforcement
Improved performance and maintainability

In this guide, you’ll learn:

• What triggers, procedures, and functions are
• How to write and manage them in MySQL
• Real-world usage scenarios
• Best practices for optimal implementation

Topics Covered

🔔 1. MySQL Triggers – Respond to Table Events

What is a Trigger?

A trigger runs SQL code automatically in response to data changes.

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

Example – Audit Log

CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_audit(user_id, old_email, new_email, updated_at)
  VALUES (OLD.id, OLD.email, NEW.email, NOW());
END;

Used for logging, validation, or historical tracking.

2. MySQL Stored Procedures – Encapsulate SQL Logic

What is a Stored Procedure?

A stored procedure is a named set of SQL statements stored in the DB and executed via CALL.

DELIMITER //

CREATE PROCEDURE add_customer(
  IN name VARCHAR(100),
  IN email VARCHAR(100)
)
BEGIN
  INSERT INTO customers(name, email) VALUES (name, email);
END //

DELIMITER ;

Call Procedure

CALL add_customer('Alice', 'alice@example.com');

Great for form actions, business rules, and batch jobs.

3. MySQL Stored Functions – Return a Value

What is a Stored Function?

A stored function returns a result and can be used in expressions.

DELIMITER //

CREATE FUNCTION get_discount(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN price * 0.90;
END //

DELIMITER ;

Use in Query

SELECT product_name, get_discount(price) AS discounted_price
FROM products;

Use functions inside SELECT, WHERE, or ORDER BY clauses.

4. Manage & View Stored Programs

Triggers vs Stored Programs

Real-World Use Cases

Best Practices

Summary – Recap & Next Steps

Triggers, procedures, and functions empower MySQL to automate and encapsulate logic, improve code reuse, and support scalable architecture across your applications.

Key Takeaways

• Triggers run automatically on table events
• Stored procedures encapsulate logic with parameters
• Functions return values and are query-friendly
• Use these tools to move business logic closer to the data

Real-World Relevance
Found in audit systems, ERP workflows, e-commerce logic, data validations, and performance-heavy operations where central DB logic is preferred.

FAQ – MySQL Triggers & Stored Programs

Can a stored procedure return a value?

Not directly, but you can use OUT parameters or SELECT statements inside.

Can I create multiple triggers on one table?

Yes, but only one trigger per timing/event type. (e.g., one BEFORE INSERT, one AFTER INSERT)

Can I use stored functions inside WHERE clauses?

Absolutely. They behave like built-in SQL functions:

SELECT * FROM orders WHERE get_discount(total) > 100;

Can a trigger update the same table?

Yes, but use caution. MySQL disables recursive triggers by default.

Are stored routines faster than app logic?

For repeated DB logic, yes—they reduce round trips and centralize business rules.