GD CURRRATE

CREATE TABLE gd_currrate
(
  id             dintkey,
  fromcurr       dintkey,
  tocurr         dintkey,
  fordate        dtimestamp_notnull,
  regulatorkey   dforeignkey,
  coeff          dcurrrate,
  amount         dcurrrate_amount,
  val            dcurrrate, /*  value = amount * coeff  */
  editiondate    deditiondate
);
 
COMMIT;
 
SET TERM ^ ;
 
CREATE OR ALTER TRIGGER gd_bi_currrate FOR gd_currrate 
  ACTIVE
  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);
END
^
 
CREATE OR ALTER TRIGGER gd_biu_currrate FOR gd_currrate 
  ACTIVE
  BEFORE INSERT OR UPDATE
  POSITION 32000
AS
BEGIN
  IF (NEW.amount IS NULL) THEN
    NEW.amount = 1;
  IF (NEW.val IS NULL) THEN
    NEW.val = NEW.amount * NEW.coeff;
  ELSE  
    NEW.coeff = NEW.val / NEW.amount; 
END
^
 
SET TERM ; ^
 
ALTER TABLE gd_currrate ADD CONSTRAINT gd_pk_currrate
  PRIMARY KEY (id);
 
ALTER TABLE gd_currrate ADD CONSTRAINT gd_uk_currrate
  UNIQUE (fromcurr, tocurr, fordate, regulatorkey);
 
ALTER TABLE gd_currrate ADD CONSTRAINT gd_fk1_currrate
  FOREIGN KEY (fromcurr) REFERENCES gd_curr (id) ON UPDATE CASCADE;
 
ALTER TABLE gd_currrate ADD CONSTRAINT gd_fk2_currrate
  FOREIGN KEY (tocurr) REFERENCES gd_curr (id) ON UPDATE CASCADE;
 
ALTER TABLE gd_currrate ADD CONSTRAINT gd_chk1_currrate
  CHECK(fromcurr <> tocurr);
 
CREATE DESC INDEX gd_x_currrate_fordate ON gd_currrate(fordate);