Triggers
Automate actions on row changes with BEFORE, AFTER, and INSTEAD OF triggers.
What you'll learn
- What a trigger is and how it fires automatically
- BEFORE vs AFTER triggers and when to use each
- Trigger events: INSERT, UPDATE, DELETE
- Using triggers for auditing and derived columns
- Why triggers can be a double-edged sword
Concept
What Is a Trigger?
A trigger is a named procedure that the database executes automatically when a specific event (INSERT, UPDATE, or DELETE) occurs on a table. Unlike stored procedures that you call explicitly, triggers fire on their own — you do not invoke them in application code. They are attached to a table and an event.
BEFORE vs AFTER
- BEFORE triggers run before the row is modified. They can validate data, modify the NEW row (e.g. set a default or computed column), or cancel the operation by raising an error. Use BEFORE for data transformation and validation.
- AFTER triggers run after the row has been modified. They cannot change the row, but they can update other tables, write audit logs, or fire notifications. Use AFTER for side effects like auditing.
Trigger Events
A trigger can fire on one or more events: INSERT, UPDATE, or DELETE. For UPDATE, you can further restrict it to fire only when specific columns change: UPDATE OF price, stock. Inside the trigger, you can access the old and new row values via OLD and NEW (PostgreSQL/MySQL) or :OLD and :NEW (Oracle).
Auditing with Triggers
A common use case is an audit log: every time a row changes, insert a record into an audit table with the old values, the new values, who changed it, and when. This is implemented with an AFTER trigger on UPDATE and DELETE. It provides a full history of changes without changing application code.
The Double-Edged Sword
Triggers are powerful but dangerous. They run invisibly — application developers may not realize that an INSERT also updates three other tables. This makes debugging difficult, can cause performance issues (cascading triggers), and can lead to surprising behavior during bulk operations. Use triggers sparingly, document them well, and prefer explicit application logic when possible.
Example
-- PostgreSQL: BEFORE trigger to set updated_at automatically
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();
-- PostgreSQL: AFTER trigger for audit logging
CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.price IS DISTINCT FROM OLD.price THEN
INSERT INTO price_audit (product_id, old_price, new_price, changed_at)
VALUES (OLD.id, OLD.price, NEW.price, NOW());
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_audit_price
AFTER UPDATE OF price ON products
FOR EACH ROW
EXECUTE FUNCTION log_price_change();
Two triggers: a BEFORE UPDATE trigger that auto-sets updated_at, and an AFTER UPDATE trigger that logs price changes to an audit table only when the price actually changed.
Try it
- SQL Formatter
Format trigger function and CREATE TRIGGER statements.
- Code Diff
Compare trigger definitions when modifying audit logic.
Common mistakes
The mistake
Creating cascading triggers that fire each other in a loop
The fix
Triggers on table A that update table B, which has a trigger that updates table A, can loop infinitely. Design triggers carefully and use conditions to break cycles.
The mistake
Using triggers for business logic that should be in application code
The fix
Triggers are invisible to application developers and hard to test. Use them for data-level concerns (timestamps, audit logs) and keep business rules in the application.
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.