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.