BEFORE trigger behavior in PostgreSQL - Deep Dive

Overview - BEFORE trigger behavior

What is it?

A BEFORE trigger in PostgreSQL is a special kind of database function that runs automatically before an insert, update, or delete operation happens on a table. It lets you check or change the data before it is saved. This helps keep data clean and consistent without changing the application code.

Why it matters

BEFORE triggers exist to catch or modify data early, preventing bad or unwanted changes. Without them, you would have to rely on application code to validate data, which can be inconsistent or forgotten. This could lead to errors, corrupted data, or security issues in your database.

Where it fits

Before learning BEFORE triggers, you should understand basic SQL commands like INSERT, UPDATE, and DELETE. After mastering BEFORE triggers, you can explore AFTER triggers, constraint triggers, and advanced trigger functions for complex data workflows.

Mental Model

Core Idea

A BEFORE trigger acts like a gatekeeper that inspects and can change data just before it enters the database.

Think of it like...

Imagine a security guard checking your luggage before you enter a concert. The guard can stop you, ask you to fix something, or let you pass with changes. The BEFORE trigger is that guard for your data.

┌───────────────┐
│ Client sends  │
│ INSERT/UPDATE │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ BEFORE Trigger│
│  checks data  │
│  modifies if  │
│   needed      │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Data saved to │
│   database    │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What is a Trigger in PostgreSQL

Concept: Triggers are automatic functions that run when data changes happen in a table.

In PostgreSQL, a trigger is a special function linked to a table. It runs when you insert, update, or delete rows. Triggers help automate checks or actions without changing your application code.

Result

You understand that triggers automate database actions on data changes.

Knowing triggers exist helps you see how databases can enforce rules automatically, improving data safety.

2

Foundation

Types of Triggers: BEFORE vs AFTER

Concept: Triggers can run before or after data changes, affecting when and how they interact with data.

BEFORE triggers run before the data is saved, letting you modify or reject it. AFTER triggers run after the data is saved, useful for logging or cascading changes.

Result

You can distinguish when triggers run and their impact on data flow.

Understanding trigger timing is key to choosing the right trigger type for your needs.

3

Intermediate

BEFORE Trigger: Modifying Data Before Save

🤔Before reading on: do you think a BEFORE trigger can change the data being inserted or updated, or only observe it? Commit to your answer.

Concept: BEFORE triggers can change the data before it is saved to the table.

In a BEFORE trigger, you can access the new data row and modify its values. For example, you can set default values, clean input, or enforce rules by changing the data before the database stores it.

Result

Data saved to the table reflects any changes made by the BEFORE trigger.

Knowing you can modify data early lets you enforce data quality rules inside the database itself.

4

Intermediate

BEFORE Trigger: Preventing Data Changes

🤔Before reading on: can a BEFORE trigger stop a data change from happening, or does it only modify data? Commit to your answer.

Concept: BEFORE triggers can stop a data change by raising an error.

If a BEFORE trigger raises an exception, the data change is canceled. This lets you reject invalid data before it enters the table, acting like a gatekeeper that refuses bad input.

Result

Invalid data changes are blocked, keeping the database clean.

Understanding that triggers can reject data helps you build strong data validation inside the database.

5

Intermediate

BEFORE Trigger: Accessing OLD and NEW Rows

🤔Before reading on: in a BEFORE UPDATE trigger, do you think you can see both the old and new versions of a row? Commit to your answer.

Concept: BEFORE triggers can access both the existing (OLD) and new (NEW) row data during updates.

In UPDATE triggers, OLD holds the current row before change, and NEW holds the proposed new row. You can compare them to decide if changes are allowed or modify NEW before saving.

Result

You can write triggers that react differently based on what is changing.

Knowing how to access OLD and NEW rows unlocks powerful conditional logic in triggers.

6

Advanced

BEFORE Trigger: Returning NULL to Skip Operation

🤔Before reading on: do you think returning NULL from a BEFORE trigger cancels the operation or causes an error? Commit to your answer.

Concept: Returning NULL from a BEFORE trigger cancels the current row operation without error.

