How to add Check constraint in Oracle sql to check for the percentage not greater than 100

Question

I have a query, Suppose I have a Table called Behaviour and in that I have two columns one is Product_name and second is proportion. Suppose, User has entered 3 records Product1-> A with proportion 50, Product 1-> A with Proportion 30, Product 1->A with Proportion 20. I want a check constraint in my table where I can check a user should not enter the proportion >100 for a same product.

Could you please help me in that.

Answer 1

A trigger can do that (with some limitations), but something among the lines:

CREATE OR REPLACE TRIGGER some_table_portion_trig
  BEFORE INSERT OR UPDATE OF proportion ON some_table
  FOR EACH ROW
DECLARE 
  l_sum_portion NUMBER;
BEGIN
  SELECT SUM(portion) + :new.proportion
    INTO l_sum_portion
    FROM some_table
   WHERE product_name = :new.product_name;

   IF l_sum_portion > 100 THEN
     RAISE_APPLICATION_ERROR(-20001, 'Sum of portions exceedds 100 for product_name '|| :new.product_name);
   END IF;
EXCEPTION WHEN OTHERS THEN
  NULL;
END;