Create Trigger in MySQL

riggers automatically execute database logic when tables are modified, ensuring data integrity and audit trails.
CREATE TRIGGER defines these automatic actions with precise timing control.
They are essential for audit logging, data validation, and maintaining data consistency across operations.

Basic CREATE TRIGGER Syntax

Example

Automatically sets creation timestamp on every INSERT.

BEFORE vs AFTER Triggers
BEFORE triggers modify row data before DML operation.
AFTER triggers execute after row changes complete.

OLD and NEW Keywords
NEW references new/updated row values.
OLD references previous row values (UPDATE/DELETE only).

Trigger Variables Availability

Multiple Statements in Trigger

CREATE TRIGGER with Conditions

Error Handling in Triggers

Trigger Naming Convention

• before_table_event_trigger
• after_table_event_trigger

Permissions Required

Execution Context
Triggers execute in triggering statement's context.
Cannot query same table being modified.

Performance Considerations
Each affected row fires trigger once.
1000 row UPDATE = 1000 trigger executions.

Important Notes

• FOR EACH ROW mandatory
• Single trigger per timing/event per table
• Cannot call DDL from triggers
• OLD/NEW scoped to trigger only

Example Scenario
Complete audit trigger system:

Common Mistakes

• Missing FOR EACH ROW clause
• Modifying triggering table inside trigger
• No TRIGGER privilege
• Infinite loops from AFTER UPDATE triggers
• Complex logic slowing bulk operations

Key Points to Remember

• CREATE TRIGGER defines automatic actions
• BEFORE/AFTER timing control
• OLD/NEW row value access
• FOR EACH ROW executes per affected row
• SIGNAL raises custom errors
• TRIGGER privilege required