A trigger in SQL is a database object that automatically executes (or “fires”) a specified block of code in response to certain events (like INSERT, UPDATE, or DELETE) on a table or view.
🔧 What Is a Trigger?
A trigger is like a hidden “watchdog” that runs custom logic automatically when data changes in a table.
🧠 When Would You Use a Trigger?
✅ Common Use Cases:
Use Case
Description
Audit logging
Log who changed what and when
Enforcing business rules
Block or transform certain changes
Auto-updating fields
E.g., update updated_at on row change
Cascading actions
Automatically affect related records
Preventing invalid changes
Validate complex conditions before changes
🧾 Basic Trigger Syntax (PostgreSQL)
-- 1. Create a trigger function
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at := NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 2. Attach trigger to a table
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
✅ Now every time a users row is updated, updated_at is set to NOW() automatically.
🚦 Trigger Timing Options
Keyword
Fires When?
BEFORE INSERT
Before inserting a row
AFTER INSERT
After inserting a row
BEFORE UPDATE
Before an update
AFTER UPDATE
After an update
BEFORE DELETE
Before deleting a row
AFTER DELETE
After deleting a row
🧮 FOR EACH ROW vs FOR EACH STATEMENT
Option
Description
FOR EACH ROW
Fires once per affected row
FOR EACH STATEMENT
Fires once per SQL statement
🐬 Example: MySQL Trigger
CREATE TRIGGER log_deletes
AFTER DELETE ON users
FOR EACH ROW
INSERT INTO deleted_users_log(user_id, deleted_at)
VALUES (OLD.id, NOW());
