Trigger for data validation in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a trigger in PostgreSQL?

A trigger is a special procedure that automatically runs when certain events happen in the database, like inserting or updating data.

beginner

How can triggers help with data validation?

Triggers can check data before it is saved and stop the action if the data does not meet rules, ensuring only valid data is stored.

intermediate

What is the difference between BEFORE and AFTER triggers?

BEFORE triggers run before the data change happens, so they can prevent invalid data. AFTER triggers run after the change, useful for logging or actions that don't block data.

intermediate

Write a simple trigger function in PostgreSQL that checks if a new user's age is at least 18.

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

intermediate

How do you attach a trigger function to a table for INSERT events?

Use CREATE TRIGGER with the table name, specify BEFORE INSERT, and link the trigger function. Example: CREATE TRIGGER validate_age BEFORE INSERT ON users FOR EACH ROW EXECUTE FUNCTION check_age();

What event would you use a BEFORE trigger for?

A To create a new table

B To log data after it is inserted

C To delete data automatically

D To check data before it is inserted or updated

BEFORE triggers run before data changes, so they are perfect for checking or validating data.

Which language is commonly used to write PostgreSQL trigger functions?

A PL/pgSQL

B Python

C JavaScript

D HTML

PL/pgSQL is the built-in procedural language for writing trigger functions in PostgreSQL.

What happens if a trigger raises an exception during data validation?

A The data change continues anyway

B The data change is stopped and an error is shown

C The database restarts

D The trigger is ignored

Raising an exception in a trigger stops the data change and returns an error to the user.

Which statement correctly creates a trigger for validating data on INSERT?

A CREATE TRIGGER validate BEFORE UPDATE ON table EXECUTE FUNCTION validate_func();

B CREATE TRIGGER validate AFTER DELETE ON table EXECUTE FUNCTION validate_func();

C CREATE TRIGGER validate BEFORE INSERT ON table FOR EACH ROW EXECUTE FUNCTION validate_func();

D CREATE TRIGGER validate ON table FOR EACH STATEMENT EXECUTE FUNCTION validate_func();

The correct syntax includes BEFORE INSERT, FOR EACH ROW, and EXECUTE FUNCTION.

Triggers are useful for:

A Automatically enforcing rules on data changes

B Manually editing data in tables

C Backing up the database

D Creating user accounts

Triggers automatically run code to enforce rules or actions when data changes.

Explain how a trigger can be used to validate data before inserting it into a table.

Think about the timing of the trigger and what happens if data is wrong.

Describe the steps to create and attach a trigger function for data validation in PostgreSQL.

Consider both the function and the trigger creation commands.