Triggers in MySQL

# CHAPTER 22

Triggers in MySQL #

1. Introduction #

2. Learning Objectives #

• Define what a MySQL Trigger is.

• Differentiate between BEFORE and AFTER triggers.

• Understand the NEW and OLD keywords.

• Create an automated Audit Logging system.

• Understand the hidden performance dangers of Triggers.

3. What is a Trigger? #

• INSERT

• UPDATE

• DELETE

You can configure the trigger to fire BEFORE the event (allowing you to intercept and modify the incoming data) or AFTER the event (allowing you to react to the finalized data).

4. The NEW and OLD Keywords #

• OLD: Contains the data as it existed *before* the update.

• NEW: Contains the incoming data that is trying to be saved.

5. Using a BEFORE Trigger (Data Scrubbing) #

DELIMITER //

-- Fire this logic right BEFORE an insert hits the users table
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    -- We intercept the incoming NEW email and permanently lowercase it!
    SET NEW.email = LOWER(NEW.email);
END //

DELIMITER ;

*Now, even if a user bypasses the website and types raw SQL to insert an uppercase email, the database intercepts it and fixes it automatically!*

6. Mini Project: The Audit Log (AFTER Trigger) #

First, create a blank table to hold the history:

CREATE TABLE salary_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Now, create the automated tripwire!

DELIMITER //

-- Fire this logic AFTER a successful update occurs on the employees table
CREATE TRIGGER log_salary_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Only log it if the salary actually changed!
    IF OLD.salary != NEW.salary THEN
        INSERT INTO salary_audit_log (employee_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;
END //

DELIMITER ;

*Magic! The PHP application runs a standard UPDATE employees SET salary.... It has no idea the audit table even exists. But MySQL silently and flawlessly logs the historical change behind the scenes.*

7. Common Mistakes #

• The Infinite Loop: If you create an AFTER UPDATE trigger on the users table, and the code inside the trigger runs an UPDATE on the users table, the trigger will fire itself. Which will fire itself again. MySQL will crash. Never update the table that fired the trigger.

• Hidden Logic: Triggers are invisible. If a new junior developer looks at the PHP code, they won't understand why emails are suddenly becoming lowercase, because the logic is hidden deep inside the database engine. Overusing triggers makes applications extremely difficult to debug.

8. Best Practices #

• Use exclusively for Auditing and Strict Data Integrity: Triggers should only be used for cross-table data validation and logging. Never use a Trigger to implement standard business logic (like sending an email or generating a PDF). That belongs in the backend application code!

9. Exercises #

1. 1. What are the three specific DML statements that can cause a MySQL Trigger to fire?

1. 2. If you are writing a BEFORE DELETE trigger, do you have access to the NEW keyword? Why or why not?

10. MCQ Quiz with Answers #

11. Interview Questions #

• Q: Contrast a Stored Procedure with a Trigger. How are they executed differently?

• Q: Explain the potential dangers of utilizing Triggers heavily in a large-scale web application, particularly concerning debugging and the "Infinite Loop" scenario.

12. FAQs #

13. Summary #

14. Next Chapter Recommendation #