RP REPORTGROUP

CREATE TABLE rp_reportgroup

 (
   id             dintkey,
   parent         dforeignkey,
   name           dname,
   description    dtext180,
   lb               dlb,
   rb               drb,
   usergroupname  dname DEFAULT ,
   reserved       dinteger
 );

 ALTER TABLE rp_reportgroup
   ADD CONSTRAINT rp_pk_reportgroup PRIMARY KEY (id);

 CREATE UNIQUE INDEX rp_x_reportgroup_ugn ON rp_reportgroup
   /*COMPUTED BY (UPPER(usergroupname));*/
   (usergroupname);

 CREATE UNIQUE INDEX rp_x_reportgroup_lrn ON
   rp_reportgroup (name, parent);

 ALTER TABLE rp_reportgroup ADD CONSTRAINT rp_fk_reportgroup_parent
   FOREIGN KEY (parent) REFERENCES rp_reportgroup(id)
   ON UPDATE CASCADE
   ON DELETE CASCADE;

 ALTER TABLE gd_documenttype ADD CONSTRAINT gd_fk_documenttype_rpgroupkey
   FOREIGN KEY (reportgroupkey) REFERENCES rp_reportgroup (id) ON UPDATE CASCADE;

 COMMIT;

 SET TERM ^ ;

 CREATE TRIGGER rp_before_insert_reportgroup FOR rp_reportgroup
   BEFORE INSERT
   POSITION 0
 AS
 BEGIN
   IF (NEW.id IS NULL) THEN
     NEW.id = GEN_ID(gd_g_unique, 1) + GEN_ID(gd_g_offset, 0);
   IF (NEW.usergroupname IS NULL) THEN
     NEW.usergroupname = CAST(NEW.id AS varchar(60));
 END
 ^

 CREATE PROCEDURE rp_p_checkgrouptree (newparent INTEGER, id INTEGER)
 RETURNS (
     include INTEGER
 )
 AS
   DECLARE VARIABLE I INTEGER;
 begin
   IF (newparent = id) THEN
   BEGIN
     include = 1;
     EXIT;
   END ELSE
     include = 0;
   FOR SELECT id FROM rp_reportgroup WHERE parent = :id INTO :I do
   BEGIN
     IF (newparent = I) THEN
     BEGIN
       include = 1;
       EXIT;
     END ELSE
     BEGIN
       EXECUTE PROCEDURE rp_p_checkgrouptree(:newparent, :I) RETURNING_VALUES :include;
       if (include = 1) then
         EXIT;
     END
   END
 END
 ^
 SET TERM ; ^