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
);
ALTER 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_63
FOREIGN KEY (PKey)
REFERENCES UslDivisionsKeys;
ALTER TABLE UslLgots
ADD CONSTRAINT R_50
FOREIGN KEY (Property)
REFERENCES UslPropsKeys;
ALTER TABLE UslLgots
ADD FOREIGN KEY (Usl)
REFERENCES UslsKeys;
ALTER TABLE UslLgots
ADD FOREIGN KEY (Category)
REFERENCES UslCatKeys;
ALTER TABLE UslProps
ADD CONSTRAINT R_51
FOREIGN KEY (PKey)
REFERENCES UslPropsKeys;
ALTER TABLE Usls
ADD CONSTRAINT R_64
FOREIGN KEY (Division)
REFERENCES UslDivisionsKeys;
ALTER TABLE Usls
ADD FOREIGN KEY (UslType)
REFERENCES UslTypes;
ALTER TABLE Usls
ADD FOREIGN KEY (PKey)
REFERENCES UslsKeys;
CREATE PROCEDURE PrGenUslPropsKeys
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genUslPropsKeys, 1);
END ^
CREATE PROCEDURE PrGenUslProps
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genUslProps, 1);
END ^
CREATE PROCEDURE PrGenPhonesRegions
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genPhonesRegions, 1);
END ^
CREATE PROCEDURE PrGenPhonesStations
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genPhonesStations, 1);
END ^
CREATE PROCEDURE PrGenPhonesStreets
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genPhonesStreets, 1);
END ^
CREATE PROCEDURE PrGenPhonesBanks
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genPhonesBanks, 1);
END ^
CREATE PROCEDURE PrGenTalksPay
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genTalksPay, 1);
END ^
CREATE PROCEDURE PrGenTalks
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genTalks, 1);
END ^
CREATE PROCEDURE PrGenNach
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genNach, 1);
END ^
CREATE PROCEDURE PrGenNachBillings
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genNachBillings, 1);
END ^
CREATE PROCEDURE PrGenNachBillDates
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genNachBillDates, 1);
END ^
CREATE PROCEDURE PrGenNachConstUsl
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genNachConstUsl, 1);
END ^
CREATE PROCEDURE PrGenUslDivisions
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genUslDivisions, 1);
END ^
CREATE PROCEDURE PrGenUslLgots
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genUslLgots, 1);
END ^
CREATE PROCEDURE PrGenUslsKeys
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genUslsKeys, 1);
END ^
CREATE PROCEDURE PrGenUsls
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genUsls, 1);
END ^
CREATE PROCEDURE PrGenUslCatKeys
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genUslCatKeys, 1);
END ^
CREATE PROCEDURE PrGenUslCat
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genUslCat, 1);
END ^
CREATE PROCEDURE PrGenPhones
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genPhones, 1);
END ^
CREATE PROCEDURE PrGenPhonesOwnersKeys
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genPhonesOwnersKeys, 1);
END ^
CREATE PROCEDURE PrGenPhonesOwners
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genPhonesOwners, 1);
END ^
CREATE PROCEDURE PrGenSysSettings
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genSysSettings, 1);
END ^
CREATE PROCEDURE PrGenPhonesKeys
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genPhonesKeys, 1);
END ^
CREATE PROCEDURE PrGenPlat
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genPlat, 1);
END ^
CREATE PROCEDURE PrGenPhonesPostStations
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genPhonesPostStations, 1);
END ^
CREATE PROCEDURE PrGenSysLog
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genSysLog, 1);
END ^
CREATE PROCEDURE PrGenUslTypes
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genUslTypes, 1);
END ^
CREATE PROCEDURE PrGenUslDivisionsKeys
RETURNS (ACode INTEGER)
AS
BEGIN
ACode = GEN_ID(genUslDivisionsKeys, 1);