If a BEFORE trigger returns NULL, PostgreSQL skips the insert or update for that row silently. This can be used to filter out unwanted rows during bulk operations.

Result

Some rows can be silently ignored during data changes.

Understanding this subtle behavior helps you control data flow without raising errors.

7

Expert

BEFORE Trigger: Performance and Side Effects

🤔Before reading on: do you think BEFORE triggers always run quickly and without side effects? Commit to your answer.

Concept: BEFORE triggers run inside the transaction and can affect performance and cause side effects if not carefully designed.

Because BEFORE triggers run before data changes, they add overhead to every insert/update/delete. Complex logic or external calls inside triggers can slow down transactions or cause unexpected behaviors if they modify other tables or raise errors.

Result

Poorly designed BEFORE triggers can degrade database performance or cause hard-to-debug issues.

Knowing the cost and risks of triggers guides you to write efficient, safe trigger code.

Under the Hood

When a data change command runs, PostgreSQL checks for any triggers on the target table. For BEFORE triggers, it calls the trigger function before applying the change. The function receives special variables representing the new and old data rows. The trigger can modify the new row or raise errors. If the trigger returns NULL, the operation for that row is skipped. After the trigger finishes, PostgreSQL proceeds with the data change using the possibly modified data.

Why designed this way?

BEFORE triggers were designed to allow data validation and modification at the earliest point, ensuring data integrity before it reaches storage. This design separates data rules from application code, centralizing control in the database. Alternatives like application-side validation were less reliable and harder to maintain. The ability to cancel or modify data early prevents wasted work and inconsistent states.

┌───────────────┐
│ SQL Command   │
│ (INSERT/UPD)  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ BEFORE Trigger│
│ Function Call │
│  - Access OLD │
│  - Access NEW │
│  - Modify NEW │
│  - Raise Error│
│  - Return NULL│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Data Change   │
│ (Apply NEW or │
│  Skip if NULL)│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a BEFORE trigger run after the data is saved? Commit yes or no.

Common Belief: BEFORE triggers run after the data is saved to the table.

Reality: BEFORE triggers run before the data is saved, allowing modification or cancellation before storage.

Why it matters: Thinking triggers run after data is saved can lead to wrong assumptions about when data can be changed or validated.

Quick: Can a BEFORE trigger modify the data being inserted or updated? Commit yes or no.

Common Belief: BEFORE triggers can only observe data but cannot change it.

Reality: BEFORE triggers can modify the data before it is saved, changing values or adding defaults.

Why it matters: Not knowing this limits how you design data validation and cleaning inside the database.

Quick: Does returning NULL from a BEFORE trigger cause an error? Commit yes or no.

Common Belief: Returning NULL from a BEFORE trigger causes an error or rollback.

Reality: Returning NULL silently skips the current row operation without error.

Why it matters: Misunderstanding this can cause unexpected missing rows or silent data loss.

Quick: Do BEFORE triggers run outside the transaction? Commit yes or no.

Common Belief: BEFORE triggers run independently outside the transaction.

Reality: BEFORE triggers run inside the transaction, so their effects are rolled back if the transaction fails.

Why it matters: Assuming triggers run outside transactions can cause confusion about data consistency and rollback behavior.

Expert Zone

1

BEFORE triggers can be fired for each row or once per statement, affecting performance and logic complexity.

2

Modifying NEW row values in BEFORE triggers only works for INSERT and UPDATE, not DELETE operations.

3

BEFORE triggers can cause recursive calls if they modify the same table, requiring careful design to avoid infinite loops.

When NOT to use

Avoid BEFORE triggers for heavy computations or external calls that slow down transactions. Use AFTER triggers or background jobs for logging, notifications, or complex workflows. For simple constraints, prefer CHECK constraints or foreign keys for better performance and clarity.

Production Patterns

In production, BEFORE triggers are often used to enforce data formats, set audit fields like timestamps, or implement soft deletes by modifying data before saving. They are combined with AFTER triggers for logging and complex workflows. Careful testing ensures triggers do not degrade performance or cause unexpected side effects.

Connections

Database Constraints

BEFORE triggers build on constraints by allowing more complex validation and modification before data is saved.

