BEFORE trigger behavior in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a BEFORE INSERT trigger that calls the function.

PostgreSQL

CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION [1]();

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

Aupdate_employee

Bcheck_salary

Clog_insert

Dbefore_insert_function

Attempts:

3 left

💡 Hint

Common Mistakes

Using a function name that does not exist.

Confusing AFTER triggers with BEFORE triggers.

The trigger must call the function named before_insert_function which is defined to run BEFORE INSERT.

2fill in blank

medium

Complete the code to define a BEFORE UPDATE trigger that modifies the NEW row.

PostgreSQL

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

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

Aupdate_timestamp

Bbefore_update_trigger

Cset_update_time

Dupdate_employee

Attempts:

3 left

💡 Hint

Common Mistakes

Using a function name that does not match the trigger purpose.

Not returning NEW in a BEFORE trigger function.

The function update_timestamp is designed to update the updated_at column before the row is updated.

3fill in blank

hard

Fix the error in the trigger function by completing the missing keyword.

PostgreSQL

CREATE FUNCTION validate_salary() RETURNS trigger AS $$
BEGIN
  IF NEW.salary < 0 THEN
    RAISE EXCEPTION 'Salary cannot be negative';
  [1] NEW;
END;
$$ LANGUAGE plpgsql;

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

AEND

BRAISE

CRETURN

DNEW

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the RETURN statement causes the trigger to fail.

Using RAISE instead of RETURN for returning the row.

In a BEFORE trigger function, you must RETURN NEW to apply the changes to the row.

4fill in blank

hard

Fill both blanks to create a BEFORE DELETE trigger that logs the deleted row.

PostgreSQL

CREATE FUNCTION [1]() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log(table_name, operation, old_data, changed_at)
  VALUES ('employees', 'DELETE', ROW_TO_JSON([2]), NOW());
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

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

Alog_delete

BNEW

COLD

Drecord

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW instead of OLD in a DELETE trigger.

Not returning OLD in a BEFORE DELETE trigger.

The function log_delete logs the old row data before deletion. The OLD keyword refers to the row being deleted.

5fill in blank

hard

Fill all three blanks to create a BEFORE INSERT trigger that sets a default value if NULL.

PostgreSQL

CREATE FUNCTION [1]() RETURNS trigger AS $$
BEGIN
  IF NEW.status IS NULL THEN
    NEW.status = [2];
  END IF;
  RETURN [3];
END;
$$ LANGUAGE plpgsql;

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

Aset_default_status

B'active'

CNEW

D'pending'

Attempts:

3 left

💡 Hint

Common Mistakes

Returning OLD instead of NEW in an INSERT trigger.

Using a wrong default value string.

The function set_default_status sets the status to 'active' if it is NULL, then returns the modified row NEW.