GD CONSTVALUE
Материал из GedeminWiki
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 ; ^