BEFORE UPDATE triggers execute before row modifications persist to disk.
Access both OLD (current values) and NEW (proposed changes) for comparison.
Ideal for validation, calculated fields, and automatic timestamp updates.

Basic BEFORE UPDATE Syntax


OLD and NEW in BEFORE UPDATE
OLD = existing row values (read-only).
NEW = proposed changes (read/write).

Auto-Timestamp Update


Prevent Status Reversal


Enforce Business Rules


Calculate Derived Fields


Audit Field Population


Complex Validation Example


Prevent Self-Reference Loops


Percentage Change Validation



Inventory Stock Validation


OLD/NEW Availability Matrix

BEFORE UPDATE

NEW

OLD

All columns

✓ (R/W)

✓ (R)

Multiple Conditions Combined


Permissions Required


Testing BEFORE UPDATE Trigger


Execution Order

  1. DML identifies affected rows
  2. BEFORE UPDATE triggers fire (alphabetical order)
  3. Row modifications applied
  4. AFTER UPDATE triggers fire

Performance Considerations

  • Executes per updated row
  • Complex logic impacts bulk UPDATE performance
  • Keep validations simple and index-dependent

Common Use Cases

  • Automatic timestamp updates
  • Business rule enforcement
  • Data validation/sanitization
  • Audit trail population
  • Derived field calculation

Important Notes

  • OLD values read-only, NEW fully modifiable
  • Multiple BEFORE UPDATE triggers execute alphabetically
  • SIGNAL aborts entire UPDATE statement
  • Cannot modify triggering table within trigger

Production Example
Complete employee update trigger:


Key Points

  • BEFORE UPDATE accesses OLD (current) and NEW (proposed) values
  • Perfect for validation and auto-population
  • SIGNAL prevents invalid updates
  • Executes before data persists to disk
  • Alphabetical execution order for multiple triggers
THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid new email format'; END IF; -- Auto-timestamp SET NEW.updated_at = NOW(); END;