Trigger for Auto-Updating the last_modified Field When a Record Changes

Imagine you're building an app to manage students and courses, and you have a students table. This table has a last_modified field that should automatically update every time you change something in a record (like updating a student's name or age).

Instead of manually updating last_modified in every SQL query, let's create a trigger that does it for us.

Structure of the students Table

First, let's create the students table that we'll use in this example. This table holds basic info about students:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- Unique student identifier
    name VARCHAR(100) NOT NULL,   -- Student name
    age INT,                      -- Student age
    last_modified TIMESTAMP NOT NULL DEFAULT NOW() -- Last modified time
);

• The last_modified field is initially set to the current time (NOW()) when the record is created.
• This field will be auto-updated when student data changes.

Let's fill the table with some test data:

INSERT INTO students (name, age)
VALUES 
    ('Otto Lin', 20),
    ('Maria Chi', 22),
    ('Alex Song', 19);

Now the data in the table looks like this:

Creating the Function to Update last_modified

The PL/pgSQL function will be used by the trigger to update the last_modified field. It'll be called automatically before the record is changed.

Let's create the update_last_modified function:

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Update the last_modified field to the current time
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• NEW is a special variable that holds the new data for the record (after it's changed).
• We set NEW.last_modified to NOW() (the current date and time).
• The function returns the updated NEW variable, which is needed for the trigger to work right.

Creating the Trigger

Now let's create a trigger that will automatically call the update_last_modified function every time a record in the students table is updated.

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Here's what's going on:

• BEFORE UPDATE means the trigger fires before the update operation happens.
• FOR EACH ROW means the trigger fires for every row that's being changed.
• EXECUTE FUNCTION update_last_modified() tells it to call the update_last_modified function.

Testing the Trigger

Now let's check out how our trigger works. Let's select the current data from the students table:

SELECT * FROM students;

Result:

Now let's update the age of the student with student_id = 1:

UPDATE students
SET age = 21
WHERE student_id = 1;

Let's select the data from the table again:

SELECT * FROM students;

Expected Result:

Notice: the last_modified field for the record with student_id = 1 got updated to the current time, while the other records stayed the same.

Extending the Trigger Logic

Let's say now we want the last_modified field to update only if certain columns change. For example, if only the student's name or age changes, the trigger should fire, but not for other changes.

To do this, you can add a WHEN condition to the trigger definition.

Let's create a new trigger with a condition:

DROP TRIGGER IF EXISTS set_last_modified ON students;

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name OR OLD.age IS DISTINCT FROM NEW.age)
EXECUTE FUNCTION update_last_modified();

Here:

• The WHEN condition checks if the old values (OLD) are different from the new ones (NEW) for the name and age columns.
• If neither of these columns changed, the trigger doesn't fire.

Let's try updating the data in the table again and test the new logic.

Tips for Using Triggers

1. Don't overuse triggers. They're handy, but can make your database logic more complicated and debugging harder.
2. Always document what your trigger does and when it's used.
3. Use WHEN conditions to minimize accidental trigger calls.
4. Remember, triggers can affect database performance, especially if your table has a lot of records.

Common Mistakes When Working with Triggers

Incorrect data changes. For example, you forgot to set a value for NEW and returned the original data without changes.

Wrong conditions. For example, you forgot to add a WHEN condition, and the trigger fires even when nothing needs to change.

Recursion. If a trigger calls a function that calls the trigger again, you can accidentally create an infinite loop. PostgreSQL has recursion protection, but it's better to avoid these situations.

This example shows how using triggers can make automatic data updates way easier. In real projects, this technique is often used for change logging, keeping data integrity, and automating boring stuff.