Trigger function creation in PostgreSQL - Step-by-Step Execution

Concept Flow - Trigger function creation

Write trigger function code

↓

Create function in database

↓

Define trigger on table

↓

Trigger fires on event

↓

Trigger function executes

↓

Perform actions (e.g., modify data)

↓

Finish trigger execution

This flow shows how you write a trigger function, create it in the database, attach it to a table event, and how it runs automatically when that event happens.

Execution Sample

PostgreSQL

CREATE FUNCTION log_update() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log(table_name, action) VALUES ('my_table', 'update');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This code creates a trigger function that logs an update action into an audit_log table.

Execution Table

Step	Action	Evaluation	Result
1	Create function log_update()	Function code parsed and stored	Function created successfully
2	Create trigger on my_table for UPDATE event	Trigger linked to function log_update()	Trigger created successfully
3	Update a row in my_table	Trigger fires on UPDATE event	Trigger function log_update() executes
4	Inside trigger: Insert into audit_log	Insert statement runs	New row added to audit_log with table_name='my_table', action='update'
5	Trigger function returns NEW	Row update continues	Update completes with trigger side effect
6	No more trigger actions	End of trigger execution	Control returns to main operation

💡 Trigger function completes and returns control after performing its actions

Variable Tracker

Variable	Start	After Step 4	Final
NEW	Row data before update	Row data after update	Returned to update operation
audit_log	No new row	Row inserted with ('my_table', 'update')	Contains new audit entry

Key Moments - 3 Insights

Why does the trigger function return NEW at the end?

Returning NEW tells PostgreSQL to continue with the updated row; see execution_table step 5 where RETURN NEW allows the update to complete.

When exactly does the trigger function run?

It runs automatically after the UPDATE event on the table, as shown in execution_table step 3 where the trigger fires immediately after the update starts.

What happens if the trigger function does not return NEW or OLD?

PostgreSQL expects a row to continue the operation; missing RETURN causes an error or stops the operation, so always return NEW or OLD as appropriate.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 4?

A The trigger function inserts a row into audit_log

B The trigger function finishes without action

C The update operation is canceled

D The trigger function deletes a row

💡 Hint

Check execution_table row 4 under 'Action' and 'Result' columns

At which step does the trigger function return control to the main update operation?

A Step 3

B Step 5

C Step 2

D Step 6

💡 Hint

Look at execution_table step 5 where RETURN NEW is mentioned

If the trigger function did not return NEW, what would happen?

A The update would continue normally

B The trigger would run twice

C An error would occur stopping the update

D The audit_log would not be updated

💡 Hint

Refer to key_moments about the importance of returning NEW or OLD

Concept Snapshot

Trigger function creation in PostgreSQL:
- Write function with RETURNS trigger
- Use PL/pgSQL language
- Function runs on table events (INSERT, UPDATE, DELETE)
- Must RETURN NEW or OLD row
- Attach function to trigger on table
- Trigger fires automatically on event

Full Transcript

Trigger functions in PostgreSQL are special functions that run automatically when certain events happen on a table, like updates. You first write the function using PL/pgSQL, making sure it returns the updated or original row. Then you create the function in the database and attach it to a trigger on the table for a specific event. When that event happens, the trigger fires and runs your function. For example, a trigger function can log updates to an audit table. The function must return NEW or OLD to let the database know how to proceed with the row. This process helps automate actions tied to data changes.

Practice

1. What is the correct return type for a trigger function in PostgreSQL?

easy

A. void

B. trigger

C. integer

D. boolean

Solution

1. Step 1: Understand trigger function requirements

   Trigger functions must return a special type that PostgreSQL recognizes for triggers.

2. Step 2: Identify the correct return type

   The return type for trigger functions is always trigger, not standard types like void or integer.

3. Final Answer:

   trigger -> Option B

4. Quick Check:

   Trigger functions return type = trigger [OK]

Hint: Trigger functions always return type 'trigger' [OK]

Common Mistakes:

• Using void or integer as return type
• Forgetting to specify return type
• Confusing trigger function with normal function

2. Which of the following is the correct way to start a trigger function in PostgreSQL?

easy

A. CREATE FUNCTION my_trigger() RETURNS trigger AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION my_trigger() RETURNS boolean AS $$ BEGIN RETURN TRUE; END; $$ LANGUAGE plpgsql;

