Ensure Data Integrity with a Validation Trigger

Data Validation Trigger using Stored Procedure

Write a PostgreSQL query to create a trigger that calls a stored procedure to validate data before insertion or update on a table.

Solution:

-- Create or replace a function named validate_data
CREATE OR REPLACE FUNCTION validate_data() RETURNS TRIGGER AS $$
BEGIN
    -- Check if the new score is outside the valid range (0 to 100)
    IF NEW.score < 0 OR NEW.score > 100 THEN
        -- Raise an exception if the score is out of bounds
        RAISE EXCEPTION 'Score must be between 0 and 100';
    END IF;
    
    -- Return the new row after validation
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger named trg_validate_data
CREATE TRIGGER trg_validate_data
-- Set the trigger to execute before inserting or updating a row in the scores table
BEFORE INSERT OR UPDATE ON scores
-- Apply the trigger to each affected row
FOR EACH ROW EXECUTE FUNCTION validate_data();

Explanation:

• Purpose of the Query:
• The goal is to ensure that data, such as a score, is within a valid range before being committed to the table.
• This shows how to integrate data validation within a trigger using a stored procedure.
• Key Components:
• The stored procedure checks the NEW record's score and raises an exception if it’s out of bounds.
• The trigger fires before both INSERT and UPDATE operations.
• Real-World Application:
• Prevents invalid data entry, thus maintaining data integrity.

Notes:

• The scores table must have a score column defined with a numeric type.
• Validation logic should cover all potential edge cases.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a trigger that validates multiple column constraints before allowing data insertion.
• Write a PostgreSQL query to create a trigger that ensures a numeric field falls within an acceptable range before updating a record.
• Write a PostgreSQL query to create a trigger that validates that a start_date is earlier than an end_date before insert.
• Write a PostgreSQL query to create a trigger that performs cross-table validations before updating critical records.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Data Synchronization Trigger using Stored Procedure.