MySQL Triggers
A trigger in MySQL is a set of SQL statements that automatically executes ("fires") in response to a specific event on a particular table. Triggers are useful for enforcing business rules, validating input data, maintaining audit trails, and synchronizing tables.
When to Use Triggers
- Auditing changes (who changed what and when)
- Enforcing complex business rules
- Maintaining derived/calculated columns
- Replicating data across tables
- Implementing soft deletes
- Validating data before insertion/update
Trigger Types and Timing
MySQL supports triggers based on timing and event:
| Timing | Event | Description |
|---|---|---|
| BEFORE | INSERT, UPDATE, DELETE | Executes before the operation |
| AFTER | INSERT, UPDATE, DELETE | Executes after the operation |
Basic Trigger Syntax
Basic Syntax
NEW and OLD Keywords
Inside triggers, you can access:
- NEW: Refers to new row (available in INSERT and UPDATE triggers)
- OLD: Refers to old row (available in UPDATE and DELETE triggers)
BEFORE INSERT Trigger Examples
Data Validation Trigger
Auto-generate Email
Set Default Timestamps
AFTER INSERT Trigger Examples
Audit Log Trigger
Update Summary Table
BEFORE UPDATE Trigger Examples
Prevent Salary Reduction
Update Timestamp
Cascade Update
AFTER UPDATE Trigger Examples
Update Audit Log
BEFORE DELETE Trigger Examples
Soft Delete Implementation
Check Foreign Key Constraints
AFTER DELETE Trigger Examples
Delete Audit Log
Clean Up Related Data
Managing Triggers
View Triggers
Drop Triggers
Complex Trigger Example
Complete Employee Management Triggers
Trigger Limitations and Best Practices
Trigger Limitations:
- Cannot use CALL to stored procedures that return result sets
- Cannot use explicit or implicit COMMIT/ROLLBACK
- No RETURN statements
- Cannot create/drop tables in triggers
- Performance impact on bulk operations
Best Practices:
- Keep triggers simple and fast
- Avoid nested triggers when possible
- Document triggers thoroughly
- Test triggers with edge cases
- Monitor trigger performance
- Use triggers for auditing and validation, not business logic
- Consider alternative approaches (stored procedures, application logic)
Debugging Triggers
Debug Log Table
Real-World Use Cases
1. E-commerce Inventory Management:
2. Banking Transaction Log:
Conclusion
MySQL triggers are powerful tools for maintaining data integrity, implementing business rules, and automating database tasks. While they should be used judiciously due to performance considerations, when applied correctly, triggers can significantly simplify application logic and ensure data consistency across your database.
