PostgreSQL Triggers Explained with Real‑World Examples
Sometimes you want the database to automatically perform
an action when data is inserted, updated, or deleted.
This is where triggers are used in PostgreSQL.
In this article, you will learn:
- What
triggers are
- Why
triggers are useful
- How
triggers work
- Types
of triggers in PostgreSQL
- How
to create triggers with examples
This guide is written for beginners.
What Is a Trigger in PostgreSQL?
A trigger is a database object that:
- Automatically
executes when a specific event occurs
- Is
attached to a table
- Runs
a function when data changes
Triggers respond to events like:
- INSERT
- UPDATE
- DELETE
Triggers help automate logic inside the database.
Why Use Triggers?
Triggers are useful for:
- Automatically
updating data
- Logging
changes
- Enforcing
business rules
- Auditing
data
- Maintaining
derived or calculated values
Triggers reduce manual work and errors.
How PostgreSQL Triggers Work
A trigger has two parts:
- A trigger
function (written in PL/pgSQL)
- A trigger
that calls the function
When an event occurs on a table, PostgreSQL:
- Executes
the trigger
- Calls
the trigger function automatically
Trigger Events
Triggers can fire on:
- INSERT
- UPDATE
- DELETE
- TRUNCATE
Example:
- After
a row is inserted
- Before
a row is updated
Trigger Timing
Triggers can run:
- BEFORE
the event
- AFTER
the event
BEFORE triggers:
- Modify
data before saving
AFTER triggers:
- Used
for logging or auditing
Example Table
SQL
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT,
email TEXT,
created_at TIMESTAMP
);
Show more lines
Creating a Trigger Function
Trigger functions do not return a value.
They return a special type called TRIGGER.
Example Trigger Function
SQL
CREATE OR REPLACE FUNCTION set_created_at()
RETURNS TRIGGER AS $
BEGIN
NEW.created_at = NOW();
RETURN NEW;
END;
$ LANGUAGE plpgsql;
Show more lines
This function sets the created_at timestamp automatically.
Creating a Trigger
SQL
CREATE TRIGGER users_created_at_trigger
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION set_created_at();
Show more lines
Now, whenever a user is inserted, created_at is set
automatically.
Inserting Data to Test Trigger
SQL
INSERT INTO users (name, email)
VALUES ('Ravi', 'ravi@example.com');
Show more lines
The created_at column is filled automatically.
BEFORE vs AFTER Trigger Example
AFTER INSERT Trigger for Logging
Example log table:
SQL
CREATE TABLE user_logs (
id SERIAL PRIMARY KEY,
user_id INTEGER,
action TEXT,
log_time TIMESTAMP
);
Show more lines
Trigger Function
SQL
CREATE OR REPLACE FUNCTION log_user_insert()
RETURNS TRIGGER AS $
BEGIN
INSERT INTO user_logs(user_id, action, log_time)
VALUES (NEW.id, 'INSERT', NOW());
RETURN NEW;
END;
$ LANGUAGE plpgsql;
Show more lines
Create Trigger
SQL
CREATE TRIGGER user_insert_log
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_insert();
Show more lines
Whenever a user is inserted, a log entry is created.
Trigger Using UPDATE
Update Trigger Function
SQL
CREATE OR REPLACE FUNCTION log_user_update()
RETURNS TRIGGER AS $
BEGIN
INSERT INTO user_logs(user_id, action, log_time)
VALUES (NEW.id, 'UPDATE', NOW());
RETURN NEW;
END;
$ LANGUAGE plpgsql;
Show more lines
Create UPDATE Trigger
SQL
CREATE TRIGGER user_update_log
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_update();
Show more lines
OLD and NEW Keywords
Inside trigger functions:
- NEW
refers to new row data
- OLD
refers to old row data
Example:
SQL
OLD.email
NEW.email
Show more lines
Useful for comparing changes.
Viewing Triggers on a Table
SQL
\d users
Show more lines
Triggers attached to the table will be listed.
Dropping a Trigger
SQL
DROP TRIGGER trigger_name ON table_name;
Show more lines
Example:
SQL
DROP TRIGGER user_insert_log ON users;
Show more lines
Dropping a Trigger Function
SQL
DROP FUNCTION function_name;
Show more lines
Example:
SQL
DROP FUNCTION set_created_at;
Show more lines
When NOT to Use Triggers
Avoid triggers when:
- Logic
is very complex
- Debugging
becomes difficult
- Performance
is critical
- Logic
can be handled in application code
Triggers should be used carefully.
Best Practices for Triggers
- Keep
trigger logic simple
- Use
triggers for automation, not heavy logic
- Avoid
recursive triggers
- Document
trigger behavior
- Test
triggers thoroughly
Good triggers improve reliability.
Summary
- Triggers
run automatically on data changes
- Triggers
respond to INSERT, UPDATE, DELETE
- Trigger
functions use PL/pgSQL
- BEFORE
triggers modify data
- AFTER
triggers log or audit changes
You now understand PostgreSQL triggers clearly.
What’s Next?
Next, we will learn error handling and exceptions in
PL/pgSQL.
Next article:
PostgreSQL Exception Handling Using PL/pgSQL
No comments:
Post a Comment