Automating Tasks with Stored Procedures and Triggers

Stored procedures and triggers in MySQL – how to automate repetitive SQL and react automatically when data changes.

Stored procedures

A stored procedure is a set of SQL statements saved in the database and called whenever you need them. Think of them as functions, but living inside MySQL.

Creating a stored procedure

A simple procedure that adds a customer to the customers table:

DELIMITER //

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

DELIMITER ;

To call it:

CALL AddCustomer('John Doe', 'john@example.com');

Benefits of stored procedures

• Efficiency: complex operations run in a single call, reducing round trips to the server.
• Maintenance: change the logic in one place rather than across multiple applications.
• Security: grant access to a procedure without giving direct access to the underlying tables.

Triggers

Triggers run automatically when a specific event happens – an insert, update, or delete. They fire without anyone calling them explicitly.

Creating a trigger

Suppose you want to log every change to a customer’s email:

DELIMITER //

CREATE TRIGGER AfterEmailUpdate
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    IF OLD.email != NEW.email THEN
        INSERT INTO email_change_log (customer_id, old_email, new_email, change_date)
        VALUES (OLD.id, OLD.email, NEW.email, NOW());
    END IF;
END //

DELIMITER ;

That inserts a row into email_change_log whenever a customer’s email is updated.

What triggers are good for

• Data integrity: enforce rules automatically when data changes.
• Auditing: keep a log of who changed what and when.
• Automating tasks: format data, validate values, or update related tables without application code.

Best practices

Both features are useful, but they add complexity:

1. Keep triggers simple: complex trigger logic is hard to debug. If it gets complicated, consider a stored procedure instead.
2. Watch performance: triggers run on every matching row change. Too many can slow writes noticeably.
3. Test thoroughly: check edge cases, not just the happy path.
4. Document them: future you (or your team) will need to know why a trigger exists and what it does.

Use stored procedures for reusable logic you call deliberately. Use triggers for rules that must always fire when data changes. Do not reach for either unless you have a clear reason.