Trigger for data validation in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger function that raises an exception if the salary is less than 0.

PostgreSQL

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

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

A>=

B>

C=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' instead of '<' causes the check to fail.

Using '=' only checks for zero, not negative values.

The trigger function checks if the new salary is less than 0 to raise an exception.

2fill in blank

medium

Complete the code to create a trigger that calls the check_salary function before insert on employees table.

PostgreSQL

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

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

Acheck_salary

Bvalidate_salary

Csalary_trigger

Draise_salary

Attempts:

3 left

💡 Hint

Common Mistakes

Using a function name that does not exist causes an error.

Misspelling the function name.

The trigger must call the function named check_salary to validate the salary.

3fill in blank

hard

Fix the error in the trigger function to correctly check if NEW.age is less than 18.

PostgreSQL

CREATE FUNCTION check_age() RETURNS trigger AS $$ BEGIN IF NEW.age [1] 18 THEN RAISE EXCEPTION 'Age must be at least 18'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

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

A<

B<=

C>=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>=' checks for age 18 or more, which is incorrect here.

Using '<=' includes 18, which is allowed.

The condition should check if age is less than 18 to raise an exception.

4fill in blank

hard

Fill both blanks to create a trigger that fires before update on the users table and calls the check_age function.

PostgreSQL

CREATE TRIGGER [1] BEFORE [2] ON users FOR EACH ROW EXECUTE FUNCTION check_age();

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

Aage_check

BINSERT

CUPDATE

Duser_update

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'INSERT' instead of 'UPDATE' changes when the trigger fires.

Using a generic trigger name that is unclear.

The trigger name is age_check and it fires before UPDATE on the users table.

5fill in blank

hard

Fill all three blanks to create a trigger function that prevents inserting a user with an empty username or NULL email.

PostgreSQL

CREATE FUNCTION validate_user() RETURNS trigger AS $$ BEGIN IF NEW.username = [1] OR NEW.email IS [2] THEN RAISE EXCEPTION [3]; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

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

A''

BNULL

C'Username and email cannot be empty or null'

D'Invalid user data'

Attempts:

3 left

💡 Hint

Common Mistakes

Using double quotes instead of single quotes for empty string.

Using '=' instead of 'IS' to check for NULL.

Providing a generic exception message that is unclear.

The function checks if username is empty string '' or email is NULL, then raises an exception with the message 'Username and email cannot be empty or null'.