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.