What Are Triggers in MySQL? A Complete Guide

When working with relational databases like MySQL, there are times when you want certain actions to happen automatically in response to specific events on a table. That’s where triggers come into play.

A trigger is a special type of stored program in MySQL that is automatically executed (or “fired”) when certain events occur in a table, such as an INSERT, UPDATE, or DELETE operation.

In simple words:
👉 A trigger is like a hidden database guardian that ensures actions are taken behind the scenes without needing explicit calls from the application.

✅ Why Use Triggers in MySQL?

Triggers are powerful tools that can:

1. Maintain Data Integrity
   • Automatically validate or adjust data before it gets inserted or updated.
   • Example: Prevent inserting negative stock values.
2. Enforce Business Rules
   • Ensure company policies or rules are applied consistently at the database level.
   • Example: Prevent deleting a customer who has pending orders.
3. Automate Audit Trails / Logging
   • Keep track of changes made to important tables by recording them in a log table.
   • Example: Store “before” and “after” values when salary is updated.
4. Synchronize Tables
   • Keep multiple tables in sync automatically.
   • Example: Update account balances in one table when a transaction happens in another.
5. Prevent Invalid Transactions
   • Stop unwanted or dangerous modifications before they affect the database.
   • Example: Prevent inserting duplicate email addresses into a users table.

🔑 Types of Triggers in MySQL

MySQL supports six types of triggers, based on the event (INSERT, UPDATE, DELETE) and the timing (BEFORE, AFTER):

1. BEFORE INSERT → Fires before a new row is inserted.
2. AFTER INSERT → Fires after a new row is inserted.
3. BEFORE UPDATE → Fires before a row is updated.
4. AFTER UPDATE → Fires after a row is updated.
5. BEFORE DELETE → Fires before a row is deleted.
6. AFTER DELETE → Fires after a row is deleted.

📝 Basic Syntax of a Trigger

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

Example: Logging User Updates

Suppose we want to keep track of salary changes in an employees table:

CREATE TABLE salary_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_log (emp_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;
END;
//
DELIMITER ;

👉 Now, whenever an employee’s salary changes, the old and new values are automatically logged.

⚡ Advantages of Using Triggers

• ✅ Automatic execution without modifying application code.
• ✅ Enforce rules consistently at the database level.
• ✅ Great for auditing and logging sensitive data changes.
• ✅ Help maintain referential integrity across tables.

⚠️ Things to Watch Out For

While triggers are powerful, they must be used carefully:

1. Hidden Complexity
   • Since triggers run in the background, debugging issues can be tricky.
2. Performance Impact
   • Poorly written triggers can slow down INSERT/UPDATE/DELETE operations.
3. Limited Control
   • Triggers apply at the row level, not at the transaction level.
4. Not Portable
   • Triggers are specific to the database system; migrating to another RDBMS may require rewriting them.

🚀 Real-Life Use Cases of Triggers

1. Audit Logs → Tracking changes to sensitive columns like salary, email, or passwords.
2. Cascade Actions → Automatically deleting related records when a parent record is removed.
3. Validation → Preventing invalid entries (e.g., negative prices, duplicate usernames).
4. Synchronizing Data → Keeping backup tables or summary tables updated automatically.
5. Notifications → Triggering stored procedures or alerts when specific conditions are met.

🔍 Conclusion

Triggers in MySQL are like automatic background workers that keep your data accurate, enforce rules, and log important changes without manual intervention.

They’re best used for data integrity, automation, and auditing, but should be implemented carefully to avoid performance bottlenecks or hidden complexity.

👉 In short:

• Use triggers for data-level rules and automation.
• Avoid using them for business logic that belongs in application code.