What is a Trigger in SQL?
Learn what a SQL trigger is, BEFORE vs AFTER timing, row vs statement level, and common use cases like auditing and validation.
Expected Interview Answer
A trigger in SQL is a stored piece of code that automatically executes in response to a specific event, such as an INSERT, UPDATE, or DELETE, on a table.
Triggers are defined once and fire automatically whenever the matching event happens, without the application needing to call them explicitly. They can run before or after the event (BEFORE or AFTER), and for each affected row (FOR EACH ROW) or once per statement. Common uses include enforcing complex business rules, auditing changes by logging old and new values, maintaining derived or summary data, and validating data before it is written.
- Enforces business rules automatically at the database level
- Centralizes auditing without changing application code
- Keeps derived or summary data in sync
- Runs consistently regardless of which client made the change
AI Mentor Explanation
A ground’s automatic sprinkler system does not wait for someone to manually turn it on; it is configured to trigger the moment the pitch moisture sensor reports a low reading, without groundstaff intervention. A SQL trigger works the same way: it is set up once against an event like an INSERT or UPDATE, and the database fires it automatically every time that event occurs, without the application explicitly calling it.
Step-by-Step Explanation
Step 1
Define the trigger
Attach a trigger to a table and specify the event: INSERT, UPDATE, or DELETE.
Step 2
Choose timing
Decide whether it fires BEFORE the event (to validate/modify data) or AFTER (to react to it).
Step 3
Set the granularity
Choose FOR EACH ROW to run once per affected row, or once per statement.
Step 4
Trigger fires automatically
Whenever the matching event occurs on the table, the database runs the trigger logic with no explicit call.
What Interviewer Expects
- Definition tied to a table event (INSERT/UPDATE/DELETE)
- Understanding of BEFORE vs AFTER timing
- Awareness of FOR EACH ROW vs statement-level triggers
- Real use cases like auditing and enforcing business rules
Common Mistakes
- Confusing triggers with stored procedures that must be called manually
- Not mentioning BEFORE/AFTER timing options
- Overusing triggers for logic better placed in the application layer
- Forgetting that triggers can cause hidden side effects that are hard to debug
Best Answer (HR Friendly)
“A trigger is a piece of code in the database that runs automatically whenever a specific event happens, like inserting, updating, or deleting a row in a table. It is commonly used for things like auditing changes, enforcing rules, or keeping related data in sync, without the application needing to do that work itself.”
Code Example
CREATE TABLE employee_audit (
employee_id INT,
old_salary NUMERIC,
new_salary NUMERIC,
changed_at TIMESTAMP DEFAULT NOW()
);
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.salary <> OLD.salary THEN
INSERT INTO employee_audit (employee_id, old_salary, new_salary)
VALUES (OLD.id, OLD.salary, NEW.salary);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();Follow-up Questions
- What is the difference between a BEFORE and an AFTER trigger?
- How do row-level and statement-level triggers differ?
- What are the downsides of overusing triggers?
- How does a trigger differ from a stored procedure?
MCQ Practice
1. A SQL trigger executes when?
A trigger fires automatically when its defined event, like INSERT or UPDATE, occurs on the table.
2. BEFORE triggers are commonly used to?
BEFORE triggers run prior to the event, allowing validation or modification of the incoming data.
3. A FOR EACH ROW trigger fires?
Row-level triggers execute once for every row impacted by the triggering statement.
Flash Cards
What is a SQL trigger? — Stored code that automatically executes in response to an INSERT, UPDATE, or DELETE event on a table.
BEFORE vs AFTER trigger? — BEFORE runs prior to the event to validate/modify data; AFTER runs once the event has occurred.
What is a row-level trigger? — A trigger set with FOR EACH ROW that fires once per affected row.
Give a common trigger use case. — Auditing changes by logging old and new values whenever a row is updated.