w3resource

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.



Follow us on Facebook and Twitter for latest update.