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.