Организация удаленного доступа к распределенным базам данныхРефераты >> Программирование и компьютеры >> Организация удаленного доступа к распределенным базам данных
Code INTEGER NOT NULL
);
ALTER TABLE PhonesKeys
ADD CONSTRAINT XPKPhonesKeys PRIMARY KEY (Code);
CREATE TABLE PhonesOwners (
Code INTEGER NOT NULL,
PKey INTEGER NOT NULL,
Name1 DESCR_TYPE,
Name2 DESCR_TYPE,
Category INTEGER NOT NULL,
Bank INTEGER,
Street INTEGER NOT NULL,
PostStation INTEGER,
House CHAR(5),
Corpus CHAR(3),
Flat CHAR(3),
Account CHAR(5),
RS CHAR(9),
INN CHAR(13),
Nmb_Dogov CHAR(6),
Date_Dogov DATE,
BegDate DATE_TYPE,
EndDate DATE_TYPE
);
ALTER TABLE PhonesOwners
ADD CONSTRAINT XPKPhonesOwners PRIMARY KEY (Code);
CREATE TRIGGER PhonesOwners_BUH FOR PhonesOwners
BEFORE UPDATE POSITION 0
AS
BEGIN
/* Изменение BegDate */
IF (new.BegDate <> old.BegDate) THEN
BEGIN
IF (new.BegDate < old.BegDate) THEN
BEGIN
/* Расширение BegDate */
UPDATE PhonesOwners
SET EndDate = new.BegDate
WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));
END
ELSE
BEGIN
/* Сужение BegDate */
UPDATE PhonesOwners
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 PhonesOwners
SET BegDate = new.EndDate
WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));
END
ELSE
BEGIN
/* Сужение EndDate */
UPDATE PhonesOwners
SET BegDate = new.EndDate
WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));
END
END
/* Сборка мусора */
DELETE FROM PhonesOwners
WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));
END ^
CREATE TRIGGER PhonesOwners_BIH FOR PhonesOwners
BEFORE INSERT POSITION 0
AS
BEGIN
DELETE FROM PhonesOwners
WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));
UPDATE PhonesOwners
SET BegDate = new.EndDate
WHERE ((new.EndDate BETWEEN BegDate AND 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
