Triggers in MySQL

When database integrity requires automatic actions on data changes, triggers provide the solution.
MySQL triggers automatically execute in response to INSERT, UPDATE, or DELETE events on tables.
They are widely used for:

• Audit trails and logging
• Data validation and business rules
• Maintaining derived calculations
• Referential integrity enforcement

What is Trigger
Trigger is a stored program automatically executed when table undergoes DML operation.
Executes invisibly to user, always in context of triggering statement.

Trigger Timing and Events

Trigger Scope

• Single table only (no cross-table triggers)
• Single statement execution
• OLD/NEW row references available
• Cannot call stored procedures (MySQL limitation)

Basic Trigger Structure

OLD and NEW References

• NEW: New row values (INSERT/UPDATE)
• OLD: Previous row values (UPDATE/DELETE)

Use Cases

• Automatically set created_date on INSERT
• Update audit log on changes
• Prevent invalid data entry
• Maintain summary tables

Performance Impact
Triggers execute for every affected row.

Permissions Required

Execution Order

1. BEFORE triggers (row-by-row)
2. DML statement executes
3. AFTER triggers (row-by-row)

Important Limitations

• Cannot use in transactions with DDL
• No recursive triggers
• 1 trigger per event per table
• Cannot call other triggers directly

Example Scenario
Audit all student changes:

Common Mistakes

• Infinite loops (BEFORE trigger causing UPDATE)
• Poor performance on bulk operations
• Missing TRIGGER privilege
• Complex logic slowing transactions
• Forgetting OLD/NEW context

Key Points to Remember

• BEFORE/AFTER timing, INSERT/UPDATE/DELETE events
• OLD/NEW row references available
• FOR EACH ROW mandatory
• Executes automatically per affected row
• TRIGGER privilege required
• Single trigger per event per table