Detailed explanation
A trigger is a database object containing logic that executes automatically when a specified event occurs. For data manipulation, that event is usually an INSERT, UPDATE, or DELETE on a table. Unlike a stored procedure, a trigger is not explicitly called by application code; the database invokes it as part of processing the triggering statement. Triggers are useful for auditing changes, enforcing specialised rules, and maintaining related data consistently across multiple applications that write to the same database.
Triggers differ by timing and execution scope. A BEFORE trigger runs before the affected operation completes and, in some databases, can validate or modify incoming row values. An AFTER trigger runs after the row operation, but not necessarily after the transaction commits. Some systems also support INSTEAD OF triggers, often for views. A row-level trigger executes once per affected row, whereas a statement-level trigger executes once per statement. Availability and syntax vary: MySQL supports row-level triggers, while PostgreSQL also supports statement-level triggers.
In the supplied MySQL-style example, CREATE TRIGGER audit_salary AFTER UPDATE ON employees defines a row-level trigger. FOR EACH ROW means an update affecting 100 employees invokes its body 100 times. OLD.salary contains the previous salary, NEW.salary contains the resulting salary, and the INSERT writes both values, the employee identifier, and a timestamp into salary_audit. However, the example logs every updated employee row, even when the salary remains unchanged. To audit only salary changes, add a condition using the database's NULL-safe comparison rules; in MySQL, NOT (OLD.salary <=> NEW.salary) detects a change even when one value is NULL.
For transactional tables, trigger work normally belongs to the same transaction as the original change, so a failure can reject the operation and a rollback removes both changes. This consistency comes with costs: extra writes, longer lock durations, and less visible dependencies. Interviewers expect you to explain these trade-offs and prefer constraints, defaults, or generated columns when they directly express the requirement. Use triggers for focused database-side behaviour, not as a hidden home for extensive business logic.
Key points to remember
- Triggers execute automatically in response to configured database events.
- Timing options include BEFORE, AFTER, and, in some databases, INSTEAD OF.
- Row-level triggers run per affected row; statement-level triggers run per statement.
- OLD and NEW expose row versions in MySQL-style triggers, depending on the event.
- Triggers support auditing but add execution overhead and hidden dependencies.
Common mistakes candidates make
- Assuming AFTER means after commit, or that audit rows always survive a rollback.
- Claiming the example records only salary changes without checking OLD.salary against NEW.salary.
- Treating trigger syntax, row references, and execution scope as identical across databases.
Likely follow-up questions
How is a trigger different from a stored procedure?
A stored procedure is explicitly invoked by a caller, while a trigger runs automatically when its configured event occurs. Procedures can accept parameters; triggers obtain their context through database-provided event and row information.
What happens if the trigger's audit INSERT fails?
With MySQL transactional tables such as InnoDB, a trigger error causes the triggering statement to fail and its changes to roll back. Nontransactional tables do not provide the same rollback guarantees.
Should derived columns always be maintained using triggers?
No: a generated column is often preferable when its value can be expressed as a supported calculation from the same row. Consider a trigger when the requirement needs behaviour that generated columns or declarative constraints cannot express.
Interview tip: Define a trigger, walk through OLD and NEW in the salary-audit example, and finish with transaction behaviour and one performance trade-off.