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'.

Practice

1. What is the main purpose of a trigger in PostgreSQL for data validation?

easy

A. To create new tables based on existing ones

B. To speed up query execution by indexing data

C. To automatically check and enforce rules on data before it is saved

D. To backup the database automatically

Solution

1. Step 1: Understand trigger role

   Triggers run automatically when data changes, allowing checks on data.

2. Step 2: Identify validation purpose

   Data validation means checking data correctness before saving it.

3. Final Answer:

   To automatically check and enforce rules on data before it is saved -> Option C

4. Quick Check:

   Trigger = automatic data check [OK]

Hint: Triggers run automatically to check data before saving [OK]

Common Mistakes:

• Thinking triggers speed up queries
• Confusing triggers with backups
• Assuming triggers create tables

2. Which of the following is the correct way to declare a BEFORE INSERT trigger in PostgreSQL?

easy

A. CREATE TRIGGER trg BEFORE INSERT ON table_name EXECUTE FUNCTION func_name;

B. CREATE TRIGGER trg AFTER INSERT ON table_name EXECUTE PROCEDURE func_name();

C. CREATE TRIGGER trg BEFORE INSERT ON table_name EXECUTE PROCEDURE func_name();

D. CREATE TRIGGER trg BEFORE INSERT ON table_name EXECUTE FUNCTION func_name();

Solution

1. Step 1: Recall PostgreSQL trigger syntax

   PostgreSQL uses EXECUTE FUNCTION for triggers since version 11.

2. Step 2: Identify correct timing and syntax

   BEFORE INSERT triggers run before inserting data; syntax must match.

3. Final Answer:

   CREATE TRIGGER trg BEFORE INSERT ON table_name EXECUTE FUNCTION func_name(); -> Option D

4. Quick Check:

   BEFORE INSERT + EXECUTE FUNCTION = CREATE TRIGGER trg BEFORE INSERT ON table_name EXECUTE FUNCTION func_name(); [OK]

Hint: Use EXECUTE FUNCTION for triggers in PostgreSQL 11+ [OK]

Common Mistakes:

• Using EXECUTE PROCEDURE instead of EXECUTE FUNCTION
• Confusing BEFORE and AFTER timing
• Missing parentheses after function name

3. Given this trigger function to prevent negative prices:

CREATE FUNCTION check_price() RETURNS trigger AS $$ BEGIN IF NEW.price < 0 THEN RAISE EXCEPTION 'Price cannot be negative'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

What happens if you try to insert a row with price = -5?

medium

A. An error is raised and the insert is stopped

B. The row is inserted with price -5

C. The price is automatically set to 0

D. The trigger is ignored and insert proceeds

Solution

1. Step 1: Analyze trigger function logic

   The function checks if NEW.price is less than 0 and raises an exception if true.

2. Step 2: Understand RAISE EXCEPTION effect

   RAISE EXCEPTION stops the operation and returns an error to the user.

3. Final Answer:

   An error is raised and the insert is stopped -> Option A

4. Quick Check:

   Negative price triggers error [OK]

Hint: RAISE EXCEPTION stops insert on invalid data [OK]

Common Mistakes:

• Assuming data is inserted anyway
• Thinking price auto-corrects
• Ignoring trigger effects

4. You wrote this trigger function:

CREATE FUNCTION validate_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;

But when inserting age = 15, no error occurs. What is the likely mistake?

medium

A. RAISE EXCEPTION syntax is incorrect

B. The trigger is not attached to the table

C. The function does not return NEW

D. The trigger function is missing LANGUAGE plpgsql

Solution

1. Step 1: Check function correctness

   The function correctly raises exception and returns NEW, syntax is fine.

2. Step 2: Consider trigger attachment

   If no error occurs, likely the trigger is not linked to the table to run the function.

3. Final Answer:

   The trigger is not attached to the table -> Option B

4. Quick Check:

   Trigger must be attached to run function [OK]

Hint: Attach trigger to table to activate validation [OK]

Common Mistakes:

• Forgetting to create the trigger after function
• Assuming function runs without trigger
• Misreading RAISE EXCEPTION syntax

5. You want to ensure that a user's email is unique and not empty using a trigger. Which approach correctly combines data validation and uniqueness check in PostgreSQL?

hard

A. Create a BEFORE INSERT OR UPDATE trigger that raises exception if NEW.email is empty or exists in the table

B. Use a UNIQUE constraint on email column only, no trigger needed

C. Create an AFTER INSERT trigger that deletes duplicates after insertion

D. Use a trigger that sets empty emails to a default value

Solution

1. Step 1: Understand validation needs

   Email must be non-empty and unique before saving data.

2. Step 2: Choose trigger timing and logic

   BEFORE INSERT OR UPDATE trigger can check NEW.email and query table for duplicates, raising exception if invalid.

3. Final Answer:

   Create a BEFORE INSERT OR UPDATE trigger that raises exception if NEW.email is empty or exists in the table -> Option A

4. Quick Check:

   Validate and check uniqueness before insert/update [OK]

Hint: Use BEFORE trigger to check and stop invalid data [OK]

Common Mistakes:

• Relying only on UNIQUE constraint without empty check
• Using AFTER trigger to fix duplicates (too late)
• Setting default instead of raising error