Triggers
A trigger is a DBMS-managed procedure that automatically executes in response to a specified data modification event (INSERT, UPDATE, DELETE) on a table. Unlike stored procedures, triggers are never called directly — they fire implicitly when the triggering event occurs, enforcing business rules, audit requirements, or data integrity at the database level.
Trigger Timing
- BEFORE — Executes before the triggering statement. Can modify or reject the incoming data.
- AFTER — Executes after the triggering statement. The data change has already occurred.
- INSTEAD OF — Replaces the triggering statement entirely. Used primarily on views to make them updatable.
Trigger Granularity
- FOR EACH ROW — Fires once per affected row (row-level trigger). Most common.
- FOR EACH STATEMENT — Fires once per SQL statement regardless of rows affected (statement-level). Available in PostgreSQL, not SQLite.
Syntax
SQLite
CREATE TRIGGER audit_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
WHEN OLD.salary != NEW.salary
BEGIN
INSERT INTO audit_log (table_name, action, old_value, new_value, changed_at)
VALUES ('employees', 'salary_update', OLD.salary, NEW.salary, datetime('now'));
END;MySQL
DELIMITER //
CREATE TRIGGER validate_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary cannot be negative';
END IF;
END //
DELIMITER ;OLD and NEW References
| Event | OLD | NEW |
|---|---|---|
| INSERT | Not available | New row values |
| UPDATE | Pre-update values | Post-update values |
| DELETE | Deleted row values | Not available |
Common Use Cases
- Data Validation — Enforce complex business rules beyond simple constraints
- Audit Logging — Automatically record who changed what and when
- Derived/Computed Values — Auto-populate timestamps, totals, or status fields
- Cascading Operations — Propagate changes to related tables
- Enforcing Referential Integrity — Where foreign keys alone aren’t sufficient
WHEN Clause
Conditional execution — the trigger body only runs if the condition is true:
CREATE TRIGGER only_big_changes
AFTER UPDATE ON accounts
FOR EACH ROW
WHEN ABS(NEW.balance - OLD.balance) > 10000
BEGIN
INSERT INTO large_transaction_alerts (account_id, amount)
VALUES (NEW.id, NEW.balance - OLD.balance);
END;Nested and Recursive Triggers
- SQLite: Nested triggers enabled via
PRAGMA recursive_triggers = ON - MySQL: Triggers can activate other triggers but cannot directly recurse on the same table
- Care required to avoid infinite loops
Execution Order
When multiple triggers exist on the same table and event:
- SQLite: Triggers fire in the order they were created
- MySQL: Only one BEFORE and one AFTER trigger per event per table (pre-8.0); MySQL 8.0+ supports
FOLLOWSandPRECEDESfor ordering - PostgreSQL: Alphabetical order by trigger name
Triggers vs Stored Procedures
| Aspect | Trigger | Stored Procedure |
|---|---|---|
| Invocation | Automatic on DML event | Explicit CALL |
| Parameters | None (uses OLD/NEW) | IN, OUT, INOUT |
| Return values | None | Can return result sets |
| Transaction context | Within the triggering transaction | Own or caller’s transaction |
| Use case | Reactive side effects | Reusable business logic |