Understanding BEFORE triggers helps you see how databases enforce rules beyond simple constraints, enabling dynamic data control.

Event-driven Programming

BEFORE triggers are a form of event-driven programming inside the database, reacting automatically to data changes.

Knowing this connection helps you apply event-driven thinking to database design, improving automation and responsiveness.

Quality Control in Manufacturing

BEFORE triggers act like quality inspectors who check and fix products before they leave the factory.

Seeing this parallel helps appreciate the importance of early validation to prevent defects and costly fixes later.

Common Pitfalls

#1 Trying to modify data in an AFTER trigger expecting it to change the saved row.

Wrong approach:

CREATE FUNCTION after_update() RETURNS trigger AS $$ BEGIN NEW.column := 'value'; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg AFTER UPDATE ON table FOR EACH ROW EXECUTE FUNCTION after_update();

Correct approach:

CREATE FUNCTION before_update() RETURNS trigger AS $$ BEGIN NEW.column := 'value'; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION before_update();

Root cause:

Misunderstanding that AFTER triggers cannot modify the row being saved, only observe or act after.

#2 Returning NULL from a BEFORE trigger expecting the row to be saved.

Wrong approach:

CREATE FUNCTION before_insert() RETURNS trigger AS $$ BEGIN RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg BEFORE INSERT ON table FOR EACH ROW EXECUTE FUNCTION before_insert();

Correct approach:

CREATE FUNCTION before_insert() RETURNS trigger AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg BEFORE INSERT ON table FOR EACH ROW EXECUTE FUNCTION before_insert();

Root cause:

Not knowing that returning NULL cancels the row operation silently.

#3 Writing complex logic with external calls inside a BEFORE trigger causing slow inserts.

Wrong approach:

CREATE FUNCTION before_insert() RETURNS trigger AS $$ BEGIN PERFORM http_request('http://example.com'); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg BEFORE INSERT ON table FOR EACH ROW EXECUTE FUNCTION before_insert();

Correct approach:

Use AFTER triggers or asynchronous background jobs for external calls, keeping BEFORE triggers fast and simple.

Root cause:

Not realizing triggers run inside transactions and slow external calls block data changes.

Key Takeaways

BEFORE triggers run before data changes, allowing you to modify or reject data early.

They act like gatekeepers, ensuring only valid and clean data enters the database.

Returning NULL from a BEFORE trigger cancels the current row operation silently.

BEFORE triggers run inside transactions, so their effects are rolled back if the transaction fails.

Careful design of BEFORE triggers prevents performance issues and unintended side effects.

Practice

1. What is the main purpose of a BEFORE trigger in PostgreSQL?

easy

A. To run code after data is inserted or updated

B. To delete rows automatically

C. To run custom code before data is inserted or updated

D. To create new tables dynamically

Solution

1. Step 1: Understand trigger timing

   BEFORE triggers execute before the actual data change happens in the table.

2. Step 2: Identify trigger purpose

   They allow checking or modifying data before it is saved, preventing bad data if needed.

3. Final Answer:

   To run custom code before data is inserted or updated -> Option C

4. Quick Check:

   BEFORE trigger = runs before data change [OK]

Hint: BEFORE triggers run before saving data [OK]

Common Mistakes:

• Confusing BEFORE with AFTER triggers
• Thinking triggers create or delete tables
• Assuming triggers run only after data changes

2. Which of the following is the correct syntax to create a BEFORE INSERT trigger on a table named users?

easy

A. CREATE TRIGGER trg BEFORE INSERT ON users CALL func();

B. CREATE TRIGGER trg AFTER INSERT ON users EXECUTE FUNCTION func();

C. CREATE TRIGGER trg BEFORE INSERT INTO users EXECUTE FUNCTION func();

D. CREATE TRIGGER trg BEFORE INSERT ON users EXECUTE FUNCTION func();

Solution

1. Step 1: Check trigger timing and event

   The trigger must be BEFORE INSERT on the table users.

2. Step 2: Verify syntax for calling function

   PostgreSQL uses EXECUTE FUNCTION to call the trigger function.

3. Final Answer:

   CREATE TRIGGER trg BEFORE INSERT ON users EXECUTE FUNCTION func(); -> Option D

