Trigger for data validation in PostgreSQL - Step-by-Step Execution

Concept Flow - Trigger for data validation

Insert/Update/Delete operation

↓

Trigger fires BEFORE/AFTER operation

↓

Trigger function runs

↓

Validation passes

↓

Operation allowed

↓

End

When a data change happens, the trigger runs a function that checks the data. If the data is valid, the change continues; if not, it stops with an error.

Execution Sample

PostgreSQL

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

CREATE TRIGGER check_age BEFORE INSERT OR UPDATE ON persons
FOR EACH ROW EXECUTE FUNCTION validate_age();

This trigger checks that the 'age' field is not negative before inserting or updating a row in the 'persons' table.

Execution Table

Step	Operation	Trigger Fired?	Validation Check	Result	Action
1	Insert person with age=25	Yes	25 < 0 ?	False	Insert allowed
2	Insert person with age=-5	Yes	-5 < 0 ?	True	Error raised, insert aborted
3	Update person age to 30	Yes	30 < 0 ?	False	Update allowed
4	Update person age to -1	Yes	-1 < 0 ?	True	Error raised, update aborted
5	Delete person	No	No validation needed	N/A	Delete allowed

💡 Operations stop or continue based on validation result; errors abort the operation.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5
NEW.age	N/A	25	-5	30	-1	N/A
Validation result	N/A	False	True	False	True	N/A
Operation status	N/A	Allowed	Aborted	Allowed	Aborted	Allowed

Key Moments - 2 Insights

Why does the trigger stop the insert when age is negative?

Because the validation check in the trigger function returns True for NEW.age < 0, it raises an exception which aborts the operation, as shown in execution_table rows 2 and 4.

Does the trigger run on DELETE operations?

No, the trigger is defined for BEFORE INSERT OR UPDATE only, so it does not run on DELETE, as shown in execution_table row 5.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 2 when inserting age -5?

A Insert allowed

B Error raised, insert aborted

C Trigger does not fire

D Age is set to 0 automatically

💡 Hint

Check the 'Result' and 'Action' columns in execution_table row 2.

At which step does the trigger NOT fire?

A Step 1

B Step 3

C Step 5

D Step 4

💡 Hint

Look at the 'Trigger Fired?' column in execution_table.

If the trigger function did not return NEW at the end, what would happen?

A The operation would fail with an error

B The operation would still succeed

C The trigger would run twice

D The data would be inserted with NULL values

💡 Hint

In PostgreSQL triggers, returning NEW is required to allow the operation to proceed.

Concept Snapshot

Trigger for data validation:
- Define a trigger function that checks data conditions.
- Attach trigger BEFORE INSERT or UPDATE on a table.
- If validation fails, raise an exception to stop operation.
- If validation passes, return NEW to continue.
- Triggers help enforce rules automatically on data changes.

Full Transcript

A trigger for data validation in PostgreSQL runs automatically before data changes like insert or update. It calls a function that checks if the new data meets rules, such as age not being negative. If the data is invalid, the function raises an error and stops the change. If valid, it returns the new data to allow the change. This process helps keep data clean and correct without manual checks.