Trigger Types in PostgreSQL: BEFORE, AFTER, INSTEAD OF

Triggers in PostgreSQL fall into three main categories:

1. BEFORE — these run before the main operation (like INSERT, UPDATE, or DELETE). You can use them to stop the operation from happening or to tweak the data before it's saved.

2. AFTER — these fire after the main operation is done. This type is often used for logging, creating related records, or doing stuff that depends on the operation finishing successfully.

3. INSTEAD OF — these run instead of the actual operation. They're only used for views. For example, if someone tries to insert data into a view, you can control that process with an INSTEAD OF trigger.

BEFORE Trigger

BEFORE triggers fire before PostgreSQL does the main operation. They're handy if you want to check or change data right before it's saved. Think of it like a baggage check before boarding a plane: if the bag doesn't fit, you can change it or block it entirely.

Let's do an example with data validation before inserting. Say we have a students table where we store info about students. We want to make sure a student's age isn't over 100 (which is pretty unlikely, honestly).

Create the table:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);

Create a trigger function:

CREATE OR REPLACE FUNCTION validate_age()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.age > 100 THEN
        RAISE EXCEPTION 'Student age cannot be more than 100 years!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Create the trigger:

CREATE TRIGGER before_insert_students
BEFORE INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION validate_age();

Now, if you try to insert a student with age over 100, PostgreSQL will throw an error:

INSERT INTO students (name, age) VALUES ('Ivan Ivanov', 120);
-- Error: Student age cannot be more than 100 years!

That's how you do validation!

AFTER Trigger

AFTER triggers run after the main operation finishes successfully. They're useful for stuff that depends on the result of the operation. For example, logging or creating related records.

Scenario: we've got a students table, and we want to log all changes in a separate log table.

Create the log table:

CREATE TABLE students_log (
    id SERIAL PRIMARY KEY,
    student_id INT,
    operation TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create a trigger function:

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO students_log (student_id, operation)
    VALUES (NEW.id, TG_OP);  -- TG_OP holds the operation type: INSERT, UPDATE, or DELETE
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Create the trigger:

CREATE TRIGGER after_insert_students
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

Now, when you add a new student, PostgreSQL automatically logs the operation:

INSERT INTO students (name, age) VALUES ('Anna Ling', 22);

SELECT * FROM students_log;
-- Result:
-- id | student_id | operation |        timestamp
--  1 |          1 | INSERT    | 2023-11-15 12:00:00

INSTEAD OF Trigger

INSTEAD OF triggers fire instead of doing the operation. This is the only trigger type you can use with views. They let you handle operations you can't do directly on a view.

Scenario: we've got two tables, courses and teachers. We'll make a view that joins them, and write a trigger to handle inserts through that view.

Create the tables:

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    teacher_id INT NOT NULL
);

CREATE TABLE teachers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

Create the view:

CREATE VIEW course_details AS
SELECT 
    courses.id AS course_id,
    courses.name AS course_name,
    teachers.name AS teacher_name
FROM courses
JOIN teachers ON courses.teacher_id = teachers.id;

The problem: you can't just insert data into the view, since it pulls from two tables. The fix: use an INSTEAD OF trigger.

Create a trigger function:

CREATE OR REPLACE FUNCTION insert_course_details()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO teachers (name) VALUES (NEW.teacher_name) RETURNING id INTO NEW.teacher_id;
    INSERT INTO courses (name, teacher_id) VALUES (NEW.course_name, NEW.teacher_id);
    RETURN NULL;  -- Data isn't saved in the view
END;
$$ LANGUAGE plpgsql;

Create the trigger:

CREATE TRIGGER instead_of_insert_course_details
INSTEAD OF INSERT ON course_details
FOR EACH ROW
EXECUTE FUNCTION insert_course_details();

Now you can insert data straight into the view:

INSERT INTO course_details (course_name, teacher_name)
VALUES ('Mathematics', 'Alex Ming');

SELECT * FROM courses;
-- Result:
-- id | name        | teacher_id
--  1 | Mathematics | 1

SELECT * FROM teachers;
-- Result:
-- id | name
--  1 | Alex Ming

Comparing Trigger Types

Quirks and Limitations

BEFORE triggers can change data before the operation runs. For example, you could auto-format names (like making them uppercase).

AFTER triggers can't mess with the data, since the operation's already done. They're just for follow-up actions.

INSTEAD OF triggers only work on views. They let you pull off complex insert/update logic across several related tables.

That's it for today! If BEFORE, AFTER, and INSTEAD OF seem tricky, don't sweat it. The main thing is to remember what they're for and when to use them. Try building a few examples yourself to lock in the material.