Validate Data with a Business Rule Enforcement Trigger
Business Rule Enforcement Trigger using Stored Procedure
Write a PostgreSQL query to create a trigger that calls a stored procedure to enforce a business rule by validating data before insertion.
Solution:
-- Create or replace a function named enforce_business_rule
CREATE OR REPLACE FUNCTION enforce_business_rule() RETURNS TRIGGER AS $$
BEGIN
-- Check if the new row's amount is negative
IF NEW.amount < 0 THEN
-- Raise an exception if the amount is negative
RAISE EXCEPTION 'Amount cannot be negative';
END IF;
-- Return the new row if the condition is not met
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Create a trigger named trg_business_rule
CREATE TRIGGER trg_business_rule
-- Set the trigger to execute before inserting a row into the orders table
BEFORE INSERT ON orders
-- Apply the trigger to each row being inserted
FOR EACH ROW EXECUTE FUNCTION enforce_business_rule();
Explanation:
- Purpose of the Query:
- The goal is to enforce a business rule that prevents negative values for the amount column.
- This shows how to use a stored procedure within a trigger to validate incoming data.
- Key Components:
- The trigger is set to fire before an INSERT operation.
- The stored procedure checks the NEW record's amount and raises an exception if the rule is violated.
- Real-World Application:
- Useful for maintaining data integrity by enforcing business logic at the database level.
Notes:
- The orders table must include an amount column.
- RAISE EXCEPTION halts the transaction if validation fails.
For more Practice: Solve these Related Problems:
- Write a PostgreSQL query to create a trigger that rejects inserts if a numerical field exceeds a preset maximum.
- Write a PostgreSQL query to create a trigger that enforces that discount rates remain within a defined range on insert and update.
- Write a PostgreSQL query to create a trigger that validates that an order total is not less than the sum of its line items before committing.
- Write a PostgreSQL query to create a trigger that only allows records with a status of 'active' if certain conditions are met.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous PostgreSQL Exercise: Recursive Trigger Prevention using Stored Procedure.
Next PostgreSQL Exercise: Conditional Trigger Execution 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