C. CREATE TRIGGER my_trigger() RETURNS trigger AS $$ BEGIN RETURN OLD; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION my_trigger() RETURNS void AS $$ BEGIN END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check syntax for trigger function creation

   Trigger functions must be created with CREATE FUNCTION, return type trigger, and use plpgsql language.

2. Step 2: Identify correct function body and return statement

   Trigger functions must return NEW or OLD row, so RETURN NEW; is correct here.

3. Final Answer:

   CREATE FUNCTION my_trigger() RETURNS trigger AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   Trigger function syntax = CREATE FUNCTION my_trigger() RETURNS trigger AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql; [OK]

Hint: Trigger functions use RETURNS trigger and RETURN NEW or OLD [OK]

Common Mistakes:

• Using RETURNS void instead of trigger
• Using CREATE TRIGGER instead of CREATE FUNCTION
• Returning boolean or void instead of NEW or OLD

3. Given this trigger function, what will be the result when a new row is inserted?

CREATE FUNCTION check_age() RETURNS trigger AS $$
BEGIN
  IF NEW.age < 18 THEN
    RAISE EXCEPTION 'Age must be 18 or older';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

medium

A. The row is inserted regardless of age

B. The function returns NULL causing an error

C. The row is always rejected

D. The row is inserted only if age is 18 or older

Solution

1. Step 1: Analyze the condition in the trigger function

   The function checks if NEW.age < 18 and raises an exception if true.

2. Step 2: Understand effect of exception and return

   If age is less than 18, insertion stops with error. Otherwise, RETURN NEW; allows insertion.

3. Final Answer:

   The row is inserted only if age is 18 or older -> Option D

4. Quick Check:

   Exception blocks underage inserts = The row is inserted only if age is 18 or older [OK]

Hint: Exception stops insert; RETURN NEW allows it [OK]

Common Mistakes:

• Thinking all rows insert regardless
• Assuming RETURN NEW inserts row without checks
• Confusing RAISE EXCEPTION with warnings

4. Identify the error in this trigger function code:

CREATE FUNCTION update_timestamp() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW
END;
$$ LANGUAGE plpgsql;

medium

A. Cannot assign to NEW in trigger functions

B. Incorrect return type, should be void

C. Missing semicolon after RETURN NEW

D. Missing BEGIN keyword

Solution

1. Step 1: Check syntax for statements inside function

   Each statement must end with a semicolon in PL/pgSQL.

2. Step 2: Identify missing semicolon

   The line RETURN NEW lacks a semicolon at the end, causing syntax error.

3. Final Answer:

   Missing semicolon after RETURN NEW -> Option C

4. Quick Check:

   PL/pgSQL statements end with semicolon [OK]

Hint: Check semicolons after each statement [OK]

Common Mistakes:

• Forgetting semicolon after RETURN NEW
• Thinking NEW cannot be assigned
• Confusing return type with void

5. You want to create a trigger function that automatically sets a column last_modified to the current timestamp whenever a row is updated. Which of the following trigger function definitions correctly achieves this?

hard

A. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN OLD.last_modified := NOW(); RETURN OLD; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION set_last_modified() RETURNS void AS $$ BEGIN NEW.last_modified := NOW(); END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Identify correct assignment operator in PL/pgSQL

   PL/pgSQL uses := for assignment, not =.

2. Step 2: Check return type and returned row

   Trigger functions must return type trigger and return NEW for BEFORE UPDATE triggers.

3. Step 3: Evaluate each option

   CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; uses correct assignment :=, returns NEW, and has correct return type. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; uses = which is invalid for assignment. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN OLD.last_modified := NOW(); RETURN OLD; END; $$ LANGUAGE plpgsql; assigns to OLD which is read-only. CREATE FUNCTION set_last_modified() RETURNS void AS $$ BEGIN NEW.last_modified := NOW(); END; $$ LANGUAGE plpgsql; returns void which is invalid.

4. Final Answer:

   CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; -> Option A

5. Quick Check:

   Use := for assignment and return NEW [OK]

Hint: Use := for assignment and return NEW in trigger [OK]

Common Mistakes:

• Using = instead of := for assignment
• Returning OLD instead of NEW
• Using void return type for trigger function