Triggers
SQL · Views & Triggers
Theory
A trigger is a block of SQL that the database runs automatically when a specific event happens on a table: an INSERT, UPDATE or DELETE. You define when it fires (BEFORE or AFTER the change, or INSTEAD OF it for views) and whether it runs once per affected row (FOR EACH ROW) or once per statement. Inside a row-level trigger, NEW holds the incoming values and OLD the previous ones.
A typical use is auditing: AFTER INSERT ON employees FOR EACH ROW, write NEW.id and the current time into audit_log. BEFORE triggers can validate or adjust values before they are saved. Triggers run inside the same transaction, so if the trigger fails, the original change is rolled back too. Because they are invisible to the person running the statement, heavy or chained triggers make systems slow and hard to debug. Syntax differs a lot between engines.
Why we use it
Some actions must happen every time data changes, no matter which app or person made the change: logging, keeping a last_updated column current, enforcing complex rules. Triggers guarantee it inside the database.
When to use it
Use triggers for auditing, automatic timestamps and integrity rules that constraints cannot express. Prefer constraints for simple rules and application or pipeline code for business workflows, so logic stays visible and testable.
How it works
- An INSERT, UPDATE or DELETE runs on employees
- Is a trigger defined for this event? Yes: Check its timing. No: Change runs normally.
- BEFORE trigger: inspect or change NEW values
- Apply the change to the table
- AFTER trigger: e.g. write a row to audit_log
- Did the trigger raise an error? Yes: Roll back the whole statement. No: Commit together.
- Change and trigger work succeed as one unit
Syntax
CREATE TRIGGER trigger_name
AFTER INSERT ON table
FOR EACH ROW
BEGIN ... END;Example
CREATE TRIGGER log_new_employee
AFTER INSERT ON employees
FOR EACH ROW
INSERT INTO audit_log VALUES (NEW.id, NOW());Key points
- Runs automatically: no manual call needed
- Useful for auditing, validation and keeping derived columns current
- NEW and OLD give the row values after and before the change
- Runs in the same transaction as the triggering statement
- Hidden logic: keep triggers small and documented