GD CONSTVALUE

CREATE TABLE gd_constvalue
(
  id            dintkey,
  userkey       dforeignkey,
  companykey    dforeignkey,
  constkey      dintkey,
  constdate     DATE,
  constvalue    dtext120,
  editorkey     dforeignkey,     /* Кто создал или изменил запись */
  editiondate   deditiondate    /* Когда создана или изменена запись */
);

ALTER TABLE gd_constvalue
  ADD CONSTRAINT gd_pk_constvalue PRIMARY KEY (id);
 
ALTER TABLE gd_constvalue ADD CONSTRAINT gd_fk_user_constvalue
  FOREIGN KEY (userkey) REFERENCES gd_contact(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE;
 
ALTER TABLE gd_constvalue ADD CONSTRAINT gd_fk_oc_constvalue
  FOREIGN KEY (companykey) REFERENCES gd_ourcompany(companykey)
  ON DELETE CASCADE
  ON UPDATE CASCADE;
 
ALTER TABLE gd_constvalue ADD CONSTRAINT gd_fk_vn_constvalue
  FOREIGN KEY (constkey) REFERENCES gd_const(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE;
 
ALTER TABLE gd_constvalue ADD CONSTRAINT gd_fk_ek_constvalue
  FOREIGN KEY (editorkey) REFERENCES gd_contact(id)
  ON DELETE SET NULL
  ON UPDATE CASCADE;
 
SET TERM ^ ;
 
CREATE EXCEPTION gd_e_invalidconstname 'Constant already exists'
^
 
CREATE TRIGGER gd_bi_const FOR gd_const
  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 (EXISTS (SELECT * FROM gd_const WHERE UPPER(name) = UPPER(NEW.name))) THEN
  BEGIN
    EXCEPTION gd_e_invalidconstname;
  END
END
^
 
CREATE TRIGGER gd_bu_const FOR gd_const
  BEFORE UPDATE
  POSITION 0
AS
BEGIN
  IF (EXISTS (SELECT * FROM gd_const WHERE UPPER(name) = UPPER(NEW.name)
    AND id <> NEW.id)) THEN
  BEGIN
    EXCEPTION gd_e_invalidconstname;
  END
END
^
 
CREATE TRIGGER gd_bi_constvalue FOR gd_constvalue
  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
^
 
SET TERM ; ^