add a constraint or a trigger to verify value before insert or update for SQL

Question

i have 2 tables : parent and child, there is a certain column in the parent table defining max_bill_value, and a column in child table defining bill_value. the condition here is :

sum(bill_value)<max_bill_value 

the trigger body will be something like this

SELECT bill_value INTO max_bill_value FROM parent_table WHERE id=:NEW.parent_id; 

SELECT SUM(bill_value) INTO sum_bill_value FROM child_table where parent_id=:NEW.parent_id; 

if max_bill_value < sum_bill_value then raise_application_error(-20001, 'ERROR!!!');
end if;

my first solution was the use of a trigger, which was impossible because of the

"ORA-04091: table name is mutating, trigger/function may not see it." message.

so what would be a simple solution for this?

Answer 1

Presumably you are using a row level trigger. Convert it to a statement level trigger starting by removing the piece FOR EACH ROW in order to prevent getting a mutating table error such as

CREATE OR REPLACE TRIGGER trg_chk_bill
BEFORE INSERT ON child_table
--FOR EACH ROW
DECLARE
  sum_bill_value child_table.bill_value%TYPE;
  max_bill_value parent_table.bill_value%TYPE; 
BEGIN
  SELECT bill_value
    INTO max_bill_value  
    FROM parent_table;

  SELECT SUM(bill_value)
    INTO sum_bill_value  
    FROM child_table;
    
  IF sum_bill_value > max_bill_value THEN
     RAISE_APPLICATION_ERROR(-20333,'MaX bill value is exceeded'); 
  END IF;  
        
END;
/