Организация удаленного доступа к распределенным базам данных

LTER TABLE UslDivisionsKeys
       ADD CONSTRAINT XPKUslDivisionsKeys PRIMARY KEY (Code);


CREATE TABLE UslLgots (
       Code                 INTEGER NOT NULL,
       Category             INTEGER NOT NULL,
       Property             INTEGER,
       Tax                  CURR_TYPE,
       Usl                  INTEGER NOT NULL,
       NachCoeff            INTEGER NOT NULL,
       Nalog                INTEGER NOT NULL,
       BegDate              INTEGER NOT NULL,
       Info                 INTEGER NOT NULL,
       EndDate              INTEGER NOT NULL
);


ALTER TABLE UslLgots
       ADD CONSTRAINT XPKUslLgots PRIMARY KEY (Code);


CREATE TABLE UslProps (
       Code                 INTEGER NOT NULL,
       PKey                 INTEGER NOT NULL,
       Tag                  INTEGER NOT NULL,
       ValInteger           INTEGER,
       ValFloat             FLOAT,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);


ALTER TABLE UslProps
       ADD CONSTRAINT XPKUslProps PRIMARY KEY (Code);

CREATE TRIGGER UslProps_BUH FOR UslProps
BEFORE UPDATE  POSITION 0
AS
BEGIN

 /*     Изменение BegDate    */
 IF (new.BegDate <> old.BegDate) THEN
   BEGIN
     IF (new.BegDate < old.BegDate) THEN
       BEGIN
         /*     Расширение BegDate    */
         UPDATE UslProps
          SET EndDate = new.BegDate
          WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey =
new.PKey));


       END
     ELSE
       BEGIN
         /*     Сужение BegDate     */
         UPDATE UslProps
          SET EndDate = new.BegDate
          WHERE ((EndDate = old.BegDate)  AND (PKey = new.PKey));


       END
   END

 /*     Изменение EndDate    */
 IF (new.EndDate <> old.EndDate) THEN
   BEGIN
     IF (new.EndDate > old.EndDate) THEN
       BEGIN
         /*     Расширение EndDate    */
          UPDATE UslProps
          SET BegDate = new.EndDate
          WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey =
new.PKey));


       END
     ELSE
       BEGIN
         /*     Сужение EndDate     */
          UPDATE UslProps
          SET BegDate = new.EndDate
          WHERE ((BegDate = old.EndDate)  AND (PKey = new.PKey));


       END
   END

    /*     Сборка мусора       */
    DELETE FROM UslProps
    WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey
= new.PKey) AND (Code <> new.Code));


END ^


CREATE TRIGGER UslProps_BIH FOR UslProps
BEFORE INSERT POSITION 0
AS
BEGIN

  DELETE FROM UslProps
  WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey =
new.PKey));


  UPDATE UslProps
  SET BegDate = new.EndDate
  WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));


  UPDATE UslProps
  SET EndDate = new.BegDate
  WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));


END ^



CREATE TRIGGER UslProps_BDH FOR UslProps
BEFORE DELETE  POSITION 0
AS
BEGIN

    UPDATE UslProps
    SET EndDate = old.EndDate
     WHERE ((EndDate = old.BegDate)  AND (PKey = old.PKey));


END ^



CREATE TABLE UslPropsKeys (
       Code                 INTEGER NOT NULL
);


ALTER TABLE UslPropsKeys
       ADD CONSTRAINT XPKUslPropsKeys PRIMARY KEY (Code);


CREATE TABLE Usls (
       Code                 INTEGER NOT NULL,
       PKey                 INTEGER NOT NULL,
       Division             INTEGER NOT NULL,
       UslType              INTEGER NOT NULL,
       Name                 CHAR(64) NOT NULL,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);

CREATE INDEX XIEUslsName ON Usls
(
       Name
);


ALTER TABLE Usls
       ADD CONSTRAINT XPKUsls PRIMARY KEY (Code);

CREATE TRIGGER Usls_BUH FOR Usls
BEFORE UPDATE  POSITION 0
AS
BEGIN

 /*     Изменение BegDate    */
 IF (new.BegDate <> old.BegDate) THEN
   BEGIN
     IF (new.BegDate < old.BegDate) THEN
       BEGIN
         /*     Расширение BegDate    */
         UPDATE Usls
          SET EndDate = new.BegDate
          WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey =
new.PKey));


       END
     ELSE
       BEGIN
         /*     Сужение BegDate     */
         UPDATE Usls
          SET EndDate = new.BegDate
          WHERE ((EndDate = old.BegDate)  AND (PKey = new.PKey));


       END
   END

 /*     Изменение EndDate    */
 IF (new.EndDate <> old.EndDate) THEN
   BEGIN
     IF (new.EndDate > old.EndDate) THEN
       BEGIN
         /*     Расширение EndDate    */
          UPDATE Usls
          SET BegDate = new.EndDate
          WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey =
new.PKey));


       END
     ELSE
       BEGIN
         /*     Сужение EndDate     */
          UPDATE Usls
          SET BegDate = new.EndDate
          WHERE ((BegDate = old.EndDate)  AND (PKey = new.PKey));


       END
   END

    /*     Сборка мусора       */
    DELETE FROM Usls
    WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey
= new.PKey) AND (Code <> new.Code));


END ^


