\connect bom

--create table bom(PartID INTEGER primary key, SuperPartID INTEGER references bom(PartID) on delete cascade, price INTEGER not null);

-- Critical operation(s): UPDATE, INSERT

CREATE OR REPLACE FUNCTION check_children_function() RETURNS trigger AS $check_children$
    DECLARE
        total integer;
    BEGIN

        Select count(*) INTO total from bom where SuperPartID = NEW.superPartID and NEW.SuperPartID != null;
        RAISE EXCEPTION 'TRIGGER GOT CALLED total was %', total;
        if(total >= 5) THEN
            RAISE EXCEPTION 'This part already has 5 subparts.';
        END IF;
        RETURN NEW;
    END
$check_children$ LANGUAGE plpgsql;

DROP TRIGGER check_children ON bom;

CREATE TRIGGER check_children BEFORE UPDATE OR INSERT ON bom FOR EACH ROW EXECUTE PROCEDURE check_children_function();