In this lesson, you will learn how to use database triggers to automatically execute background tasks and enforce data validation whenever a change occurs in your tables.
The "Silent Observer" Concept
Up until now, you have used Stored Routines to perform tasks when you specifically call them. A trigger is different: it is a special type of database object that sits silently in the background, watching for specific events (like INSERT, UPDATE, or DELETE) to happen on a table. When that event occurs, the database automatically "triggers" the associated code.
Think of a trigger like a security alarm system. You don't have to call the police every time a door opens; the alarm system detects the action and acts immediately based on its pre-programmed instructions.
Why Use Triggers?
Triggers are incredibly powerful for two main scenarios:
- Automated Validation: You can prevent "bad" data from being saved by checking conditions before a write operation is finalized.
- Maintaining Consistency: If you update a balance in one table, you might need to automatically sync that data in another table or insert a record into an audit log.
A Concrete Example
Imagine you have an Employees table and you want to ensure that every time an employee’s salary is updated, you keep a record of the change. Instead of relying on your application code to remember this, you can create a trigger:
CREATE TRIGGER log_salary_change
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
INSERT INTO Salary_Audit (employee_id, old_salary, new_salary, change_date)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END;
In this code, OLD refers to the data before the update, and NEW refers to the data being saved. The database handles this entire process automatically in the background, ensuring your audit trail is never missed, even if someone updates the database through a different tool or script.
Triggers help you enforce business logic at the database level, ensuring that rules are followed regardless of how or where an update is triggered.
Key Takeaway: A trigger is a self-executing database object that enforces validation or performs background synchronization automatically whenever a specified data modification event occurs.