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.