CREATE TRIGGER Usls_BIH FOR Usls
BEFORE INSERT POSITION 0
AS
BEGIN

  DELETE FROM Usls
  WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey =
new.PKey));


  UPDATE Usls
  SET BegDate = new.EndDate
  WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));


  UPDATE Usls
  SET EndDate = new.BegDate
  WHERE ((new.BegDate BETWEEN BegDate AND EndDate)  AND (PKey = new.PKey));


END ^



CREATE TRIGGER Usls_BDH FOR Usls
BEFORE DELETE  POSITION 0
AS
BEGIN

    UPDATE Usls
    SET EndDate = old.EndDate
     WHERE ((EndDate = old.BegDate)  AND (PKey = old.PKey));


END ^



CREATE TABLE UslsKeys (
       Code                 INTEGER NOT NULL
);


ALTER TABLE UslsKeys
       ADD CONSTRAINT XPKUslsKeys PRIMARY KEY (Code);


CREATE TABLE UslTypes (
       Code                 INTEGER NOT NULL,
       Name                 DESCR_TYPE
);


ALTER TABLE UslTypes
       ADD CONSTRAINT XPKUslTypes PRIMARY KEY (Code);


ALTER TABLE Nach
       ADD CONSTRAINT R_59
              FOREIGN KEY (Usl)
                             REFERENCES UslsKeys;


ALTER TABLE Nach
       ADD CONSTRAINT R_57
              FOREIGN KEY (Phone)
                             REFERENCES PhonesKeys;


ALTER TABLE Nach
       ADD FOREIGN KEY (Owner)
                             REFERENCES PhonesOwnersKeys;


ALTER TABLE NachBillings
       ADD CONSTRAINT R_65
              FOREIGN KEY (Division)
                             REFERENCES UslDivisionsKeys;


ALTER TABLE NachBillings
       ADD FOREIGN KEY (BillDateCode)
                             REFERENCES NachBillDates;


ALTER TABLE NachBillings
       ADD FOREIGN KEY (Owner)
                             REFERENCES PhonesOwnersKeys;


ALTER TABLE NachConstUsl
       ADD CONSTRAINT R_60
              FOREIGN KEY (Usl)
                             REFERENCES UslsKeys;


ALTER TABLE NachConstUsl
       ADD CONSTRAINT R_58
              FOREIGN KEY (Phone)
                             REFERENCES PhonesKeys;


ALTER TABLE NachConstUsl
       ADD FOREIGN KEY (Owner)
                             REFERENCES PhonesOwnersKeys;


ALTER TABLE Phones
       ADD FOREIGN KEY (Owner)
                             REFERENCES PhonesOwnersKeys;


ALTER TABLE Phones
       ADD FOREIGN KEY (PKey)
                             REFERENCES PhonesKeys;


ALTER TABLE Phones
       ADD FOREIGN KEY (Street)
                             REFERENCES PhonesStreets;


ALTER TABLE PhonesOwners
       ADD FOREIGN KEY (PostStation)
                             REFERENCES PhonesPostStations;


ALTER TABLE PhonesOwners
       ADD FOREIGN KEY (Street)
                             REFERENCES PhonesStreets;


ALTER TABLE PhonesOwners
       ADD FOREIGN KEY (Bank)
                             REFERENCES PhonesBanks;


ALTER TABLE PhonesOwners
       ADD FOREIGN KEY (Category)
                             REFERENCES UslCatKeys;


ALTER TABLE PhonesOwners
       ADD FOREIGN KEY (PKey)
                             REFERENCES PhonesOwnersKeys;


ALTER TABLE PhonesPostStations
       ADD CONSTRAINT R_62
              FOREIGN KEY (Region)
                             REFERENCES PhonesRegions;


ALTER TABLE PhonesStations
       ADD FOREIGN KEY (Region)
                             REFERENCES PhonesRegions;


ALTER TABLE PhonesStreets
       ADD FOREIGN KEY (Region)
                             REFERENCES PhonesRegions;


ALTER TABLE PhonesStreets
       ADD FOREIGN KEY (Station)
                             REFERENCES PhonesStations;


ALTER TABLE Plat
       ADD CONSTRAINT R_61
              FOREIGN KEY (ToUsl)
                             REFERENCES UslsKeys;


ALTER TABLE Plat
       ADD FOREIGN KEY (Owner)
                             REFERENCES PhonesOwnersKeys;


ALTER TABLE SysSettings
       ADD FOREIGN KEY (NullOwner)
                             REFERENCES PhonesOwnersKeys;


ALTER TABLE SysSettings
       ADD FOREIGN KEY (TimeTalksUsl)
                             REFERENCES UslsKeys;


ALTER TABLE Talks
       ADD FOREIGN KEY (ToPhone)
                             REFERENCES PhonesKeys;


ALTER TABLE Talks
       ADD FOREIGN KEY (Phone)
                             REFERENCES PhonesKeys;


ALTER TABLE Talks
       ADD FOREIGN KEY (DayCode)
                             REFERENCES TalksPay;


ALTER TABLE TalksPay
       ADD FOREIGN KEY (Phone)
                             REFERENCES PhonesKeys;


ALTER TABLE UslCat
       ADD FOREIGN KEY (PKey)
                             REFERENCES UslCatKeys;


ALTER TABLE UslDivisions
       ADD CONSTRAINT R_6

Пред. 16 17 18 19 20 След.

скачать работу

Отправка СМС бесплатно