Код: Выделить всё
/* таблица до обновления */
CREATE TABLE P_PRK (
IDR D_CODE NOT NULL /* D_CODE = INTEGER */,
NOM D_NMK /* D_NMK = VARCHAR(100) */,
DAT_PRK DATE
);
ALTER TABLE P_PRK ADD PRIMARY KEY (IDR);
SET TERM ^ ;
CREATE TRIGGER TBI_P_PRK FOR P_PRK
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.IDR = GEN_ID(GEN_PRK, 1);
END
^
SET TERM ; ^
Код: Выделить всё
/* Скрипт модификации */
RECREATE TABLE P_PRK (
IDR D_CODE NOT NULL,
COD_FAK D_CODE,
COD_SPR D_CODE,
N_PRK D_NSM,
DAT_PRK D_DAT,
OB D_NMK,
STV D_NMP,
DAT_REC D_DAT DEFAULT CURRENT_DATE,
PRIMARY KEY (IDR)
);
CREATE TABLE P_PNP (
IDR D_CODE NOT NULL,
COD_PRK D_CODE,
COD_PUN D_CODE,
N_PUN D_CODE,
OSN D_NMK,
DAT_REC D_DAT DEFAULT CURRENT_DATE,
PRIMARY KEY (IDR)
);
CREATE TABLE S_PUN (
CODE D_CODE NOT NULL,
NMK_R D_NMK,
NMP_R D_NMK,
PRIMARY KEY (CODE)
);
CREATE TABLE S_ACT (
CODE D_CODE NOT NULL,
COD_PUN D_CODE NOT NULL,
NMK_ACT D_NSM,
TBL D_NSM,
NVL D_NSM,
PRIMARY KEY (CODE)
);
CREATE TABLE P_STU (
IDR D_CODE NOT NULL,
ID_STU D_CODE,
COD_PNP D_CODE,
FAM_R D_NSM,
IMA_R D_NSM,
OTH_R D_NSM,
DAT_REC D_DAT DEFAULT CURRENT_DATE,
PRIMARY KEY (IDR)
);
CREATE TABLE P_UCH (
IDR D_CODE NOT NULL,
COD_PNP D_CODE NOT NULL,
COD_KRS D_CODE,
COD_FAK D_CODE,
COD_KAF D_CODE,
COD_KWL D_CODE,
COD_UST D_CODE,
COD_FOB D_CODE,
COD_FOP D_CODE,
COD_UAC D_CODE,
N_ZA4 VARCHAR(20),
N_GRP VARCHAR(20),
DAT_REC D_DAT DEFAULT CURRENT_DATE,
PRIMARY KEY (IDR)
);
ALTER TABLE P_PRK ADD FOREIGN KEY (COD_FAK) REFERENCES S_FAK (CODE) ON UPDATE NO ACTION ON DELETE NO ACTION ;
ALTER TABLE P_PNP ADD FOREIGN KEY (COD_PRK) REFERENCES P_PRK (IDR) ON UPDATE NO ACTION ON DELETE NO ACTION ;
ALTER TABLE P_PNP ADD FOREIGN KEY (COD_PUN) REFERENCES S_PUN (CODE) ON UPDATE NO ACTION ON DELETE NO ACTION ;
ALTER TABLE S_ACT ADD FOREIGN KEY (COD_PUN ) REFERENCES S_PUN (CODE) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE P_STU ADD FOREIGN KEY (COD_PNP) REFERENCES P_PNP (IDR) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE P_STU ADD FOREIGN KEY (ID_STU) REFERENCES I_STU (IDR) ON UPDATE NO ACTION ON DELETE NO ACTION ;
ALTER TABLE P_UCH ADD FOREIGN KEY (COD_PNP) REFERENCES P_PNP (IDR) ON UPDATE NO ACTION ON DELETE NO ACTION ;
COMMIT;
/* ------------ вставка/обновление записей ------------------*/
INSERT INTO S_PUN (CODE, NMK_R, NMP_R) VALUES(1, 'отчислить (ак.неусп.)', 'отчислить за академическую неуспеваемость');
INSERT INTO S_PUN (CODE, NMK_R, NMP_R) VALUES(2, 'отчислить (по собст.жел)', 'отчислить по собственному желанию');
INSERT INTO S_ACT (CODE, COD_PUN, NMK_ACT, TBL, NVL) VALUES(1, 1, 'отчисление', 'P_UCH', 'COD_UST = 3');
INSERT INTO S_ACT (CODE, COD_PUN, NMK_ACT, TBL, NVL) VALUES(2, 2, 'отчисление', 'P_UCH', 'COD_UST = 3');
COMMIT;
/* ------------ тригеры и хр.процедуры ------------------*/
SET TERM ^ ;
CREATE TRIGGER TBI_S_ACT FOR S_ACT
BEFORE INSERT
AS
BEGIN
IF (NEW.CODE IS NULL) THEN NEW.CODE = GEN_ID(GEN_CODE, 1);
END ^
CREATE TRIGGER TBI_S_PUN FOR S_PUN
BEFORE INSERT
AS
BEGIN
IF (NEW.CODE IS NULL) THEN NEW.CODE = GEN_ID(GEN_CODE, 1);
END ^
CREATE OR ALTER TRIGGER TBI_P_PRK FOR P_PRK /* CREATE TRIGGER дает такой же результат */
BEFORE INSERT
AS
BEGIN
IF (NEW.IDR IS NULL) THEN NEW.IDR = GEN_ID(GEN_PRK, 1);
END ^
CREATE TRIGGER TBI_P_PNP FOR P_PNP
BEFORE INSERT
AS
BEGIN
IF (NEW.IDR IS NULL) THEN NEW.IDR = GEN_ID(GEN_PRK, 1);
END ^
CREATE TRIGGER TBI_P_UCH FOR P_UCH
BEFORE INSERT
AS
BEGIN
IF (NEW.IDR IS NULL) THEN NEW.IDR = GEN_ID(GEN_PRK, 1);
END ^
CREATE TRIGGER TBI_P_STU FOR P_STU
BEFORE INSERT
AS
BEGIN
IF (NEW.IDR IS NULL) THEN NEW.IDR = GEN_ID(GEN_PRK, 1);
END ^
CREATE TRIGGER TBU_P_PNP FOR P_PNP
ACTIVE BEFORE UPDATE
AS
BEGIN
NEW.DAT_REC = CURRENT_DATE;
END ^
CREATE TRIGGER TBU_P_PRK FOR P_PRK
ACTIVE BEFORE UPDATE
AS
BEGIN
NEW.DAT_REC = CURRENT_DATE;
END ^
CREATE TRIGGER TBU_P_STU FOR P_STU
ACTIVE BEFORE UPDATE
AS
BEGIN
NEW.DAT_REC = CURRENT_DATE;
END ^
CREATE TRIGGER TBU_P_UCH FOR P_UCH
ACTIVE BEFORE UPDATE
AS
BEGIN
NEW.DAT_REC = CURRENT_DATE;
END ^
SET TERM ; ^
/* ------------ версия ------------------*/
UPDATE S_INFO
SET
NMK_R = '1.2.7'
WHERE CODE = -1;
COMMIT;
Код: Выделить всё
/*Скрипт добавления записей*/
INSERT INTO P_PRK (IDR, COD_FAK, COD_SPR, N_PRK, DAT_PRK, OB, STV)
VALUES(44, 1, 1, 'aa', CURRENT_DATE, 'aa', 'aa');/*отрабатывает*/
/*пробовал и COMMIT*/
INSERT INTO P_PNP (IDR, COD_PRK, COD_PUN, N_PUN, OSN)
VALUES(2, 44, 1, 1, 'bb'); /*здесь выдает ошибку*/
, без b/r.