NEW and OLD record access in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of trigger function using OLD and NEW

Consider a PostgreSQL trigger function that logs changes on a table. What will be the output of the following function when an UPDATE occurs changing column price from 100 to 120?

CREATE OR REPLACE FUNCTION log_price_change() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Price changed from % to %', OLD.price, NEW.price;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

A Price changed from 100 to 120

B Price changed from 100 to NULL

C Price changed from NULL to 120

D Price changed from 120 to 100

💡 Hint

OLD contains the row before the update, NEW contains the row after.

In an UPDATE trigger, OLD holds the original row values, and NEW holds the new values. So the price changed from 100 (OLD.price) to 120 (NEW.price).

🧠 Conceptual

intermediate

Understanding OLD and NEW in DELETE triggers

In a DELETE trigger in PostgreSQL, which of the following statements is true about the availability of OLD and NEW records?

A Both OLD and NEW contain the row being deleted

B NEW contains the row being deleted; OLD is NULL

C OLD contains the row being deleted; NEW is NULL

D Both OLD and NEW are NULL

💡 Hint

Think about what data exists before and after a DELETE.

When a row is deleted, the OLD record holds the row before deletion, but there is no NEW record because the row no longer exists after deletion.

📝 Syntax

advanced

Identify the syntax error in trigger function using OLD and NEW

Which option contains a syntax error in this PostgreSQL trigger function that tries to compare OLD and NEW values?

CREATE OR REPLACE FUNCTION check_update() RETURNS trigger AS $$
BEGIN
  IF NEW.value <> OLD.value THEN
    RAISE NOTICE 'Value changed';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

A IF (NEW.value <> OLD.value) THEN

B IF NEW.value <> OLD.value

C IF NEW.value <> OLD.value THEN

D IF NEW.value != OLD.value THEN

💡 Hint

Check the syntax of IF statements in PL/pgSQL.

In PL/pgSQL, IF statements require THEN keyword after the condition. Option B misses THEN, causing a syntax error.

❓ optimization

advanced

Optimizing trigger function to avoid unnecessary updates

You have a BEFORE UPDATE trigger that updates a timestamp column only if certain columns changed. Which approach is best to avoid unnecessary updates?

Options show different ways to compare OLD and NEW records.

A

IF NEW.col1 <> OLD.col1 OR NEW.col2 <> OLD.col2 THEN
  NEW.updated_at = now();
END IF;

B NEW.updated_at = now();

C

IF OLD IS DISTINCT FROM NEW THEN
  NEW.updated_at = now();
END IF;

D

IF NEW IS DISTINCT FROM OLD THEN
  NEW.updated_at = now();
END IF;

💡 Hint

PostgreSQL supports IS DISTINCT FROM for row comparisons.

Using 'NEW IS DISTINCT FROM OLD' compares entire rows safely including NULLs, avoiding multiple OR conditions and unnecessary updates.

🔧 Debug

expert

Debugging a trigger function that causes infinite recursion

A BEFORE UPDATE trigger function updates the same table's row by setting NEW.updated_at = now(). However, this causes infinite recursion and stack overflow. What is the best fix?

A Add a condition to update NEW.updated_at only if OLD.updated_at is different

B Disable triggers on the table temporarily during update

C Change the trigger to AFTER UPDATE instead of BEFORE UPDATE

D Use a separate function to update updated_at outside the trigger

💡 Hint

Think about what causes the trigger to fire repeatedly.

Updating NEW.updated_at unconditionally causes the trigger to fire again. Adding a condition to update only if the timestamp changed prevents recursion.