GD PEOPLE

 CREATE TABLE gd_people
 (
   contactkey     dintkey,
   firstname      dtext20,      /* Імя                                                            */
   surname        dtext20 NOT NULL,/* Прозвішча                                                      */
   middlename     dtext20,      /* Імя па бацьку                                                  */
   nickname       dtext20,      /* Кароткае імя                                                   */
   rank           dtext20,      /* Званіе                                                         */

   /* Хатнія дадзеныя */
   hplacekey      dforeignkey,
   haddress       dtext60,     /* Адрас                                                          */
   hcity          dtext20,     /* Горад                                                          */
   hregion        dtext20,     /* Вобласць                                                       */
   hZIP           dtext20,     /* Індэкс                                                         */
   hcountry       dtext20,     /* Краіна                                                         */
   hdistrict      dtext20,
   hphone         dtext20,

   /* Працоўныя дадзеныя */
   wcompanykey    dforeignkey,
   wcompanyname   dtext60,     /* Кампанія                                                       */
   wdepartment    dtext20,     /* Падраздзяленьне                                                */
   wpositionkey   dforeignkey,

   /* Пэрсанальныя дадзеныя */
   spouse         dtext20,     /* Супруг/супруга                                                 */
   children       dtext20,     /* Дзеткі                                                         */
   sex            dgender,     /* Пол                                                            */
   birthday       ddate,        /* Дата нараджэньня                                               */

   /* Пашпартныя дадзеныя */
   passportnumber dtext40,     /* нумар пашпарту */
   /*passportdate   ddate,*/       /* ??? */
   passportexpdate ddate,      /* тэрмін дзеяння пашпарту */
   passportissdate ddate,      /* дата выдачы */
   passportissuer dtext40,     /* хто выдаў */
   passportisscity dtext20,    /* дзе выдадзены */
   personalnumber dtext40,     /* пэрсанальны номер */

   /*Угодкі*/

   /* Дадатковая інфармацыя */
   visitcard      dBMP,        /* Візітная картка                                                */
   photo          dBMP         /* Фота                                                           */
 );

 ALTER TABLE gd_people
   ADD CONSTRAINT gd_pk_people PRIMARY KEY (contactkey);

 ALTER TABLE gd_people ADD CONSTRAINT gd_fk_people_contactkey
   FOREIGN KEY (contactkey) REFERENCES gd_contact(id)
   ON UPDATE CASCADE
   ON DELETE CASCADE;

 /* калі выдаляецца кампанія на якую спасылаецца чалавек */
 /* нічога страшнага -- ануліруем гэную спасылку і ўсё   */
 ALTER TABLE gd_people ADD CONSTRAINT gd_fk_people_companykey
   FOREIGN KEY (wcompanykey) REFERENCES gd_contact(id)
   ON UPDATE CASCADE
   ON DELETE SET NULL;

 ALTER TABLE gd_people ADD CONSTRAINT gd_fk_people_positionkey
   FOREIGN KEY (wpositionkey) REFERENCES wg_position(id)
   ON UPDATE CASCADE
   ON DELETE SET NULL;

 ALTER TABLE gd_people ADD CONSTRAINT gd_fk_people_hplacekey
   FOREIGN KEY (hplacekey) REFERENCES gd_place(id)
   ON UPDATE CASCADE
   ON DELETE SET NULL;

 CREATE TRIGGER gd_bi_people FOR gd_people
   BEFORE INSERT
   POSITION 0
 AS
 BEGIN
   NEW.rank = NULL;
   SELECT SUBSTRING(name FROM 1 FOR 20) FROM wg_position WHERE id = NEW.wpositionkey
     INTO NEW.rank;

   IF (NOT NEW.wcompanykey IS NULL) THEN
   BEGIN
     SELECT name FROM gd_contact WHERE id = NEW.wcompanykey
       INTO NEW.wcompanyname;
   END
 END

 CREATE TRIGGER gd_bu_people FOR gd_people
   BEFORE UPDATE
   POSITION 0
 AS
 BEGIN
   NEW.rank = NULL;
   SELECT SUBSTRING(name FROM 1 FOR 20) FROM wg_position WHERE id = NEW.wpositionkey
     INTO NEW.rank;

   IF (NOT NEW.wcompanykey IS NULL) THEN
   BEGIN
     SELECT name FROM gd_contact WHERE id = NEW.wcompanykey
       INTO NEW.wcompanyname;
   END
 END