BEFORE INSERT triggers execute before data reaches the table.
They modify NEW row values or validate data using SIGNAL.
Perfect for auto-timestamps, UUID generation, and input sanitization.
Basic BEFORE INSERT Syntax
NEW Keyword in BEFORE INSERT
NEW represents incoming row values (modifiable).
OLD unavailable (no previous row exists).
Auto-Timestamp Example
UUID Primary Key Generation
Data Validation with SIGNAL
Email Format Validation
Default Status Setting
Multiple Validations Combined
Business Rule Enforcement
Prevent Duplicate Emails
Currency Conversion
Data Sanitization
NEW Column Availability
| BEFORE INSERT | NEW | OLD |
| All columns | ✓ | ✗ |
| Read/Write | ✓ | N/A |
Error Handling SQLSTATE Codes
| Code | Purpose |
| 45000 | User-defined error |
| 22003 | Numeric overflow |
| 22007 | Invalid datetime |
Permissions Required
Testing BEFORE INSERT Trigger
Performance Impact
- Executes once per inserted row
- 1000 rows = 1000 trigger executions
- Keep logic simple for bulk operations
Common Use Cases
- Auto-generated timestamps
- UUID primary keys
- Input validation/sanitization
- Default value assignment
- Business rule enforcement
Important Notes
- NEW values fully modifiable
- SIGNAL aborts INSERT immediately
- Multiple BEFORE INSERT triggers execute in alphabetical order
- Cannot modify triggering table
Production Example
Complete student onboarding trigger:
Key Points
- BEFORE INSERT modifies data before storage
- NEW keyword accesses/modifies incoming values
- SIGNAL prevents invalid data insertion
- Perfect for validation and auto-values
- Executes per row, scales with INSERT size