create table D_Inv_Location_Types (
   Inv_Loc_Type_ID integer not null,
   Inv_Loc_Type_Name varchar not null,
   Inv_Loc_Type_Description varchar not null,
   Inv_Loc_Type_Disp_ID_Name varchar null,
   CONSTRAINT DILT_pk PRIMARY KEY(Inv_Loc_Type_ID),
   CONSTRAINT DILT_uk UNIQUE(Inv_Loc_Type_Name)
)
;

insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_description,inv_loc_type_disp_id_name) values(96,'State','These are what make up the United States of America','State');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_description,inv_loc_type_disp_id_name) values(3,'City','This is a big town','City');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_description,inv_loc_type_disp_id_name) values(15,'County','This is a large piece of land that holds numerous settlements including cities and towns.','County ID');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_description,inv_loc_type_disp_id_name) values(174,'Country','This is an area of geographical location that governs itself from a central location. i.e. Like that country Scotland in the Continent of Europe','Country ID');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_description,inv_loc_type_disp_id_name) values(166,'Central Office','CO is the facility where all Telco equipment serving a particular area is located. The CO may host voice and/or data and/or video equipment.There may also be network management servers there.','CO ID');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_description,inv_loc_type_disp_id_name) values(4,'Street','This is basically a road','ACISA');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_description,inv_loc_type_disp_id_name) values(337,'Intersection','This is a location that basically joins two points','Intersect ID');

create table D_Inv_Loc_Type_Parent_Allowed (   
   Inv_Loc_Type_ID integer not null,
   Inv_Loc_Parent_ID integer not null,
   CONSTRAINT D_Inv_LPTA_pk PRIMARY KEY(Inv_Loc_Type_ID,Inv_Loc_Parent_ID),
   CONSTRAINT D_Inv_LPTA_fk FOREIGN KEY(Inv_Loc_Type_ID) references D_Inv_Location_Types(Inv_Loc_Type_ID),
   CONSTRAINT D_Inv_LPTA2_fk FOREIGN KEY(Inv_Loc_Parent_ID) references D_Inv_Location_Types(Inv_Loc_Type_ID)
)
;
                                   
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values (4,15);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values (4,3);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values (3,15);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values (15,96);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values (166,3);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values (96,174);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values (337,3);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values (337,4);


DROP FUNCTION sp_loc_parent_check(INTEGER);
CREATE FUNCTION sp_loc_parent_check(INTEGER) RETURNS BOOLEAN AS '
DECLARE
   ID ALIAS FOR $1;
   rows RECORD;
BEGIN
  FOR rows IN SELECT * FROM d_inv_loc_type_parent_allowed
              WHERE inv_loc_parent_id=ID LOOP
    IF rows.inv_loc_type_id = ID THEN
       RAISE EXCEPTION ''OSS: You can not make a child of the location type its parent'';
    END IF;
    PERFORM sp_loc_parent_check(rows.inv_loc_type_id);
  END LOOP;
  RETURN TRUE;
END;' LANGUAGE 'plpgsql';


DROP FUNCTION loc_types_parent_trigger_func();
CREATE FUNCTION loc_types_parent_trigger_func() RETURNS OPAQUE AS '
DECLARE
    rows RECORD;
BEGIN
    PERFORM sp_loc_parent_check(NEW.inv_loc_type_id);
    RETURN NEW;
END;' LANGUAGE 'plpgsql';

DROP TRIGGER loc_types_parent_trigger ON d_inv_loc_type_parent_allowed;
CREATE TRIGGER loc_types_parent_trigger AFTER INSERT ON d_inv_loc_type_parent_allowed
FOR EACH ROW EXECUTE PROCEDURE loc_types_parent_trigger_func();

/* The statement that is causing the error with the system*/
insert into d_inv_loc_type_parent_allowed values(96,3);