Because sometimes, your database needs to take action BEFORE you even know it!
What the Heck are Triggers?
Imagine you have an overly eager employee who jumps up to do tasks before you even finish talking.
- You start to say, "Hey, can you—"
- And they’ve already done it.
That’s exactly what a TRIGGER does in PostgreSQL! It automatically runs a function when something happens in your database (like INSERT, UPDATE, or DELETE).
Think of Triggers as SQL's version of a butler—always listening and taking action when needed.
Example use cases:
Keep logs automatically ("Who deleted all my data?!")
Validate or modify data before it’s saved
Send notifications when something important happens
Understanding Triggers: When Do They Fire?
Types of Triggers in PostgreSQL
| Trigger Type | When it Happens |
|---|---|
| BEFORE | Before the event (modify data before it’s stored) |
| AFTER | After the event (good for logging, notifications, etc.) |
| INSTEAD OF | Replaces an event (useful for views) |
What Events Can Triggers React To?
Triggers can be attached to:
INSERT (when a new row is added)
UPDATE (when a row is modified)
DELETE (when a row is removed)
TRUNCATE (when the whole table is wiped out... R.I.P. data)
Creating Your First Trigger: Auto-Logging Deleted Users
Let’s say we don’t trust our users (they might delete things and pretend they didn’t 👀).
Solution? A trigger that logs every deleted user into an audit_log table.
Step 1: Create the Audit Log Table
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
username TEXT,
deleted_at TIMESTAMP DEFAULT now()
);
Step 2: Create a Function for the Trigger
CREATE FUNCTION log_deleted_user() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (username, deleted_at)
VALUES (OLD.username, now());
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
Step 3: Attach the Function to a Trigger
CREATE TRIGGER trigger_log_deletion
AFTER DELETE ON users
FOR EACH ROW EXECUTE FUNCTION log_deleted_user();
Now, whenever someone deletes a user...
DELETE FROM users WHERE id = 5;
The user is deleted
A record is automatically added to audit_log
Boom! No more sneaky deletions!
BEFORE Triggers: Fixing Data Before It’s Saved
Imagine users keep entering lowercase emails (ugh).
You want to auto-correct emails before they get inserted.
Step 1: Create the Trigger Function
CREATE FUNCTION enforce_uppercase_email() RETURNS TRIGGER AS $$
BEGIN
NEW.email := UPPER(NEW.email);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Step 2: Attach It to a BEFORE INSERT Trigger
CREATE TRIGGER trigger_uppercase_email
BEFORE INSERT ON users
FOR EACH ROW EXECUTE FUNCTION enforce_uppercase_email();
Now, even if someone tries this...
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
PostgreSQL automatically converts john@example.com → JOHN@EXAMPLE.COM.
Your database stays clean without needing manual fixes!
INSTEAD OF Triggers: When You Want Full Control
Let’s say you have a VIEW that combines users and profiles.
Normally, you can't insert/update a view. But with an INSTEAD OF trigger, you can!
Example: Insert into Multiple Tables via a View
Step 1: Create a View
CREATE VIEW user_profiles AS
SELECT u.id, u.username, p.bio
FROM users u
JOIN profiles p ON u.id = p.user_id;
Step 2: Create an INSTEAD OF Trigger Function
CREATE FUNCTION insert_user_profile() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO users (username) VALUES (NEW.username) RETURNING id INTO NEW.id;
INSERT INTO profiles (user_id, bio) VALUES (NEW.id, NEW.bio);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Step 3: Attach the Trigger to the View
CREATE TRIGGER trigger_insert_user_profile
INSTEAD OF INSERT ON user_profiles
FOR EACH ROW EXECUTE FUNCTION insert_user_profile();
Now, you can insert into the view, and PostgreSQL automatically splits it into two tables!
INSERT INTO user_profiles (username, bio) VALUES ('alice', 'Loves PostgreSQL');
Magically inserts into both users & profiles!
Event Triggers: When You Want to Spy on Your Database
Unlike normal triggers (which act on table events), Event Triggers listen to bigger events, like:
Schema changes (ALTER TABLE, CREATE TABLE, etc.)
DDL commands (DROP DATABASE, etc.)
Example: Log Every Time Someone Drops a Table
CREATE FUNCTION log_table_drops() RETURNS EVENT TRIGGER AS $$
BEGIN
RAISE NOTICE 'Someone is dropping a table: %', tg_tag;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER trigger_log_drops
ON DROP TABLE
EXECUTE FUNCTION log_table_drops();
Now, every time someone tries to drop a table, PostgreSQL warns them:
DROP TABLE users;
-- NOTICE: Someone is dropping a table: DROP TABLE
Caught you in 4K!
Removing Triggers: Oops, I Didn’t Mean That!
If you ever need to remove a trigger:
DROP TRIGGER trigger_log_deletion ON users;
Or to remove an event trigger:
DROP EVENT TRIGGER trigger_log_drops;
Poof! Gone.
Triggers = Automatic SQL Magic
AFTER triggers → Great for logging & notifications
BEFORE triggers → Modify data before saving
INSTEAD OF triggers → Make views editable
EVENT triggers → Track big schema changes
Why write extra queries when PostgreSQL can do the work for you?
Automate everything and let PostgreSQL be your loyal assistant!
0 Comments