What is a trigger in SQL and what types exist?
A trigger is code that the database runs automatically when INSERT, UPDATE or DELETE happens on a table. Types are BEFORE, AFTER and INSTEAD OF, and row-level or statement-level.
Explain it simply
A trigger is like a CCTV alarm: when a door opens (a row changes), something fires automatically. Nobody has to remember to call it, which is both its strength and its danger.
Example
Keep an audit trail of salary changes (MySQL syntax):
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary <> NEW.salary THEN
INSERT INTO salary_log (emp_id, old_salary, new_salary, changed_at)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END IF;
END;OLD holds the row before the change and NEW the row after.
Types:
- BEFORE: runs before the change, good for validating or modifying NEW values.
- AFTER: runs after the change, good for auditing.
- INSTEAD OF: replaces the action, used mainly on views.
- Row-level runs once per affected row; statement-level (PostgreSQL, Oracle, SQL Server) once per statement.
Triggers run inside the same transaction as the statement, so a failing trigger rolls back the change.
What to say in the interview
"Triggers are automatic procedures fired by data changes, typically for auditing or enforcing rules. They are invisible to application code and can hurt performance and debugging, so I use them sparingly."
Common mistakes
- Writing a trigger that updates its own table and triggers itself repeatedly.