CREATE GENERATOR GEN_CLIENT_ID_C;
CREATE TABLE CLIENT (
ID_C INTEGER,
FIO VARCHAR(50) NOT NULL,
PASPORT VARCHAR(50)
);
/******************************************************************************/
/**** Primary Keys ****/
/******************************************************************************/
ALTER TABLE CLIENT ADD PRIMARY KEY (ID_C);
/******************************************************************************/
/**** Triggers ****/
/******************************************************************************/
SET TERM ^;
/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/
/* Trigger: TRIG_CLIENT */
CREATE TRIGGER TRIG_CLIENT FOR CLIENT
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
IF (NEW.id_c IS NULL) THEN NEW.id_c = GEN_ID(gen_client_id_c,1);
END
^
DEAL:
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE GENERATOR GEN_DEAL_ID;
CREATE TABLE DEAL (
ID_D INTEGER,
ID_M INTEGER NOT NULL,
CL_ID INTEGER NOT NULL,
DEN NUMERIC(4,2),
D_D DATE
);
/******************************************************************************/
/**** Primary Keys ****/
/******************************************************************************/
ALTER TABLE DEAL ADD PRIMARY KEY (ID_D);
/******************************************************************************/
/**** Foreign Keys ****/
/******************************************************************************/
ALTER TABLE DEAL ADD FOREIGN KEY (CL_ID) REFERENCES CLIENT (ID_C) ON UPDATE CASCADE;
ALTER TABLE DEAL ADD FOREIGN KEY (ID_M) REFERENCES MOVIE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/
/* Trigger: DEC_MONEY */
CREATE TRIGGER DEC_MONEY FOR DEAL
ACTIVE AFTER INSERT POSITION 0
AS
declare variable x integer;
begin
Select Count(New.cl_id) from DEAL
into:x;
if (:x > 3) then
begin
update Deal
set Deal.den = (DEAL.den - DEAL.den/100*10)
where DEAL.id_d = New.id_d;
end
end
/* Trigger: SUB_MOVIE */
CREATE TRIGGER SUB_MOVIE FOR DEAL
ACTIVE AFTER INSERT POSITION 0
AS
begin
update movie
set movie.kol = movie.kol - 1
where movie.id = new.id_m;
end
/* Trigger: TRIG_DEAL_BI */
CREATE TRIGGER TRIG_DEAL_BI FOR DEAL
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
IF (NEW.Id_d IS NULL) THEN NEW.Id_d = GEN_ID(Gen_DEAL_ID,1);
END
MOVIE:
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE GENERATOR GEN_DEAL_ID;
CREATE TABLE DEAL (
ID_D INTEGER,
ID_M INTEGER NOT NULL,
CL_ID INTEGER NOT NULL,
DEN NUMERIC(4,2),
D_D DATE
);
/******************************************************************************/
/**** Primary Keys ****/
/******************************************************************************/
ALTER TABLE DEAL ADD PRIMARY KEY (ID_D);
/******************************************************************************/
/**** Foreign Keys ****/
/******************************************************************************/
ALTER TABLE DEAL ADD FOREIGN KEY (CL_ID) REFERENCES CLIENT (ID_C) ON UPDATE CASCADE;
ALTER TABLE DEAL ADD FOREIGN KEY (ID_M) REFERENCES MOVIE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/
/* Trigger: DEC_MONEY */
CREATE TRIGGER DEC_MONEY FOR DEAL
ACTIVE AFTER INSERT POSITION 0
AS
declare variable x integer;
begin
Select Count(cl_id) from DEAL where cl_id = New.cl_id group by cl_id
into:x;
if (:x > 3) then
begin
update Deal
set Deal.den = (DEAL.den - DEAL.den/100*10)
where DEAL.id_d = New.id_d;
end
end
/* Trigger: SUB_MOVIE */
CREATE TRIGGER SUB_MOVIE FOR DEAL
ACTIVE AFTER INSERT POSITION 0
AS
begin
update movie
set movie.kol = movie.kol - 1
where movie.id = new.id_m;
end
/* Trigger: TRIG_DEAL_BI */
CREATE TRIGGER TRIG_DEAL_BI FOR DEAL
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
IF (NEW.Id_d IS NULL) THEN NEW.Id_d = GEN_ID(Gen_DEAL_ID,1);
END
EXCEPTION:
CREATE EXCEPTION NO_DELETE 'Нельзя удалить фильм если он имеется на складе!';
VIEW:
CREATE VIEW BESTCLIENT(FIO)
AS
select FIO from client where id_c =(select cl from(select deal.cl_id as cl, count (cl_id) as col from deal group by deal.cl_id HAVING count (cl_id) >=All (select count (cl_id) as col from deal group by deal.cl_id)));