4. Quick Check:

   Correct syntax uses BEFORE INSERT ON and EXECUTE FUNCTION [OK]

Hint: Use BEFORE INSERT ON table EXECUTE FUNCTION func() [OK]

Common Mistakes:

• Using AFTER instead of BEFORE
• Writing INTO instead of ON
• Using CALL instead of EXECUTE FUNCTION

3. Consider this BEFORE INSERT trigger function that changes the new row's status to 'active':

CREATE FUNCTION set_status() RETURNS trigger AS $$
BEGIN
  NEW.status := 'active';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_status BEFORE INSERT ON accounts
FOR EACH ROW EXECUTE FUNCTION set_status();

INSERT INTO accounts (id, status) VALUES (1, 'pending');
SELECT status FROM accounts WHERE id = 1;

What will be the output of the SELECT query?

medium

A. active

B. NULL

C. pending

D. Error: cannot insert

Solution

1. Step 1: Understand BEFORE INSERT trigger effect

   The trigger sets NEW.status to 'active' before the row is inserted.

2. Step 2: Check inserted data

   Even though 'pending' was given, the trigger changes it to 'active' before saving.

3. Final Answer:

   active -> Option A

4. Quick Check:

   BEFORE trigger modifies data before insert [OK]

Hint: BEFORE triggers can modify NEW row data before insert [OK]

Common Mistakes:

• Assuming original value 'pending' is saved
• Thinking trigger runs after insert
• Expecting NULL or error without reason

4. Given this trigger function:

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;

Which problem will occur if you create a BEFORE INSERT trigger using this function and try to insert a row with age = 16?

medium

A. An error will be raised and insertion will stop

B. The trigger will silently ignore the age check

C. The row will be inserted with age 16

D. The age will be automatically set to 18

Solution

1. Step 1: Analyze trigger logic

   If NEW.age is less than 18, the trigger raises an exception.

2. Step 2: Understand effect of RAISE EXCEPTION

   Raising an exception stops the insert and returns an error to the client.

3. Final Answer:

   An error will be raised and insertion will stop -> Option A

4. Quick Check:

   RAISE EXCEPTION stops insert with error [OK]

Hint: RAISE EXCEPTION in BEFORE trigger stops insert with error [OK]

Common Mistakes:

• Thinking the row inserts anyway
• Assuming age auto-corrects
• Ignoring that exceptions stop execution

5. You want to create a BEFORE UPDATE trigger on the products table that prevents the price from being set below zero. Which trigger function code correctly enforces this rule?

hard

A. BEGIN
IF NEW.price < 0 THEN
NEW.price := 0;
END IF;
RETURN NEW;
END;

B. BEGIN
IF NEW.price < 0 THEN
RAISE EXCEPTION 'Price cannot be negative';
END IF;
RETURN NEW;
END;

C. BEGIN
IF OLD.price < 0 THEN
RAISE EXCEPTION 'Price cannot be negative';
END IF;
RETURN NEW;
END;

D. BEGIN
IF NEW.price < 0 THEN
DELETE FROM products WHERE id = NEW.id;
END IF;
RETURN NEW;
END;

Solution

1. Step 1: Identify correct condition check

   The trigger must check NEW.price to prevent negative values before update.

2. Step 2: Choose proper action on invalid data

   Raising an exception stops the update and prevents invalid price.

3. Step 3: Eliminate incorrect options

   BEGIN
     IF NEW.price < 0 THEN
       NEW.price := 0;
     END IF;
     RETURN NEW;
   END;

   silently changes price to 0 (may hide errors), C checks OLD.price (wrong), D deletes row (not appropriate).

4. Final Answer:

   BEGIN IF NEW.price < 0 THEN RAISE EXCEPTION 'Price cannot be negative'; END IF; RETURN NEW; END; -> Option B

5. Quick Check:

   Use RAISE EXCEPTION on NEW.price < 0 to stop update [OK]

Hint: Raise error on NEW.price < 0 to block update [OK]

Common Mistakes:

• Checking OLD.price instead of NEW.price
• Silently fixing invalid data instead of error
• Deleting rows inside BEFORE trigger