Creating a Simple Trigger for Updating Data: AFTER INSERT

Let's say we have a table that stores data about students. In this table, we want to automatically update the last_modified field (the date of the last record change) every time a new student is added. This field is important for tracking changes and managing data.

The workflow goes like this:

1. When a new record is added to the table, the last_modified field automatically gets the current date and time.
2. We'll use an AFTER INSERT trigger, which fires after a successful data insert.

Creating a Function for the Trigger

First, you need to create a function in PL/pgSQL. This function will update the last_modified field in our table. The function is a required part for the trigger to work, since the trigger itself just points to what needs to be done, but all the logic is handled by the function.

So, let's start by creating the table:

-- Creating the students table
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL,
    last_modified TIMESTAMP
);

Now let's create a function to update the last_modified field:

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

Let's break down what this function does:

• CREATE OR REPLACE FUNCTION update_last_modified() — creates a function named update_last_modified. If such a function already exists, it'll be replaced.
• RETURNS TRIGGER — tells PostgreSQL that this function is meant to be used with a trigger.
• NEW.last_modified := NOW(); — updates the last_modified field using the NOW() function, which returns the current date and time.
• RETURN NEW; — returns the updated record. This is a required step for an AFTER trigger.

Creating the Trigger

After creating the function, we can create the trigger itself, linking it to the students table. Here's how you do it:

-- Creating a trigger after inserting a record
CREATE TRIGGER set_last_modified
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Here's what's happening here:

• CREATE TRIGGER set_last_modified — creates a trigger named set_last_modified.
• AFTER INSERT — the trigger will fire after a row is successfully inserted into the table.
• ON students — the trigger is attached to the students table.
• FOR EACH ROW — the trigger will run for every new row added to the table.
• EXECUTE FUNCTION update_last_modified(); — calls the function we created earlier.

Note: the names of the trigger (set_last_modified) and the function (update_last_modified) can be whatever you want, but it's a good idea to follow naming standards so your code is easy to understand.

Testing the Trigger

Let's check how our trigger works. First, let's add a couple of records to the students table:

-- Inserting data into the table
INSERT INTO students (name, age) VALUES ('Ivan Ivanov', 20);
INSERT INTO students (name, age) VALUES ('Anna Petrova', 22);

Now let's see what we got in the table:

-- Viewing data in the table
SELECT * FROM students;

The expected result might look something like this:

Notice that the last_modified field was automatically filled with the current date and time for each record.

Errors You Might Run Into

1. Error: "relation does not exist" when creating the trigger. This error pops up if the students table hasn't been created. Make sure you create the table before creating the trigger.
2. Access error. If your database user doesn't have permissions to create functions or triggers, the trigger won't be created. Check your user's privileges.
3. No function call in the trigger. If you forget to specify EXECUTE FUNCTION update_last_modified(), the trigger won't be able to do what you want.

Improving the Trigger: Adding Conditions

In real-world tasks, it's often useful to limit when the trigger runs. For example, if the age field is less than 18, we don't want to update last_modified. You can do this with a WHEN condition:

-- Creating a trigger with a condition
CREATE TRIGGER set_last_modified
AFTER INSERT ON students
FOR EACH ROW
WHEN (NEW.age >= 18)
EXECUTE FUNCTION update_last_modified();

Now the last_modified field will only be updated for students whose age is >= 18.

Practical Use

Triggers like this are used all the time in real projects. Here are some examples:

• Automatically updating the last change time of a record (like we just did).
• Tracking changes in the database and writing those changes to a log table.
• Making sure data stays consistent, like checking related tables before doing an operation.
• Auditing data to meet legal or company rules.

These skills are especially handy if you're working with systems where data accuracy and reliability are super important, like in banking systems, inventory management, or CRM.

2

Task

SQL SELF, level 57, lesson 3

Locked

Creating a Function and Trigger for Automatic Update Time Filling

Creating a Function and Trigger for Automatic Update Time Filling

Open