Trigger Syntax Basics: CREATE TRIGGER, WHEN, EXECUTE FUNCTION

To create a trigger in PostgreSQL, you need to define these components:

• Trigger name.
• Event type (INSERT, UPDATE, DELETE).
• Execution timing (BEFORE or AFTER).
• The table it relates to.
• The function that will be executed (in PL/pgSQL or another language).

Here's the general command structure:

CREATE TRIGGER trigger_name
[BEFORE | AFTER] {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW | FOR EACH STATEMENT]
WHEN (condition)
EXECUTE FUNCTION function_name();

Simple Trigger Example

Let's create a basic students table and add a trigger that fires after a new record is added.

First, let's create the students table

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What's going on here? We created a table with fields id, name, and last_modified. The last_modified field will store the date and time of the last record change.

Triggers are always tied to functions. First, let's create a simple function that will update the last_modified field every time a record is added:

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

What is this magic?

1. NEW — a special variable that holds new row values (for INSERT or UPDATE events).
2. CURRENT_TIMESTAMP — a function that returns the current date and time.
3. RETURN NEW — returns the changed row for saving.

Now let's create the trigger itself:

CREATE TRIGGER set_last_modified
AFTER INSERT
ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Breakdown:

• AFTER INSERT: the trigger fires after a new row is added.
• ON students: the trigger applies to the students table.
• FOR EACH ROW: the trigger fires for every new row.
• EXECUTE FUNCTION: specifies which function should be called.

Testing the Trigger

Let's check how our trigger works:

INSERT INTO students (name) VALUES ('Alice');
SELECT * FROM students;

You’ll see something like this:

The trigger automatically updated the last_modified field. Magic? Nope, just PostgreSQL doing its thing.

Using Conditions with WHEN

Sometimes you want the trigger to run only under certain conditions. That's what the WHEN keyword is for.

Let’s look at an example where the trigger only fires for certain values.

Say we want the trigger to fire only for students named "Alice". Let's change our trigger:

CREATE OR REPLACE FUNCTION update_last_modified_condition()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_last_modified_condition
AFTER INSERT
ON students
FOR EACH ROW
WHEN (NEW.name = 'Alice')
EXECUTE FUNCTION update_last_modified_condition();

Now the trigger will update the last_modified field only for students named "Alice".

Let's check it:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO students (name) VALUES ('Bob');
SELECT * FROM students;

Result:

Heads up: for student "Bob" the last_modified field stayed empty because the trigger didn’t fire.

Trigger and Function Link: EXECUTE FUNCTION

The function is the heart of any trigger. A trigger can’t exist without a function that defines its logic. In PostgreSQL, you can write functions in PL/pgSQL or other supported languages like Python or C.

Let’s show an example using a PL/pgSQL function.

We'll create a function that logs changes in a separate audit_log table.

First, let's create the audit_log table

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    operation TEXT NOT NULL,
    student_id INTEGER NOT NULL,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

And now – the function:

CREATE OR REPLACE FUNCTION log_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (operation, student_id)
    VALUES ('INSERT', NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Now let’s write the trigger:

CREATE TRIGGER log_student_insert
AFTER INSERT
ON students
FOR EACH ROW
EXECUTE FUNCTION log_insert();

And check how it works:

INSERT INTO students (name) VALUES ('Charlie');
SELECT * FROM audit_log;

You’ll see something like this:

The trigger automatically logged the new record.

Trigger Mistakes and Gotchas

Error: missing function. If you try to create a trigger without a function, PostgreSQL will throw an error. Always create the function before the trigger.

Performance issues. A ton of triggers or heavy functions can slow down your database. Use them wisely.

Recursion. If a trigger changes the same table it fires on, you might get an infinite loop. Use WHEN conditions to avoid this.