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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics