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.