Категории: ДомЗдоровьеЗоологияИнформатикаИскусствоИскусствоКомпьютерыКулинарияМаркетингМатематикаМедицинаМенеджментОбразованиеПедагогикаПитомцыПрограммированиеПроизводствоПромышленностьПсихологияРазноеРелигияСоциологияСпортСтатистикаТранспортФизикаФилософияФинансыХимияХоббиЭкологияЭкономикаЭлектроника |
SQL-скрипты объектов базы данных
SET SQL DIALECT 3;
/* CREATE DATABASE '127.0.0.1/gds_db:F:\TORGOTDEL.GDB' PAGE_SIZE 4096
DEFAULT CHARACTER SET */
/* Table: ADRES, Owner: SYSDBA */
CREATE TABLE "ADRES" ( "NOMER" INTEGER NOT NULL, "DOM" INTEGER NOT NULL, "KORP" INTEGER, "KVAR" INTEGER, "KOD_SE" INTEGER, "KOD_FL" INTEGER, "KOD_UL" INTEGER NOT NULL, "KOD_NP" INTEGER NOT NULL, PRIMARY KEY ("NOMER") );
/* Table: DOGOVOR, Owner: SYSDBA */
CREATE TABLE "DOGOVOR" ( "NOMER" INTEGER NOT NULL, "DATA_N" DATE NOT NULL, "DATA_O" DATE, "KOD_DOL" INTEGER NOT NULL, "KOD_SE" INTEGER NOT NULL, "KOD_FL" INTEGER NOT NULL, PRIMARY KEY ("NOMER") );
/* Table: DOLGNOST, Owner: SYSDBA */
CREATE TABLE "DOLGNOST" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(40) NOT NULL, "K_NAZV" VARCHAR(15), UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: EDIN_IZM, Owner: SYSDBA */
CREATE TABLE "EDIN_IZM" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(20) NOT NULL, "K_NAZV" VARCHAR(10), UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: FIZ_LICO, Owner: SYSDBA */
CREATE TABLE "FIZ_LICO" ( "T_NOMER" INTEGER NOT NULL, "FAM" VARCHAR(20) NOT NULL, "NAME" VARCHAR(20) NOT NULL, "OTCH" VARCHAR(20), "DATA_R" DATE, "KOD_P" INTEGER NOT NULL, PRIMARY KEY ("T_NOMER") );
/* Table: NAS_PUNKT, Owner: SYSDBA */
CREATE TABLE "NAS_PUNKT" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(40) NOT NULL, "KOD_TIPA" INTEGER NOT NULL, UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: POL_FIZ_LICA, Owner: SYSDBA */
CREATE TABLE "POL_FIZ_LICA" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(7) NOT NULL, "K_NAZV" VARCHAR(3), "KK_NAZV" VARCHAR(1), UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: POZIC_PR, Owner: SYSDBA */
CREATE TABLE "POZIC_PR" ( "NOMER" INTEGER NOT NULL, "CENA" NUMERIC(8, 2) NOT NULL, "KOD_TOV" INTEGER NOT NULL, "KOD_PR" INTEGER NOT NULL, "KOD_EI" INTEGER NOT NULL, "KOD_VC" INTEGER NOT NULL, PRIMARY KEY ("NOMER") );
/* Table: POZIC_VEDOM, Owner: SYSDBA */
CREATE TABLE "POZIC_VEDOM" ( "NOMER" INTEGER NOT NULL, "KOL" NUMERIC(8, 2) NOT NULL, "KOD_TOV" INTEGER NOT NULL, "KOD_PPR" INTEGER NOT NULL, "KOD_EI" INTEGER NOT NULL, "KOD_VED" INTEGER NOT NULL, PRIMARY KEY ("NOMER") );
/* Table: PRICE, Owner: SYSDBA */
CREATE TABLE "PRICE" ( "NOMER" INTEGER NOT NULL, "DATA_P" DATE NOT NULL, PRIMARY KEY ("NOMER") );
/* Table: STRUCT_EDIN, Owner: SYSDBA */
CREATE TABLE "STRUCT_EDIN" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(40) NOT NULL, "K_NAZV" VARCHAR(15), "KOD_SE" INTEGER, "KOD_T_SE" INTEGER NOT NULL, UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: TELEFON, Owner: SYSDBA */
CREATE TABLE "TELEFON" ( "KOD" INTEGER NOT NULL, "NOMER" VARCHAR(20) NOT NULL, "KOD_TT" INTEGER NOT NULL, "KOD_SE" INTEGER, "KOD_FL" INTEGER, UNIQUE ("NOMER"), PRIMARY KEY ("KOD") );
/* Table: TIP_NAS_PUNKTA, Owner: SYSDBA */
CREATE TABLE "TIP_NAS_PUNKTA" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(20) NOT NULL, "K_NAZV" VARCHAR(10), UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: TIP_STRUCT_EDIN, Owner: SYSDBA */
CREATE TABLE "TIP_STRUCT_EDIN" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(40) NOT NULL, "K_NAZV" VARCHAR(15), UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: TIP_TEL, Owner: SYSDBA */
CREATE TABLE "TIP_TEL" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(40) NOT NULL, "K_NAZV" VARCHAR(15), UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: TIP_TOVARA, Owner: SYSDBA */
CREATE TABLE "TIP_TOVARA" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(40) NOT NULL, "K_NAZV" VARCHAR(15), UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: TIP_ULICI, Owner: SYSDBA */
CREATE TABLE "TIP_ULICI" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(20) NOT NULL, "K_NAZV" VARCHAR(10), UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: TIP_VED, Owner: SYSDBA */
CREATE TABLE "TIP_VED" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(40) NOT NULL, "K_NAZV" VARCHAR(15), UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: TOVAR, Owner: SYSDBA */
CREATE TABLE "TOVAR" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(40) NOT NULL, "K_NAZV" VARCHAR(15), "KOD_TIPA" INTEGER NOT NULL, UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: ULICA, Owner: SYSDBA */
CREATE TABLE "ULICA" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(40) NOT NULL, "KOD_TIPA" INTEGER NOT NULL, UNIQUE ("NAZV"), PRIMARY KEY ("KOD") );
/* Table: VEDOMOST, Owner: SYSDBA */
CREATE TABLE "VEDOMOST" ( "NOMER" INTEGER NOT NULL, "DATA_V" DATE NOT NULL, "KOD_TV" INTEGER NOT NULL, "KOD_SE_SC" INTEGER NOT NULL, "KOD_SE_PP" INTEGER, "KOD_FL_PP" INTEGER, "KOD_MEN" INTEGER NOT NULL, "KOD_ZAV" INTEGER NOT NULL, PRIMARY KEY ("NOMER") );
/* Table: VID_CENI, Owner: SYSDBA */
CREATE TABLE "VID_CENI" ( "KOD" INTEGER NOT NULL, "NAZV" VARCHAR(20) NOT NULL, "K_NAZV" VARCHAR(10), UNIQUE ("NAZV"), PRIMARY KEY ("KOD") ); ALTER TABLE "ADRES" ADD FOREIGN KEY ("KOD_SE") REFERENCES "STRUCT_EDIN" ("KOD"); ALTER TABLE "ADRES" ADD FOREIGN KEY ("KOD_FL") REFERENCES "FIZ_LICO" ("T_NOMER"); ALTER TABLE "ADRES" ADD FOREIGN KEY ("KOD_UL") REFERENCES "ULICA" ("KOD"); ALTER TABLE "ADRES" ADD FOREIGN KEY ("KOD_NP") REFERENCES "NAS_PUNKT" ("KOD"); ALTER TABLE "DOGOVOR" ADD FOREIGN KEY ("KOD_DOL") REFERENCES "DOLGNOST" ("KOD"); ALTER TABLE "DOGOVOR" ADD FOREIGN KEY ("KOD_SE") REFERENCES "STRUCT_EDIN" ("KOD"); ALTER TABLE "DOGOVOR" ADD FOREIGN KEY ("KOD_FL") REFERENCES "FIZ_LICO" ("T_NOMER"); ALTER TABLE "FIZ_LICO" ADD FOREIGN KEY ("KOD_P") REFERENCES "POL_FIZ_LICA" ("KOD"); ALTER TABLE "NAS_PUNKT" ADD FOREIGN KEY ("KOD_TIPA") REFERENCES "TIP_NAS_PUNKTA" ("KOD"); ALTER TABLE "POZIC_PR" ADD FOREIGN KEY ("KOD_TOV") REFERENCES "TOVAR" ("KOD"); ALTER TABLE "POZIC_PR" ADD FOREIGN KEY ("KOD_PR") REFERENCES "PRICE" ("NOMER"); ALTER TABLE "POZIC_PR" ADD FOREIGN KEY ("KOD_EI") REFERENCES "EDIN_IZM" ("KOD"); ALTER TABLE "POZIC_PR" ADD FOREIGN KEY ("KOD_VC") REFERENCES "VID_CENI" ("KOD"); ALTER TABLE "POZIC_VEDOM" ADD FOREIGN KEY ("KOD_TOV") REFERENCES "TOVAR" ("KOD"); ALTER TABLE "POZIC_VEDOM" ADD FOREIGN KEY ("KOD_PPR") REFERENCES "POZIC_PR" ("NOMER"); ALTER TABLE "POZIC_VEDOM" ADD FOREIGN KEY ("KOD_EI") REFERENCES "EDIN_IZM" ("KOD"); ALTER TABLE "POZIC_VEDOM" ADD FOREIGN KEY ("KOD_VED") REFERENCES "VEDOMOST" ("NOMER"); ALTER TABLE "STRUCT_EDIN" ADD FOREIGN KEY ("KOD_SE") REFERENCES "STRUCT_EDIN" ("KOD"); ALTER TABLE "STRUCT_EDIN" ADD FOREIGN KEY ("KOD_T_SE") REFERENCES "TIP_STRUCT_EDIN" ("KOD"); ALTER TABLE "TELEFON" ADD FOREIGN KEY ("KOD_TT") REFERENCES "TIP_TEL" ("KOD"); ALTER TABLE "TELEFON" ADD FOREIGN KEY ("KOD_SE") REFERENCES "STRUCT_EDIN" ("KOD"); ALTER TABLE "TELEFON" ADD FOREIGN KEY ("KOD_FL") REFERENCES "FIZ_LICO" ("T_NOMER"); ALTER TABLE "TOVAR" ADD FOREIGN KEY ("KOD_TIPA") REFERENCES "TIP_TOVARA" ("KOD"); ALTER TABLE "ULICA" ADD FOREIGN KEY ("KOD_TIPA") REFERENCES "TIP_ULICI" ("KOD"); ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_TV") REFERENCES "TIP_VED" ("KOD"); ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_SE_SC") REFERENCES "STRUCT_EDIN" ("KOD"); ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_SE_PP") REFERENCES "STRUCT_EDIN" ("KOD"); ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_FL_PP") REFERENCES "FIZ_LICO" ("T_NOMER"); ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_MEN") REFERENCES "DOGOVOR" ("NOMER"); ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_ZAV") REFERENCES "DOGOVOR" ("NOMER");
CREATE GENERATOR "GEN_ADRES"; CREATE GENERATOR "GEN_DOGOVOR"; CREATE GENERATOR "GEN_DOLGNOST"; CREATE GENERATOR "GEN_EDIN_IZM"; CREATE GENERATOR "GEN_FIZ_LICO"; CREATE GENERATOR "GEN_NAS_PUNKT"; CREATE GENERATOR "GEN_POL_FIZ_LICA"; CREATE GENERATOR "GEN_POZIC_PR"; CREATE GENERATOR "GEN_POZIC_VEDOM"; CREATE GENERATOR "GEN_PRICE"; CREATE GENERATOR "GEN_STRUCT_EDIN"; CREATE GENERATOR "GEN_TELEFON"; CREATE GENERATOR "GEN_TIP_NAS_PUNKTA"; CREATE GENERATOR "GEN_TIP_STRUCT_EDIN"; CREATE GENERATOR "GEN_TIP_TEL"; CREATE GENERATOR "GEN_TIP_TOVARA"; CREATE GENERATOR "GEN_TIP_ULICI"; CREATE GENERATOR "GEN_TIP_VED"; CREATE GENERATOR "GEN_TOVAR"; CREATE GENERATOR "GEN_ULICA"; CREATE GENERATOR "GEN_VEDOMOST"; CREATE GENERATOR "GEN_VID_CENI";
/* View: VIEW_PRICE_R, Owner: SYSDBA */
CREATE VIEW "VIEW_PRICE_R" ( "NAZV", "CENA" ) AS
SELECT TOVAR.NAZV, POZIC_PR.CENA FROM TOVAR, POZIC_PR WHERE POZIC_PR.KOD_PR=(SELECT MAX(PRICE.NOMER) FROM PRICE) AND POZIC_PR.KOD_TOV=TOVAR.KOD AND POZIC_PR.KOD_VC=1 ;
/* View: VIEW_PRICE_M, Owner: SYSDBA */
CREATE VIEW "VIEW_PRICE_M" ( "NAZV", "CENA" ) AS
SELECT TOVAR.NAZV, POZIC_PR.CENA FROM TOVAR, POZIC_PR WHERE POZIC_PR.KOD_PR=(SELECT MAX(PRICE.NOMER) FROM PRICE) AND POZIC_PR.KOD_TOV=TOVAR.KOD AND POZIC_PR.KOD_VC=2 ;
/* View: VIEW_PRICE_O, Owner: SYSDBA */
CREATE VIEW "VIEW_PRICE_O" ( "NAZV", "CENA" ) AS
SELECT TOVAR.NAZV, POZIC_PR.CENA FROM TOVAR, POZIC_PR WHERE POZIC_PR.KOD_PR=(SELECT MAX(PRICE.NOMER) FROM PRICE) AND POZIC_PR.KOD_TOV=TOVAR.KOD AND POZIC_PR.KOD_VC=3 ;
/* View: VIEW_PRICE_Z, Owner: SYSDBA */
CREATE VIEW "VIEW_PRICE_Z" ( "NAZV", "CENA" ) AS
SELECT TOVAR.NAZV, POZIC_PR.CENA FROM TOVAR, POZIC_PR WHERE POZIC_PR.KOD_PR=(SELECT MAX(PRICE.NOMER) FROM PRICE) AND POZIC_PR.KOD_TOV=TOVAR.KOD AND POZIC_PR.KOD_VC=4 ;
/* View: VIEW_PRIH_VED, Owner: SYSDBA */
CREATE VIEW "VIEW_PRIH_VED" ( "NOMER", "DATA_V", "NAZV_T", "KOL", "CENA", "SUMMA" ) AS
SELECT VEDOMOST.NOMER, VEDOMOST.DATA_V, TOVAR.NAZV, POZIC_VEDOM.KOL, POZIC_PR.CENA, POZIC_VEDOM.KOL * POZIC_PR.CENA FROM VEDOMOST, TOVAR, POZIC_VEDOM, POZIC_PR WHERE VEDOMOST.NOMER=POZIC_VEDOM.KOD_VED AND TOVAR.KOD=POZIC_VEDOM.KOD_TOV AND VEDOMOST.KOD_TV=1 AND POZIC_VEDOM.KOD_PPR=POZIC_PR.NOMER ;
/* View: VIEW_RAS_VED, Owner: SYSDBA */
CREATE VIEW "VIEW_RAS_VED" ( "NOMER", "DATA_V", "NAZV_T", "KOL", "CENA", "SUMMA" ) AS
SELECT VEDOMOST.NOMER, VEDOMOST.DATA_V, TOVAR.NAZV, POZIC_VEDOM.KOL, POZIC_PR.CENA, POZIC_VEDOM.KOL * POZIC_PR.CENA FROM VEDOMOST, TOVAR, POZIC_VEDOM, POZIC_PR WHERE VEDOMOST.NOMER=POZIC_VEDOM.KOD_VED AND TOVAR.KOD=POZIC_VEDOM.KOD_TOV AND VEDOMOST.KOD_TV=2 AND POZIC_VEDOM.KOD_PPR=POZIC_PR.NOMER ; ALTER TABLE "DOGOVOR" ADD CHECK(KOD_DOL>0);
ALTER TABLE "DOGOVOR" ADD CHECK(KOD_SE>0);
ALTER TABLE "DOGOVOR" ADD CHECK(KOD_FL>0);
ALTER TABLE "FIZ_LICO" ADD CHECK(KOD_P>0);
ALTER TABLE "TIP_VED" ADD CHECK(KOD>0);
ALTER TABLE "PRICE" ADD CHECK(NOMER>0);
ALTER TABLE "POZIC_PR" ADD CHECK(NOMER>0);
ALTER TABLE "POZIC_PR" ADD CHECK(CENA>0);
ALTER TABLE "POZIC_PR" ADD CHECK(KOD_TOV>0);
ALTER TABLE "POZIC_PR" ADD CHECK(KOD_PR>0);
ALTER TABLE "POZIC_PR" ADD CHECK(KOD_EI>0);
ALTER TABLE "POZIC_PR" ADD CHECK(KOD_VC>0);
ALTER TABLE "VEDOMOST" ADD CHECK(NOMER>0);
ALTER TABLE "VEDOMOST" ADD CHECK(KOD_TV>0);
ALTER TABLE "VEDOMOST" ADD CHECK(KOD_SE_SC>0);
ALTER TABLE "VEDOMOST" ADD CHECK(KOD_MEN>0);
ALTER TABLE "VEDOMOST" ADD CHECK(KOD_ZAV>0);
ALTER TABLE "TIP_TOVARA" ADD CHECK(KOD>0);
ALTER TABLE "POZIC_VEDOM" ADD CHECK(NOMER>0);
ALTER TABLE "POZIC_VEDOM" ADD CHECK(KOL>0);
ALTER TABLE "POZIC_VEDOM" ADD CHECK(KOD_TOV>0);
ALTER TABLE "POZIC_VEDOM" ADD CHECK(KOD_PPR>0);
ALTER TABLE "POZIC_VEDOM" ADD CHECK(KOD_EI>0);
ALTER TABLE "POZIC_VEDOM" ADD CHECK(KOD_VED>0);
ALTER TABLE "TIP_TEL" ADD CHECK(KOD>0);
ALTER TABLE "TELEFON" ADD CHECK(KOD>0);
ALTER TABLE "TELEFON" ADD CHECK(KOD_TT>0);
ALTER TABLE "POL_FIZ_LICA" ADD CHECK(KOD>0);
ALTER TABLE "TOVAR" ADD CHECK(KOD>0);
ALTER TABLE "TOVAR" ADD CHECK(KOD_TIPA>0);
ALTER TABLE "VID_CENI" ADD CHECK(KOD>0);
ALTER TABLE "TIP_STRUCT_EDIN" ADD CHECK(KOD>0);
ALTER TABLE "STRUCT_EDIN" ADD CHECK(KOD>0);
ALTER TABLE "STRUCT_EDIN" ADD CHECK(KOD_T_SE>0);
ALTER TABLE "TIP_ULICI" ADD CHECK(KOD>0);
ALTER TABLE "ULICA" ADD CHECK(KOD>0);
ALTER TABLE "ULICA" ADD CHECK(KOD_TIPA>0);
ALTER TABLE "TIP_NAS_PUNKTA" ADD CHECK(KOD>0);
ALTER TABLE "NAS_PUNKT" ADD CHECK(KOD>0);
ALTER TABLE "NAS_PUNKT" ADD CHECK(KOD_TIPA>0);
ALTER TABLE "FIZ_LICO" ADD CHECK(T_NOMER>0);
ALTER TABLE "DOLGNOST" ADD CHECK(KOD>0);
ALTER TABLE "EDIN_IZM" ADD CHECK(KOD>0);
ALTER TABLE "ADRES" ADD CHECK(NOMER>0);
ALTER TABLE "ADRES" ADD CHECK(DOM>0);
ALTER TABLE "ADRES" ADD CHECK(KOD_UL>0);
ALTER TABLE "ADRES" ADD CHECK(KOD_NP>0);
ALTER TABLE "DOGOVOR" ADD CHECK(NOMER>0);
COMMIT WORK; SET AUTODDL OFF;
/* Stored procedures */
CREATE PROCEDURE "DISKAN" ( "PKODVID" INTEGER, "PDATE" DATE ) RETURNS ( "KOL_ED" FLOAT, "SUMMA" FLOAT ) AS BEGIN EXIT; END ;
ALTER PROCEDURE "DISKAN" ( "PKODVID" INTEGER, "PDATE" DATE ) RETURNS ( "KOL_ED" FLOAT, "SUMMA" FLOAT ) AS begin
select sum(POZIC_VEDOM.KOL) from VEDOMOST, POZIC_VEDOM, POZIC_PR where (POZIC_VEDOM.KOD_VED=VEDOMOST.NOMER) AND (VEDOMOST.KOD_TV=2) AND (VEDOMOST.DATA_V=:"PDATE") AND (POZIC_VEDOM.KOD_PPR=POZIC_PR.NOMER) AND (POZIC_PR.KOD_VC=:"PKODVID") into :kol_ed;
select sum(POZIC_VEDOM.KOL*POZIC_PR.CENA) from VEDOMOST, POZIC_VEDOM, POZIC_PR where (POZIC_VEDOM.KOD_VED=VEDOMOST.NOMER) AND (VEDOMOST.KOD_TV=2) AND (VEDOMOST.DATA_V=:"PDATE") AND (POZIC_VEDOM.KOD_PPR=POZIC_PR.NOMER) AND (POZIC_PR.KOD_VC=:"PKODVID") into :summa;
exit; end ;
COMMIT WORK; SET AUTODDL ON;
/* Triggers only will work for SQL triggers */
CREATE TRIGGER "BEF_INS_ADRES" FOR "ADRES" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.NOMER = GEN_ID(GEN_ADRES,1); END ;
CREATE TRIGGER "BEF_INS_DOGOVOR" FOR "DOGOVOR" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.NOMER = GEN_ID(GEN_DOGOVOR,1); END ; CREATE TRIGGER "BEF_INS_DOLGNOST" FOR "DOLGNOST" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_DOLGNOST,1); END ;
CREATE TRIGGER "BEF_INS_EDIN_IZM" FOR "EDIN_IZM" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_EDIN_IZM,1); END ;
CREATE TRIGGER "BEF_INS_FIZ_LICO" FOR "FIZ_LICO" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.T_NOMER = GEN_ID(GEN_FIZ_LICO,1); END ;
CREATE TRIGGER "BEF_INS_NAS_PUNKT" FOR "NAS_PUNKT" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_NAS_PUNKT,1); END ;
CREATE TRIGGER "BEF_INS_POL_FIZ_LICA" FOR "POL_FIZ_LICA" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_POL_FIZ_LICA,1); END ;
CREATE TRIGGER "BEF_INS_POZIC_PR" FOR "POZIC_PR" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.NOMER = GEN_ID(GEN_POZIC_PR,1); END ;
CREATE TRIGGER "BEF_INS_POZIC_VEDOM" FOR "POZIC_VEDOM" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.NOMER = GEN_ID(GEN_POZIC_VEDOM,1); END ;
CREATE TRIGGER "BEF_INS_PRICE" FOR "PRICE" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.NOMER = GEN_ID(GEN_PRICE,1); END ; CREATE TRIGGER "BEF_INS_STRUCT_EDIN" FOR "STRUCT_EDIN" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_STRUCT_EDIN,1); END ;
CREATE TRIGGER "BEF_INS_TELEFON" FOR "TELEFON" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_TELEFON,1); END ;
CREATE TRIGGER "BEF_INS_TIP_NAS_PUNKTA" FOR "TIP_NAS_PUNKTA" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_TIP_NAS_PUNKTA,1); END ;
CREATE TRIGGER "BEF_INS_TIP_STRUCT_EDIN" FOR "TIP_STRUCT_EDIN" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_TIP_STRUCT_EDIN,1); END ;
CREATE TRIGGER "BEF_INS_TIP_TEL" FOR "TIP_TEL" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_TIP_TEL,1); END ;
CREATE TRIGGER "BEF_INS_TIP_TOVARA" FOR "TIP_TOVARA" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_TIP_TOVARA,1); END ;
CREATE TRIGGER "BEF_INS_TIP_ULICI" FOR "TIP_ULICI" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_TIP_ULICI,1); END ;
CREATE TRIGGER "BEF_INS_TIP_VED" FOR "TIP_VED" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_TIP_VED,1); END ; CREATE TRIGGER "BEF_INS_TOVAR" FOR "TOVAR" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_TOVAR,1); END ;
CREATE TRIGGER "BEF_INS_ULICA" FOR "ULICA" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_ULICA,1); END ;
CREATE TRIGGER "BEF_INS_VEDOMOST" FOR "VEDOMOST" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.NOMER = GEN_ID(GEN_VEDOMOST,1); END ;
CREATE TRIGGER "BEF_INS_VID_CENI" FOR "VID_CENI" ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.KOD = GEN_ID(GEN_VID_CENI,1); END ;
COMMIT WORK ;
/* Grant Roles for this database */
/* Role: "ROLE_ADMINISTRATOR", Owner: SYSDBA */
CREATE ROLE "ROLE_ADMINISTRATOR"; CREATE ROLE "ROLE_MENEDGER"; CREATE ROLE "ROLE_PROGRAMMIST"; CREATE ROLE "ROLE_ZAVSCLADOM";
/* Grant permissions for this database */
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "ADRES" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "ADRES" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "ADRES" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "ADRES" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "DOGOVOR" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "DOGOVOR" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "DOGOVOR" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "DOGOVOR" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "DOLGNOST" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "DOLGNOST" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "DOLGNOST" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "DOLGNOST" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "EDIN_IZM" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "EDIN_IZM" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "EDIN_IZM" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "EDIN_IZM" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "FIZ_LICO" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "FIZ_LICO" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "FIZ_LICO" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "FIZ_LICO" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "NAS_PUNKT" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "NAS_PUNKT" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "NAS_PUNKT" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "NAS_PUNKT" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "POL_FIZ_LICA" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "POL_FIZ_LICA" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "POL_FIZ_LICA" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "POL_FIZ_LICA" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "POZIC_PR" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "POZIC_PR" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "POZIC_PR" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "POZIC_PR" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "POZIC_VEDOM" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "POZIC_VEDOM" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "POZIC_VEDOM" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "POZIC_VEDOM" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "PRICE" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "PRICE" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "PRICE" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "PRICE" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "STRUCT_EDIN" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "STRUCT_EDIN" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "STRUCT_EDIN" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "STRUCT_EDIN" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TELEFON" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "TELEFON" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TELEFON" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "TELEFON" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_NAS_PUNKTA" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "TIP_NAS_PUNKTA" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_NAS_PUNKTA" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "TIP_NAS_PUNKTA" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_STRUCT_EDIN" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "TIP_STRUCT_EDIN" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_STRUCT_EDIN" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "TIP_STRUCT_EDIN" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_TEL" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "TIP_TEL" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_TEL" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "TIP_TEL" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_TOVARA" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "TIP_TOVARA" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_TOVARA" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "TIP_TOVARA" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_ULICI" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "TIP_ULICI" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_ULICI" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "TIP_ULICI" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_VED" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "TIP_VED" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_VED" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "TIP_VED" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TOVAR" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "TOVAR" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TOVAR" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "TOVAR" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "ULICA" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "ULICA" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "ULICA" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "ULICA" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VEDOMOST" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "VEDOMOST" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VEDOMOST" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "VEDOMOST" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VID_CENI" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "VID_CENI" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VID_CENI" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "VID_CENI" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_M" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "VIEW_PRICE_M" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_M" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "VIEW_PRICE_M" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_O" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "VIEW_PRICE_O" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_O" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "VIEW_PRICE_O" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_R" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "VIEW_PRICE_R" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_R" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "VIEW_PRICE_R" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_Z" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "VIEW_PRICE_Z" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_Z" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "VIEW_PRICE_Z" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRIH_VED" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "VIEW_PRIH_VED" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRIH_VED" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "VIEW_PRIH_VED" TO "ROLE_ZAVSCLADOM"; GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_RAS_VED" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT INSERT, SELECT, UPDATE ON "VIEW_RAS_VED" TO "ROLE_MENEDGER"; GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_RAS_VED" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT SELECT ON "VIEW_RAS_VED" TO "ROLE_ZAVSCLADOM"; GRANT "ROLE_ADMINISTRATOR" TO ADMINISTRATOR;
GRANT "ROLE_MENEDGER" TO MENEDGER;
GRANT "ROLE_PROGRAMMIST" TO PROGRAMMIST;
GRANT "ROLE_ZAVSCLADOM" TO ZAVSCLADOM;
GRANT EXECUTE ON PROCEDURE "DISKAN" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION; GRANT EXECUTE ON PROCEDURE "DISKAN" TO "ROLE_MENEDGER"; GRANT EXECUTE ON PROCEDURE "DISKAN" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION; GRANT EXECUTE ON PROCEDURE "DISKAN" TO "ROLE_ZAVSCLADOM"; Приложение В (обязательное) Листинг программы
В отдельном файле |
|
Последнее изменение этой страницы: 2016-06-09 lectmania.ru. Все права принадлежат авторам данных материалов. В случае нарушения авторского права напишите нам сюда... |