CREATE GENERATOR GEN_ZAUVAG_ID;
CREATE TABLE ZAUVAG (
    ID            BIGINT NOT NULL,
    DATA_ROZGL    DATE NOT NULL,
    DATA_MAIL     DATE,
    DATA_CORRECT  DATE,
    ID_OBLAST     INTEGER NOT NULL,
    ID_RAJON      INTEGER NOT NULL,
    TIP           SMALLINT,
    ID_PROG       INTEGER,
    ZMIST         VARCHAR(2500),
    RESULTS       VARCHAR(1000),
    PRIMER        VARCHAR(20)
);
ALTER TABLE ZAUVAG ADD CONSTRAINT PK_ZAUVAG PRIMARY KEY (ID);

ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_OBL FOREIGN KEY (ID_OBLAST) REFERENCES OBLAST (ID) ON DELETE SET NULL;
ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_PRG FOREIGN KEY (ID_PROG) REFERENCES PROGS (ID) ON DELETE SET NULL;
ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_RAJ FOREIGN KEY (ID_OBLAST, ID_RAJON) REFERENCES RAJON (ID_OBL, ID) ON DELETE SET NULL;
ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_TIP FOREIGN KEY (TIP) REFERENCES TIP_ZAUVAG (ID) ON DELETE SET NULL;

CREATE DESCENDING INDEX IDX_ZAUVAG_DATE_ROZGL ON ZAUVAG (DATA_ROZGL);
/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/
SET TERM ^ ;
/* Trigger: TR_ZAUVAG_ID */
CREATE OR ALTER TRIGGER TR_ZAUVAG_ID FOR ZAUVAG
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  /* Trigger text */
  if (new.id is null) then
    new.id = gen_id(GEN_ZAUVAG_ID,1);
end
^
SET TERM ; ^

CREATE GENERATOR GEN_ATTACH_ID;

CREATE TABLE ATTACH (
    ID           BIGINT NOT NULL,
    ID_ZAUV      BIGINT,
    FILENAME     VARCHAR(250),
    FILECONTENT  BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    TIP_ATTACH   SMALLINT
);
ALTER TABLE ATTACH ADD CONSTRAINT PK_ATTACH PRIMARY KEY (ID);
CREATE INDEX IDX_ATTACH_ZAUVAG ON ATTACH (ID_ZAUV, ID);

/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/
SET TERM ^ ;
/* Trigger: TR_ATACH_ID */
CREATE OR ALTER TRIGGER TR_ATACH_ID FOR ATTACH
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  /* Trigger text */
  if (new.id is null) then
    new.id = gen_id(gen_attach_id,1);
end
^
SET TERM ; ^