w3resource

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.

Next PostgreSQL Exercise: Archival Trigger using Stored Procedure.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.