Trigger for data validation in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a trigger function enforcing age validation

Consider a PostgreSQL table users with a trigger that prevents inserting rows where age is less than 18. What will happen if you run the following insert?

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 16);

PostgreSQL

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

CREATE TRIGGER age_check BEFORE INSERT ON users
FOR EACH ROW EXECUTE FUNCTION check_age();

A The insert fails with an error: 'Age must be at least 18'.

B The insert is ignored silently without error.

C The insert succeeds but age is automatically set to 18.

D The row is inserted successfully with age 16.

💡 Hint

Triggers can raise exceptions to stop invalid data from being inserted.

The trigger function checks the age before insert. If age is less than 18, it raises an exception, which stops the insert and returns an error.

📝 Syntax

intermediate

Identify the syntax error in this trigger function

Which option contains the correct syntax for a PostgreSQL trigger function that validates a non-empty username field before insert?

PostgreSQL

CREATE OR REPLACE FUNCTION validate_username() RETURNS trigger AS $$
BEGIN
  IF NEW.username = '' THEN
    RAISE EXCEPTION 'Username cannot be empty';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

A CREATE OR REPLACE FUNCTION validate_username() RETURNS trigger AS $$ BEGIN IF NEW.username = '' THEN RAISE 'Username cannot be empty'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

B CREATE OR REPLACE FUNCTION validate_username() RETURNS trigger AS $$ BEGIN IF NEW.username == '' THEN RAISE EXCEPTION 'Username cannot be empty'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

C CREATE FUNCTION validate_username() RETURNS trigger AS $$ BEGIN IF NEW.username = '' THEN RAISE EXCEPTION 'Username cannot be empty'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

D CREATE OR REPLACE FUNCTION validate_username() RETURNS trigger AS $$ BEGIN IF NEW.username = '' THEN RAISE EXCEPTION 'Username cannot be empty'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

💡 Hint

Check the use of 'CREATE OR REPLACE FUNCTION' and the correct comparison operator.

Option D uses the correct syntax: 'CREATE OR REPLACE FUNCTION', single '=' for comparison, and 'RAISE EXCEPTION' to throw errors.

❓ optimization

advanced

Optimizing a trigger for multiple column validations

You want to create a trigger that validates both email and phone columns on insert or update. Which option is the most efficient way to write the trigger function?

A Create one AFTER INSERT OR UPDATE trigger that checks email and phone and deletes the row if invalid.

B Create one trigger function that checks both email and phone in a single BEFORE INSERT OR UPDATE trigger.

C Create one trigger function that checks email on BEFORE INSERT and phone on BEFORE UPDATE separately.

D Create two separate triggers: one for email validation and one for phone validation, both firing BEFORE INSERT OR UPDATE.

💡 Hint

Minimize the number of triggers for better performance.

Combining validations in a single BEFORE trigger reduces overhead and ensures all validations happen before data is saved.

🔧 Debug

advanced

Debugging a trigger that does not prevent invalid data

A trigger is supposed to prevent inserting rows with salary less than 0, but invalid rows are still inserted. Which option explains the most likely cause?

PostgreSQL

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

CREATE TRIGGER salary_check BEFORE INSERT ON employees
FOR EACH ROW EXECUTE FUNCTION check_salary();

A The trigger function returns NULL, which skips the insert instead of raising an error.

B The trigger is AFTER INSERT, so it cannot prevent the insert.

C The trigger function uses RAISE EXCEPTION incorrectly and does not stop the insert.

D The trigger is defined FOR EACH STATEMENT instead of FOR EACH ROW.

💡 Hint

Check what the trigger function returns to control the insert.

Returning NULL in a BEFORE trigger tells PostgreSQL to skip the insert for that row silently, so no error is raised and the row is not inserted. But if the row is still inserted, the trigger might not be firing correctly or the return value is mishandled.

🧠 Conceptual

expert

Understanding trigger timing and data validation

Which statement best describes why a BEFORE trigger is preferred over an AFTER trigger for data validation in PostgreSQL?

A AFTER triggers run before the data is inserted, so they can modify data before saving.

B BEFORE triggers run after the data is inserted, so they can rollback changes if invalid.

C BEFORE triggers run before the data is inserted, allowing validation and prevention of invalid data before it reaches the table.

D AFTER triggers prevent invalid data by stopping the insert before it happens.

💡 Hint

Think about when the data is checked relative to insertion.

BEFORE triggers execute before the data is saved, so they can check and stop invalid data from being inserted. AFTER triggers run after insertion, so they cannot prevent the insert but can react to it.