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

EndDate)  AND (PKey = new.PKey));


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


END ^



CREATE TRIGGER PhonesOwners_BDH FOR PhonesOwners
BEFORE DELETE  POSITION 0
AS
BEGIN

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


END ^



CREATE TABLE PhonesOwnersKeys (
       Code                 INTEGER NOT NULL,
       InRest               CURR_TYPE,
       OutRest              CURR_TYPE,
       NDolg                INTEGER NOT NULL
);


ALTER TABLE PhonesOwnersKeys
       ADD CONSTRAINT XPKPhonesOwnersKeys PRIMARY KEY (Code);


CREATE TABLE PhonesPostStations (
       Code                 INTEGER NOT NULL,
       Name                 DESCR_TYPE,
       Region               INTEGER NOT NULL,
       PostIndex            CHAR(6) NOT NULL,
       PostNmb              CHAR(6) NOT NULL
);

CREATE UNIQUE INDEX XAKPhonesPostStationsIndex ON PhonesPostStations
(
       PostIndex
);

CREATE UNIQUE INDEX XAKPhonesPostStationsPostNmb ON PhonesPostStations
(
       PostNmb
);

CREATE INDEX XIEPhonesPostStationsName ON PhonesPostStations
(
       Name
);


ALTER TABLE PhonesPostStations
       ADD CONSTRAINT XPKPhonesPostStations PRIMARY KEY (Code);


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

CREATE INDEX XIEPhonesRegionsName ON PhonesRegions
(
       Name
);


ALTER TABLE PhonesRegions
       ADD CONSTRAINT XPKPhonesRegions PRIMARY KEY (Code);


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

CREATE INDEX XIEPhonesStationsName ON PhonesStations
(
       Name
);


ALTER TABLE PhonesStations
       ADD CONSTRAINT XPKPhonesStations PRIMARY KEY (Code);


CREATE TABLE PhonesStreets (
       Code                 INTEGER NOT NULL,
       Station              INTEGER NOT NULL,
       Region               INTEGER NOT NULL,
       Name                 DESCR_TYPE
);

CREATE INDEX XIEPhonesStreetsName ON PhonesStreets
(
       Name
);


ALTER TABLE PhonesStreets
       ADD CONSTRAINT XPKPhonesStreets PRIMARY KEY (Code);


CREATE TABLE Plat (
       Code                 INTEGER NOT NULL,
       Owner                INTEGER NOT NULL,
       ToUsl                INTEGER,
       PlatDate             DATE_TYPE,
       PlatType             INTEGER NOT NULL,
       DocNmb               CHAR(12) NOT NULL
);


ALTER TABLE Plat
       ADD CONSTRAINT XPKPlat PRIMARY KEY (Code);


CREATE TABLE SysLog (
       Code                 INTEGER NOT NULL,
       TableName            CHAR(16) NOT NULL,
       OpType               INTEGER NOT NULL,
       NewData              CHAR(64) NOT NULL,
       OpDate               DATE NOT NULL
);


ALTER TABLE SysLog
       ADD CONSTRAINT XPKSysLog PRIMARY KEY (Code);


CREATE TABLE SysSettings (
       Code                 INTEGER NOT NULL,
       TimeTalksUsl         INTEGER NOT NULL,
       NullOwner            INTEGER NOT NULL
);


ALTER TABLE SysSettings
       ADD CONSTRAINT XPKSysSettings PRIMARY KEY (Code);


CREATE TABLE Talks (
       Code                 INTEGER NOT NULL,
       DayCode              INTEGER NOT NULL,
       Phone                INTEGER NOT NULL,
       ToPhone              INTEGER NOT NULL,
       CallTime             CALLTIME_TYPE,
       PhoneNmb             PHONE_TYPE,
       HowLong              INTEGER NOT NULL,
       ToPhoneNmb           PHONE_TYPE,
       Calculated           SMALLINT NOT NULL,
       CallDate             DATE_TYPE
);

CREATE INDEX XAK1TalksCallDate ON Talks
(
       CallDate
);


ALTER TABLE Talks
       ADD CONSTRAINT XPKTalks PRIMARY KEY (Code);


CREATE TABLE TalksPay (
       Code                 INTEGER NOT NULL,
       Phone                INTEGER NOT NULL,
       TotalSum             CURR_TYPE,
       TotalLgotTime        CALLTIME_TYPE,
       TotalFullTime        CALLTIME_TYPE,
       TotalTime            COMPUTED BY (TotalLgotTime+TotalFullTime),
       CallDate             DATE_TYPE
);


ALTER TABLE TalksPay
       ADD CONSTRAINT XPKTalksPay PRIMARY KEY (Code);


CREATE TABLE UslCat (
       Code                 INTEGER NOT NULL,
       PKey                 INTEGER NOT NULL,
       Name                 DESCR_TYPE,
       Parent               INTEGER NOT NULL,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);

CREATE INDEX XIEUslCatName ON UslCat
(
       Name
);

CREATE INDEX XIEUslCatParent ON UslCat
(
       Parent
);


ALTER TABLE UslCat
       ADD CONSTRAINT XPKUslCat PRIMARY KEY (Code);

CREATE TRIGGER UslCat_BUH FOR UslCat
BEFORE UPDATE  POSITION 0
AS
BEGIN

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


       END
     ELSE
       BEGIN
         /*     Сужение BegDate     */
         UPDATE UslCat
          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 UslCat
          SET BegDate = new.EndDate
          WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey =
new.PKey));


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


       END
   END

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


END ^


CREATE TRIGGER UslCat_BIH FOR UslCat
BEFORE INSERT POSITION 0
AS
BEGIN

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


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


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


END ^



CREATE TRIGGER UslCat_BDH FOR UslCat
BEFORE DELETE  POSITION 0
AS
BEGIN

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


END ^



CREATE TABLE UslCatKeys (
       Code                 INTEGER NOT NULL
);


ALTER TABLE UslCatKeys
       ADD CONSTRAINT XPKUslCatKeys PRIMARY KEY (Code);


CREATE TABLE UslDivisions (
       Code                 INTEGER NOT NULL,
       Name                 DESCR_TYPE,
       PKey                 INTEGER NOT NULL,
       Parent               INTEGER NOT NULL,
       BegDate              DATE_TYPE,
       EndDate              DATE_TYPE
);

CREATE INDEX XIEUslDivisionsname ON UslDivisions
(
       Name
);

CREATE INDEX XIEUslDivisionsParent ON UslDivisions
(
       Parent
);


ALTER TABLE UslDivisions
       ADD CONSTRAINT XPKUslDivisions PRIMARY KEY (Code);

CREATE TRIGGER UslDivisions_BUH FOR UslDivisions
BEFORE UPDATE  POSITION 0
AS
BEGIN

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


       END
     ELSE
       BEGIN
         /*     Сужение BegDate     */
         UPDATE UslDivisions
          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 UslDivisions
          SET BegDate = new.EndDate
          WHERE ((new.EndDate BETWEEN BegDate AND EndDate)  AND (PKey =
new.PKey));


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


       END
   END

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


END ^


CREATE TRIGGER UslDivisions_BIH FOR UslDivisions
BEFORE INSERT POSITION 0
AS
BEGIN

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


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


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


END ^



CREATE TRIGGER UslDivisions_BDH FOR UslDivisions
BEFORE DELETE  POSITION 0
AS
BEGIN

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


END ^



CREATE TABLE UslDivisionsKeys (
       Code                 INTEGER NOT NULL
);


A

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

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

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