BEFORE DELETE triggers execute before row removal from table.
OLD contains current row values (read-only, NEW unavailable).
Critical for soft deletes, final validations, and backup procedures.

Basic BEFORE DELETE Syntax


OLD in BEFORE DELETE
OLD = current row values before deletion (read-only).
NEW unavailable (no new row created).

Soft Delete Implementation


Prevent Critical Record Deletion


Relationship Validation



Backup Before Deletion


Archive with Complete Row Data


Soft Delete with Status Update



OLD Column Availability

BEFORE DELETE

NEW

OLD

All columns

āœ—

āœ“ (Read-only)

Prevent Accidental Production Deletes


Multiple Validation Checks


Permissions Required


Testing BEFORE DELETE Trigger


Execution Flow

  1. DELETE identifies target rows
  2. BEFORE DELETE triggers execute (alphabetical)
  3. If SIGNAL raised ā†’ entire DELETE aborted
  4. If all pass ā†’ row permanently removed

Common Use Cases

  • Soft delete implementation
  • Relationship integrity checks
  • Backup/archive procedures
  • Final business rule validation
  • Audit trail creation

Production Pattern: Complete Soft Delete


Performance Considerations

Operation

Impact

Optimization

Subqueries

High

Proper indexes

Table updates

Medium

Batch processing

Archive inserts

Low

Separate tablespace

Error SQLSTATE Codes

Code

Usage

45000

Custom business errors

23000

Foreign key violations

Important Notes

  • OLD read-only, NEW unavailable
  • SIGNAL aborts entire DELETE statement
  • Multiple triggers execute alphabetically
  • Cannot modify triggering table
  • Perfect for "last chance" validation

Advanced Compliance Logging


Key Points

  • BEFORE DELETE provides final validation opportunity
  • OLD contains complete row data before removal
  • SIGNAL prevents deletion entirely
  • Essential for soft deletes and data protection
  • Executes per deleted row (scales linearly)
  • Transactional: failures preserve all data