Trigger function creation in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger function that returns the new row.

PostgreSQL

CREATE FUNCTION trg_func() RETURNS trigger AS $$ BEGIN RETURN [1]; END; $$ LANGUAGE plpgsql;

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

ANULL

BOLD

CNEW_ROW

DNEW

Attempts:

3 left

💡 Hint

Common Mistakes

Returning OLD instead of NEW causes the trigger to use the old row.

Returning NULL cancels the operation.

The trigger function must return NEW to indicate the new row after an insert or update.

2fill in blank

medium

Complete the code to specify the language of the trigger function as PL/pgSQL.

PostgreSQL

CREATE FUNCTION trg_func() RETURNS trigger AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE [1];

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

Aplpgsql

Bsql

Cplpythonu

Dc

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'sql' language does not support procedural code like BEGIN...END.

Using 'c' requires compiled code and is not typical for simple triggers.

The trigger function language must be plpgsql for procedural SQL in PostgreSQL.

3fill in blank

hard

Fix the error in the trigger function header to correctly declare it returns a trigger.

PostgreSQL

CREATE FUNCTION trg_func() RETURNS [1] AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql;

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

Atrigger

Brecord

Cvoid

Dtable

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'record' or 'void' causes errors when creating triggers.

Using 'table' is invalid as a return type for functions.

Trigger functions must return trigger type in PostgreSQL.

4fill in blank

hard

Fill both blanks to create a trigger function that logs the old and new values.

PostgreSQL

CREATE FUNCTION log_changes() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'Old: %, New: %', [1], [2]; RETURN NEW; END; $$ LANGUAGE plpgsql;

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

AOLD

BNEW

COLD_ROW

DNEW_ROW

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD_ROW or NEW_ROW are invalid identifiers in PostgreSQL triggers.

Swapping OLD and NEW changes the meaning of the logged data.

The trigger function uses OLD and NEW to access the row before and after the change.

5fill in blank

hard

Fill all three blanks to create a trigger function that prevents deletion by raising an exception.

PostgreSQL

CREATE FUNCTION prevent_delete() RETURNS trigger AS $$ BEGIN IF TG_OP = [1] THEN RAISE EXCEPTION [2]; RETURN [3]; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

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

A'DELETE'

B'Cannot delete this row!'

COLD

DNEW

Attempts:

3 left

💡 Hint

Common Mistakes

Returning NEW in a delete trigger causes errors.

Not quoting the operation name 'DELETE' causes syntax errors.

The function checks if the operation is DELETE, raises an exception with a message to prevent deletion, and returns OLD.

Practice

1. What is the correct return type for a trigger function in PostgreSQL?

easy

A. void

B. trigger

C. integer

D. boolean

Solution

1. Step 1: Understand trigger function requirements

   Trigger functions must return a special type that PostgreSQL recognizes for triggers.

2. Step 2: Identify the correct return type

   The return type for trigger functions is always trigger, not standard types like void or integer.

3. Final Answer:

   trigger -> Option B

4. Quick Check:

   Trigger functions return type = trigger [OK]

Hint: Trigger functions always return type 'trigger' [OK]

Common Mistakes:

• Using void or integer as return type
• Forgetting to specify return type
• Confusing trigger function with normal function

2. Which of the following is the correct way to start a trigger function in PostgreSQL?

easy

A. CREATE FUNCTION my_trigger() RETURNS trigger AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION my_trigger() RETURNS boolean AS $$ BEGIN RETURN TRUE; END; $$ LANGUAGE plpgsql;

C. CREATE TRIGGER my_trigger() RETURNS trigger AS $$ BEGIN RETURN OLD; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION my_trigger() RETURNS void AS $$ BEGIN END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check syntax for trigger function creation

   Trigger functions must be created with CREATE FUNCTION, return type trigger, and use plpgsql language.

2. Step 2: Identify correct function body and return statement

   Trigger functions must return NEW or OLD row, so RETURN NEW; is correct here.

3. Final Answer:

   CREATE FUNCTION my_trigger() RETURNS trigger AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   Trigger function syntax = CREATE FUNCTION my_trigger() RETURNS trigger AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql; [OK]

Hint: Trigger functions use RETURNS trigger and RETURN NEW or OLD [OK]

Common Mistakes:

• Using RETURNS void instead of trigger
• Using CREATE TRIGGER instead of CREATE FUNCTION
• Returning boolean or void instead of NEW or OLD

3. Given this trigger function, what will be the result when a new row is inserted?

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;

medium

A. The row is inserted regardless of age

B. The function returns NULL causing an error

C. The row is always rejected

D. The row is inserted only if age is 18 or older

Solution

1. Step 1: Analyze the condition in the trigger function

   The function checks if NEW.age < 18 and raises an exception if true.

2. Step 2: Understand effect of exception and return

   If age is less than 18, insertion stops with error. Otherwise, RETURN NEW; allows insertion.

3. Final Answer:

   The row is inserted only if age is 18 or older -> Option D

4. Quick Check:

   Exception blocks underage inserts = The row is inserted only if age is 18 or older [OK]

Hint: Exception stops insert; RETURN NEW allows it [OK]

Common Mistakes:

• Thinking all rows insert regardless
• Assuming RETURN NEW inserts row without checks
• Confusing RAISE EXCEPTION with warnings

4. Identify the error in this trigger function code:

CREATE FUNCTION update_timestamp() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW
END;
$$ LANGUAGE plpgsql;

medium

A. Cannot assign to NEW in trigger functions

B. Incorrect return type, should be void

C. Missing semicolon after RETURN NEW

D. Missing BEGIN keyword

Solution

1. Step 1: Check syntax for statements inside function

   Each statement must end with a semicolon in PL/pgSQL.

2. Step 2: Identify missing semicolon

   The line RETURN NEW lacks a semicolon at the end, causing syntax error.

3. Final Answer:

   Missing semicolon after RETURN NEW -> Option C

4. Quick Check:

   PL/pgSQL statements end with semicolon [OK]

Hint: Check semicolons after each statement [OK]

Common Mistakes:

• Forgetting semicolon after RETURN NEW
• Thinking NEW cannot be assigned
• Confusing return type with void

5. You want to create a trigger function that automatically sets a column last_modified to the current timestamp whenever a row is updated. Which of the following trigger function definitions correctly achieves this?

hard

A. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN OLD.last_modified := NOW(); RETURN OLD; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION set_last_modified() RETURNS void AS $$ BEGIN NEW.last_modified := NOW(); END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Identify correct assignment operator in PL/pgSQL

   PL/pgSQL uses := for assignment, not =.

2. Step 2: Check return type and returned row

   Trigger functions must return type trigger and return NEW for BEFORE UPDATE triggers.

3. Step 3: Evaluate each option

   CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; uses correct assignment :=, returns NEW, and has correct return type. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; uses = which is invalid for assignment. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN OLD.last_modified := NOW(); RETURN OLD; END; $$ LANGUAGE plpgsql; assigns to OLD which is read-only. CREATE FUNCTION set_last_modified() RETURNS void AS $$ BEGIN NEW.last_modified := NOW(); END; $$ LANGUAGE plpgsql; returns void which is invalid.

4. Final Answer:

   CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; -> Option A

5. Quick Check:

   Use := for assignment and return NEW [OK]

Hint: Use := for assignment and return NEW in trigger [OK]

Common Mistakes:

• Using = instead of := for assignment
• Returning OLD instead of NEW
• Using void return type for trigger function