CREATE TABLE DOWNLOADFILES (
  DF_ID integer NOT NULL,
  DF_PROJECT varchar(30) DEFAULT NULL,
  DF_MODULE varchar(30) DEFAULT NULL,
  DF_VERSION varchar(30) DEFAULT NULL,
  DF_USERNAME varchar(30) DEFAULT NULL,
  DF_PASSWORD varchar(30) DEFAULT NULL,
  DF_FILENAME varchar(255) DEFAULT NULL,
  DF_CLIENTNAME varchar(255) DEFAULT NULL,
  DF_ENTEREDON timestamp DEFAULT 'NOW' NOT NULL,
  DF_FETCHCOUNT INT DEFAULT 0 NOT NULL,
  CONSTRAINT DOWNLOADFILES_PK PRIMARY KEY (DF_ID)
);

COMMIT;

CREATE TABLE DOWNLOAD (
  DL_ID INT NOT NULL,
  DL_DOWNLOADFILES_FK INT NOT NULL,
  DL_TIMESTAMP TIMESTAMP NOT null,
  DL_IP VARCHAR(15) NOT NULL,
  CONSTRAINT PK_DOWNLOAD PRIMARY KEY (DL_ID)
);

COMMIT;

CREATE GENERATOR GEN_DOWNLOADFILES;
CREATE GENERATOR GEN_DOWNLOAD;

COMMIT;

SET TERM ^ ;

CREATE TRIGGER INSERTDOWNLOADFILES FOR DOWNLOADFILES ACTIVE
BEFORE INSERT POSITION 0
AS 
BEGIN 
 IF (NEW.DF_ID IS NULL) THEN   
    NEW.DF_ID=GEN_ID(GEN_DOWNLOADFILES,1);
END^

CREATE TRIGGER INSERTDOWNLOAD FOR DOWNLOAD ACTIVE
BEFORE INSERT POSITION 0
AS 
BEGIN 
 IF (NEW.DL_ID IS NULL) THEN   
   NEW.DL_ID=GEN_ID(GEN_DOWNLOAD,1);
 UPDATE DOWNLOADFILES SET DF_FETCHCOUNT = DF_FETCHCOUNT + 1 WHERE DF_ID=NEW.DL_DOWNLOADFILES_FK; 
END^

CREATE TRIGGER DELETEDOWNLOAD FOR DOWNLOAD ACTIVE
BEFORE DELETE POSITION 0
AS 
BEGIN 
  UPDATE DOWNLOADFILES SET DF_FETCHCOUNT = DF_FETCHCOUNT - 1 WHERE DF_ID=OLD.DL_DOWNLOADFILES_FK; 
END^

COMMIT ^

SET TERM ; ^
