BEFORE trigger behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of BEFORE INSERT trigger modifying NEW row

Consider a PostgreSQL table users with columns id (serial primary key) and username (text). A BEFORE INSERT trigger modifies the NEW.username to uppercase before insertion.

What will be the output of this query after inserting 'alice'?

INSERT INTO users (username) VALUES ('alice');
SELECT username FROM users WHERE id = currval(pg_get_serial_sequence('users','id'));

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, username TEXT);
CREATE OR REPLACE FUNCTION uppercase_username() RETURNS TRIGGER AS $$
BEGIN
  NEW.username := UPPER(NEW.username);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_insert_uppercase
BEFORE INSERT ON users
FOR EACH ROW EXECUTE FUNCTION uppercase_username();

A alice

B NULL

C ALICE

D Alice

💡 Hint

BEFORE triggers can modify the NEW row before it is saved.

The BEFORE INSERT trigger changes NEW.username to uppercase. So the stored username is ALICE.

❓ query_result

intermediate

Effect of BEFORE UPDATE trigger returning NULL

In PostgreSQL, what happens if a BEFORE UPDATE trigger returns NULL?

Consider this trigger function:

CREATE OR REPLACE FUNCTION cancel_update() RETURNS TRIGGER AS $$
BEGIN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

And the trigger is attached BEFORE UPDATE on a table. What is the effect on the UPDATE statement?

A The UPDATE proceeds normally without changes.

B The UPDATE is canceled; no row is updated.

C The UPDATE causes a runtime error.

D The UPDATE updates the row with NULL values.

💡 Hint

Returning NULL in a BEFORE trigger means the row is skipped.

In PostgreSQL, if a BEFORE trigger returns NULL, the operation on that row is skipped, so the UPDATE does not happen for that row.

📝 Syntax

advanced

Correct syntax for BEFORE INSERT trigger function

Which of the following is the correct syntax for a PostgreSQL BEFORE INSERT trigger function that modifies the NEW row?

A CREATE FUNCTION trg_func() RETURNS TRIGGER AS $$ BEGIN NEW.col := 1; RETURN NEW; END; $$ LANGUAGE plpgsql;

B CREATE FUNCTION trg_func() RETURNS VOID AS $$ BEGIN NEW.col := 1; END; $$ LANGUAGE plpgsql;

C CREATE FUNCTION trg_func() RETURNS TRIGGER AS $$ BEGIN UPDATE table SET col = 1; RETURN NEW; END; $$ LANGUAGE plpgsql;

D CREATE FUNCTION trg_func() RETURNS TRIGGER AS $$ BEGIN NEW.col = 1; RETURN OLD; END; $$ LANGUAGE plpgsql;

💡 Hint

BEFORE triggers must return the modified NEW row.

Option A correctly defines a trigger function returning TRIGGER, modifies NEW, and returns NEW. Option A returns VOID which is invalid. Option A tries to update table inside trigger which is not correct. Option A returns OLD which cancels the insert.

❓ optimization

advanced

Optimizing BEFORE INSERT trigger to avoid recursion

You have a BEFORE INSERT trigger on a table that modifies the NEW row and then inserts into the same table inside the trigger function. What problem can this cause?

How can you optimize or fix this behavior?

A It causes deadlock; fix by adding explicit locks.

B It causes no problem; triggers can safely insert into same table.

C It causes syntax error; fix by changing trigger to AFTER INSERT.

D It causes infinite recursion; fix by using a condition to skip trigger on internal inserts.

💡 Hint

Triggers firing themselves repeatedly cause recursion.

Inserting into the same table inside a BEFORE INSERT trigger causes the trigger to call itself repeatedly, causing infinite recursion. To fix, add a condition to skip the trigger logic for internal inserts.

🧠 Conceptual

expert

Behavior of BEFORE trigger with multiple rows in PostgreSQL

In PostgreSQL, if a BEFORE INSERT trigger is defined FOR EACH ROW, and an INSERT statement inserts multiple rows at once, how does the trigger behave?

Choose the correct statement.

A The trigger fires once per row inserted, each time with that row's NEW data.

B The trigger fires once for the entire statement with all rows in NEW.

C The trigger fires only once for the first row inserted.

D The trigger fires once per column of each row inserted.

💡 Hint

FOR EACH ROW triggers run once per row.

BEFORE INSERT triggers defined FOR EACH ROW execute once for every row inserted, allowing modification of each row individually.

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