NEW and OLD record access in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to reference the new record in a trigger function.

PostgreSQL

RETURN [1];

Drag options to blanks, or click blank then click option'

AOLD

BNEXT

CCURRENT

DNEW

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD instead of NEW when accessing the new record.

Using CURRENT which is not valid in this context.

In PostgreSQL triggers, NEW refers to the new record being inserted or updated.

2fill in blank

medium

Complete the code to reference the old record in a DELETE trigger.

PostgreSQL

RAISE NOTICE 'Deleted user: %', [1].username;

Drag options to blanks, or click blank then click option'

AOLD

BNEW

CCURRENT

DNEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW in a DELETE trigger which causes errors.

Confusing OLD with CURRENT.

In DELETE triggers, OLD contains the record being deleted.

3fill in blank

hard

Fix the error in the trigger function to correctly access the old record.

PostgreSQL

IF [1] IS NULL THEN
  RAISE EXCEPTION 'Old record missing';
END IF;

Drag options to blanks, or click blank then click option'

ANEW

BCURRENT

COLD

DNEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Checking NEW instead of OLD causing logic errors.

Using CURRENT which is not a trigger variable.

To check if the old record exists, use OLD. NEW is not valid here.

4fill in blank

hard

Fill both blanks to compare old and new values in an UPDATE trigger.

PostgreSQL

IF [1].email <> [2].email THEN
  RAISE NOTICE 'Email changed';
END IF;

Drag options to blanks, or click blank then click option'

AOLD

BNEW

CCURRENT

DNEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping OLD and NEW causing wrong comparisons.

Using CURRENT which is invalid here.

Use OLD to access the previous value and NEW for the updated value.

5fill in blank

hard

Fill all three blanks to log changes in a trigger function.

PostgreSQL

INSERT INTO audit_log(user_id, old_email, new_email) VALUES ([1].id, [2].email, [3].email);

Drag options to blanks, or click blank then click option'

AOLD

BNEW

CCURRENT

DNEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW for old values causing incorrect logs.

Using CURRENT or NEXT which are invalid.

Use OLD for previous values and NEW for new values in audit logging.

Practice

1. In a PostgreSQL trigger function, which record variable would you use to access the new row data after an INSERT operation?

easy

A. PREVIOUS

B. NEW

C. CURRENT

D. OLD

Solution

1. Step 1: Understand trigger timing for INSERT

   For an INSERT operation, the new row is being added, so the trigger can access the new data using the NEW record.

2. Step 2: Identify correct record variable

   The OLD record is not available for INSERT because there is no previous row. Therefore, NEW is used to access the inserted row.

3. Final Answer:

   NEW -> Option B

4. Quick Check:

   INSERT uses NEW = D [OK]

Hint: Use NEW for inserted or updated rows, OLD for deleted or old rows [OK]

Common Mistakes:

• Using OLD in INSERT triggers
• Confusing NEW and OLD for UPDATE
• Assuming CURRENT or PREVIOUS exist

2. Which of the following is the correct syntax to access the old value of a column named price inside a DELETE trigger in PostgreSQL?

easy

A. OLD.price

B. NEW.price

C. OLD->price

D. NEW->price

Solution

1. Step 1: Identify record variable for DELETE

   In a DELETE trigger, the row is being removed, so the old data is accessible via OLD.

2. Step 2: Use correct syntax for column access

   PostgreSQL uses dot notation to access columns in record variables, so OLD.price is correct.

3. Final Answer:

   OLD.price -> Option A

4. Quick Check:

   DELETE uses OLD.column = A [OK]

Hint: Use dot notation with OLD for deleted row columns [OK]

Common Mistakes:

• Using NEW in DELETE triggers
• Using arrow (->) instead of dot for record access
• Confusing syntax for JSON operators

3. Consider this trigger function snippet for an UPDATE operation:

IF NEW.quantity < OLD.quantity THEN
  RAISE NOTICE 'Quantity decreased from % to %', OLD.quantity, NEW.quantity;
END IF;

What will be the output if the old quantity was 10 and the new quantity is 7?

medium

A. Quantity decreased from 10 to 7

B. Quantity decreased from 7 to 10

C. No output

D. Syntax error

Solution

1. Step 1: Understand the condition in the IF statement

   The condition checks if the new quantity is less than the old quantity. Here, 7 < 10 is true.

2. Step 2: Analyze the RAISE NOTICE output

   The message prints the old quantity first, then the new quantity, so it will output: 'Quantity decreased from 10 to 7'.

3. Final Answer:

   Quantity decreased from 10 to 7 -> Option A

4. Quick Check:

   NEW < OLD triggers notice = A [OK]

Hint: Compare NEW and OLD values carefully in UPDATE triggers [OK]

Common Mistakes:

• Mixing up NEW and OLD values in output
• Assuming no output when condition is true
• Confusing syntax of RAISE NOTICE

4. You wrote this trigger function for DELETE:

CREATE FUNCTION trg_delete_check() RETURNS trigger AS $$
BEGIN
  IF NEW.id IS NULL THEN
    RAISE EXCEPTION 'ID cannot be null';
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

What is the error in this function?

medium

A. RAISE EXCEPTION syntax is wrong

B. RETURN OLD is invalid in DELETE triggers

C. Using NEW in a DELETE trigger where only OLD is available

D. Function must return VOID, not trigger

Solution

1. Step 1: Check record variables in DELETE triggers

   In DELETE triggers, the NEW record is not available because no new row is inserted or updated.

2. Step 2: Identify incorrect usage of NEW

   The function incorrectly uses NEW.id, which causes an error. It should use OLD.id instead.

3. Final Answer:

   Using NEW in a DELETE trigger where only OLD is available -> Option C

4. Quick Check:

   DELETE triggers have OLD, not NEW = C [OK]

Hint: Use OLD in DELETE triggers; NEW is unavailable [OK]

Common Mistakes:

• Using NEW in DELETE triggers
• Returning OLD incorrectly
• Misunderstanding trigger return types

5. You want to create a trigger that logs changes to a salary column only when the salary is updated to a higher value. Which trigger condition and record access correctly implements this in PostgreSQL?

hard

A. IF OLD.salary > NEW.salary THEN INSERT INTO log_table VALUES (OLD.id, OLD.salary); END IF;

B. IF NEW.salary < OLD.salary THEN INSERT INTO log_table VALUES (NEW.id, NEW.salary); END IF;

C. IF NEW.salary = OLD.salary THEN INSERT INTO log_table VALUES (NEW.id, NEW.salary); END IF;

D. IF NEW.salary > OLD.salary THEN INSERT INTO log_table VALUES (NEW.id, NEW.salary); END IF;

Solution

1. Step 1: Understand the condition for logging

   The trigger should log only when the new salary is greater than the old salary, so the condition is NEW.salary > OLD.salary.

2. Step 2: Use correct record variables for UPDATE

   The new salary and id come from NEW because the row is updated with new values.

3. Final Answer:

   IF NEW.salary > OLD.salary THEN INSERT INTO log_table VALUES (NEW.id, NEW.salary); END IF; -> Option D

4. Quick Check:

   Log when NEW > OLD salary = B [OK]

Hint: Compare NEW and OLD to detect increases, then log NEW data [OK]

Common Mistakes:

• Reversing NEW and OLD in condition
• Logging when salary decreases
• Using equality instead of greater than