Imagine a university database that stores student records.
Whenever a student is added, updated, or deleted, the university may want to automatically record that activity.
For example:
Can PostgreSQL perform these tasks automatically?
Yes. PostgreSQL uses Triggers to perform actions automatically when specified database events occur.
A Trigger is a database mechanism that automatically executes a specified function when a particular event occurs on a table or view.
In simple words:
A trigger tells PostgreSQL:
“When a particular database operation happens, automatically perform this action.”
For example:
-- Automatically execute an action
-- whenever a new student is inserted.
A trigger is associated with a table or view.
Important: In PostgreSQL, a trigger executes a special function called a Trigger Function.
Triggers are useful for automating database tasks.
Common uses include:
| Purpose | Example |
|---|---|
| Audit records | Record when a student is added |
| Data validation | Ensure marks are between 0 and 100 |
| Automatic updates | Update a modified timestamp |
| Change tracking | Save old and new marks |
| Related operations | Update another table automatically |
Note: Triggers should be used carefully because automatic actions can make database behavior harder to understand and debug.
PostgreSQL supports the following main trigger events:
| Event | Description |
|---|---|
| INSERT | When a new record is inserted |
| UPDATE | When an existing record is updated |
| DELETE | When a record is deleted |
| TRUNCATE | When all records are removed using TRUNCATE |
For example:
INSERT INTO students
VALUES (4, 'Usman', 'Physics', 78);
An INSERT trigger can automatically execute when this statement runs.
PostgreSQL provides different trigger timings.
A BEFORE trigger executes before the database operation is completed.
It is commonly used for:
Example:
BEFORE INSERT ON students
An AFTER trigger executes after the database operation has taken place.
It is commonly used for:
Example:
AFTER INSERT ON students
An INSTEAD OF trigger executes instead of the requested operation.
It is mainly used with views.
For example, it can handle an INSERT operation on a view by performing the appropriate operation on its underlying tables.
PostgreSQL provides two trigger execution levels.
A row-level trigger executes separately for every affected row.
FOR EACH ROW
Example:
If an UPDATE statement modifies five student records, the trigger executes five times.
A statement-level trigger executes once for the entire SQL statement.
FOR EACH STATEMENT
Example:
If an UPDATE statement modifies five student records, the trigger executes only once.
| Row-Level | Statement-Level |
|---|---|
| Executes for each affected row | Executes once per SQL statement |
Uses FOR EACH ROW |
Uses FOR EACH STATEMENT |
| Can access OLD and NEW row values | Cannot access individual OLD and NEW row values |
We will use a students table to understand triggers.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
department VARCHAR(50),
marks INT
);
INSERT INTO students
VALUES
(1, 'Ali', 'Computer Science', 85),
(2, 'Sara', 'Mathematics', 72),
(3, 'Ahmed', 'Computer Science', 65);
SELECT * FROM students;
Expected output:
| student_id | student_name | department | marks |
|---|---|---|---|
| 1 | Ali | Computer Science | 85 |
| 2 | Sara | Mathematics | 72 |
| 3 | Ahmed | Computer Science | 65 |
A trigger function is a special PostgreSQL function that contains the instructions to be executed by a trigger.
Unlike an ordinary function, a trigger function:
TRIGGER data type.NEW and OLD.CREATE OR REPLACE FUNCTION function_name()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
-- Write trigger instructions here
RETURN NEW;
END;
$$;
Understanding the syntax:
| Keyword | Purpose |
|---|---|
CREATE OR REPLACE FUNCTION |
Creates or replaces a function |
RETURNS TRIGGER |
Specifies that it is a trigger function |
LANGUAGE plpgsql |
Uses PostgreSQL’s procedural language |
BEGIN |
Starts the function body |
RETURN NEW |
Returns the new row |
END |
Ends the function body |
PostgreSQL provides two special variables inside row-level trigger functions.
NEW represents the new row being inserted or updated.
Example:
NEW.student_name
NEW.marks
NEW.department
OLD represents the existing row before an update or deletion.
Example:
OLD.student_name
OLD.marks
OLD.department
| Operation | NEW | OLD |
|---|---|---|
| INSERT | New row | Not available |
| UPDATE | Updated row | Previous row |
| DELETE | Not available | Deleted row |
For example, when marks change from 70 to 85:
OLD.marks = 70NEW.marks = 85This makes it possible to track changes in student records.
Suppose we want PostgreSQL to automatically record every new student added to the students table.
We will create an audit table and a trigger.
CREATE TABLE student_audit (
audit_id SERIAL PRIMARY KEY,
student_id INT,
action TEXT,
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
This table stores information about student activities.
CREATE OR REPLACE FUNCTION log_student_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO student_audit(student_id, action)
VALUES (NEW.student_id, 'INSERT');
RETURN NEW;
END;
$$;
Understanding the code:
log_student_insert().TRIGGER.NEW.student_id accesses the ID of the newly inserted student.student_audit.RETURN NEW returns the new row.CREATE TRIGGER student_insert_trigger
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_insert();
Understanding the code:
| Part | Meaning |
|---|---|
CREATE TRIGGER |
Creates a trigger |
student_insert_trigger |
Trigger name |
AFTER INSERT |
Executes after insertion |
ON students |
Attached to the students table |
FOR EACH ROW |
Executes for each inserted row |
EXECUTE FUNCTION |
Calls the trigger function |
Insert a new student:
INSERT INTO students
VALUES (4, 'Usman', 'Physics', 78);
Now check the audit table:
SELECT * FROM student_audit;
Expected output:
| audit_id | student_id | action |
|---|---|---|
| 1 | 4 | INSERT |
Result
The trigger automatically recorded the insertion. We did not manually insert anything into the audit table.
Suppose student marks must always be between 0 and 100.
We can use a BEFORE trigger to validate marks before saving a record.
CREATE OR REPLACE FUNCTION validate_student_marks()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.marks < 0 OR NEW.marks > 100 THEN
RAISE EXCEPTION
'Marks must be between 0 and 100';
END IF;
RETURN NEW;
END;
$$;
Understanding the code:
NEW.marks accesses the marks being inserted or updated.IF checks whether marks are outside the allowed range.RAISE EXCEPTION stops the operation and displays an error.RETURN NEW allows valid data to proceed.CREATE TRIGGER check_student_marks
BEFORE INSERT OR UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION validate_student_marks();
This trigger executes before every INSERT or UPDATE operation on the students table.
INSERT INTO students
VALUES (5, 'Hina', 'Physics', 90);
Expected result: The record is inserted successfully.
INSERT INTO students
VALUES (6, 'Bilal', 'Mathematics', 120);
Expected error:
ERROR: Marks must be between 0 and 100
The record will not be inserted.
Suppose we want to maintain a record whenever a student’s marks are changed.
We need to store:
CREATE TABLE student_marks_audit (
audit_id SERIAL PRIMARY KEY,
student_id INT,
old_marks INT,
new_marks INT,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE OR REPLACE FUNCTION log_marks_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO student_marks_audit
(student_id, old_marks, new_marks)
VALUES
(OLD.student_id, OLD.marks, NEW.marks);
RETURN NEW;
END;
$$;
Here:
OLD.marks stores the previous marks.NEW.marks stores the updated marks.CREATE TRIGGER student_marks_update_trigger
AFTER UPDATE OF marks ON students
FOR EACH ROW
EXECUTE FUNCTION log_marks_update();
This trigger executes after the marks column is updated.
UPDATE students
SET marks = 95
WHERE student_id = 1;
SELECT * FROM student_marks_audit;
Expected output:
| audit_id | student_id | old_marks | new_marks |
|---|---|---|---|
| 1 | 1 | 85 | 95 |
The trigger automatically saved the previous and updated marks.
Suppose we want to record the ID of every student whose record is deleted.
For DELETE operations, we use OLD because the deleted row is no longer available as a new row.
CREATE OR REPLACE FUNCTION log_student_delete()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO student_audit(student_id, action)
VALUES (OLD.student_id, 'DELETE');
RETURN OLD;
END;
$$;
CREATE TRIGGER student_delete_trigger
AFTER DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_delete();
DELETE FROM students
WHERE student_id = 3;
SELECT * FROM student_audit;
Expected output includes:
| student_id | action |
|---|---|
| 4 | INSERT |
| 3 | DELETE |
The deletion is recorded automatically.
| BEFORE Trigger | AFTER Trigger |
|---|---|
| Executes before the operation | Executes after the operation |
Can modify NEW values |
Cannot change the already stored row through NEW |
| Useful for validation | Useful for audit logging |
| Can stop the operation using an exception | Can also raise an exception and roll back the statement |
Important: The return value of an AFTER trigger is ignored. For BEFORE INSERT and UPDATE triggers, return NEW to allow the row operation to proceed. For BEFORE DELETE, return OLD to allow deletion.
CREATE TRIGGER example_row_trigger
AFTER UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION log_marks_update();
This example illustrates row-level execution. The function should be designed for the relevant UPDATE operation.
A statement-level trigger executes once per SQL statement, regardless of how many rows are affected.
CREATE OR REPLACE FUNCTION show_update_notice()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Students table was updated';
RETURN NULL;
END;
$$;
Create the trigger:
CREATE TRIGGER students_update_notice
AFTER UPDATE ON students
FOR EACH STATEMENT
EXECUTE FUNCTION show_update_notice();
Test:
UPDATE students
SET department = 'Computer Science'
WHERE marks > 70;
The notice appears once for the UPDATE statement, not once per updated student.
If a trigger is no longer required, we can remove it using DROP TRIGGER.
DROP TRIGGER trigger_name ON table_name;
DROP TRIGGER student_insert_trigger ON students;
To avoid an error if the trigger does not exist:
DROP TRIGGER IF EXISTS student_insert_trigger ON students;
Important: A trigger is attached to a table. Therefore, the table name must be specified when dropping it.
The trigger function is a separate database object. It can be removed separately when it is no longer used:
DROP FUNCTION log_student_insert();
Do not drop a trigger function while an existing trigger still depends on it.
PostgreSQL also allows us to temporarily disable or enable a trigger.
ALTER TABLE students
DISABLE TRIGGER student_insert_trigger;
ALTER TABLE students
ENABLE TRIGGER student_insert_trigger;
When a trigger is disabled, it does not execute for the relevant operations until it is enabled again.
TRIGGER.NEW represents the new row in INSERT and UPDATE operations.OLD represents the previous row in UPDATE and DELETE operations.FOR EACH ROW executes once for every affected row.FOR EACH STATEMENT executes once for the entire SQL statement.SELECT or CALL.Try these exercises using the students table.
NEW?OLD?CALL?| Concept | Description |
|---|---|
| Trigger | Automatically executes an action when an event occurs |
| Trigger Function | Function executed by a trigger |
| INSERT | New record is added |
| UPDATE | Existing record is modified |
| DELETE | Record is removed |
| BEFORE | Executes before the operation |
| AFTER | Executes after the operation |
| INSTEAD OF | Replaces an operation, mainly on views |
| NEW | New row values |
| OLD | Previous or deleted row values |
| FOR EACH ROW | Executes for each affected row |
| FOR EACH STATEMENT | Executes once per SQL statement |
| DROP TRIGGER | Removes a trigger |
Next Topic: Common Table Expressions (CTEs) in PostgreSQL
In the next lesson, students will learn how to use the WITH clause to create temporary named query results and write more readable SQL queries.