How Triggers Interact with PL/pgSQL Functions: OLD, NEW, TG_OP

Triggers in PostgreSQL don't just let you run functions in response to some actions—they also pass some awesome variables into those functions. Thanks to these variables, you can figure out what the data in the table looked like before the operation, what it looks like after, and what operation actually happened.

• OLD — holds the old row data from the table before the operation. It's used in triggers for UPDATE and DELETE, because for INSERT there just isn't anything "old" yet.
• NEW — holds the new row data from the table after the operation. It's used in triggers for INSERT and UPDATE.
• TG_OP — contains a text description of the current operation: INSERT, UPDATE, or DELETE.

All these variables are automatically available inside the function that's hooked up to the trigger.

Theory without practice is like SQL without indexes: slow and sad. So let's dive into some hands-on examples.

Using OLD to Access Old Data

Imagine we have a table called students. And someone decides to change a student's age (maybe they got it wrong and thought the student was still 20, not already 25).

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

To track what changes happened, let's make a log table:

CREATE TABLE student_changes (
    change_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    old_value INT,
    new_value INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now let's go further: we'll create a function that logs the changes. This is where OLD comes in handy:

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Log the age change
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (OLD.id, OLD.age, NEW.age);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

And now let's make a trigger:

CREATE TRIGGER student_age_update
AFTER UPDATE OF age ON students
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age) -- Only run if the age actually changed
EXECUTE FUNCTION log_student_changes();

Let's add a student and then make a change:

INSERT INTO students (name, age) VALUES ('Alisa', 20);

UPDATE students
SET age = 25
WHERE name = 'Alisa';

-- Check the change log:
SELECT * FROM student_changes;

You'll see that the log table recorded the change: age went from 20 to 25. Magic? Nope, just OLD.

Using NEW for New Data

Now let's say we want to automatically log the ID and name of every new student when they're added (yeah, it's a little paranoid, but sometimes that's useful):

CREATE OR REPLACE FUNCTION log_new_student()
RETURNS TRIGGER AS $$
BEGIN
    -- Log the new student's data
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (NEW.id, NULL, NEW.age); -- No old value, since this is an INSERT
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER student_insert_log
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_new_student();

Again, let's add a new student and check:

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

-- Check the log:
SELECT * FROM student_changes;

You'll see the new student in the logs. That's some next-level data care!

Using TG_OP to Figure Out the Operation Type

But what if we want a universal logging trigger that can handle INSERT, UPDATE, and even DELETE? That's where the TG_OP variable comes to the rescue.

Let's make a universal function:

CREATE OR REPLACE FUNCTION log_all_operations()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (NEW.id, NULL, NEW.age);

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NEW.age);

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NULL);

    END IF;

    RETURN NULL; -- For AFTER trigger on DELETE, return NULL
END;
$$ LANGUAGE plpgsql;

Let's create a trigger that fires on all three operations:

CREATE TRIGGER universal_student_log
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_all_operations();

Add, update, and delete a student:

INSERT INTO students (name, age) VALUES ('Charlie', 30);
UPDATE students SET age = 31 WHERE name = 'Charlie';
DELETE FROM students WHERE name = 'Charlie';

-- Check the log:
SELECT * FROM student_changes;

You can see the log of all operations—one trigger to rule them all!

Common Mistakes When Using OLD, NEW, TG_OP

When working with triggers, you can run into a few classic problems:

"Why doesn't OLD work on insert?" That's just how it works: for INSERT there is no old data. Use NEW instead.

"What if NEW doesn't work on delete?" Again, that's expected: for DELETE there is no new data. Use OLD instead.

Trigger logic causes infinite recursion. Make sure your trigger isn't accidentally calling itself. You can set clear conditions in the WHEN block or check TG_OP to avoid this.

2

Task

SQL SELF, level 57, lesson 4

Locked

Logging Student Age Changes

Logging Student Age Changes

Open

1

Survey/quiz

Intro to Triggers, level 57, lesson 4

Unavailable

Intro to Triggers

Intro to Triggers

Open