caca

drop sequence CINE_CLIENT_SEQ;
create sequence CINE_CLIENT_SEQ;

drop sequence CINE_EMPRUNT_SEQ;
create sequence cine_emprunt_seq;

drop table CINE_EMPRUNT;
drop table CINE_CLIENT;

create table CINE_CLIENT(
  NCLIENT      integer,
  NOM          varchar(20)
                constraint CK_CINE_CLIENT_NOM
                check(nom=upper(nom)),
  PRENOM       varchar(20)
                constraint CK_CINE_CLIENT_PRENOM
                check(prenom=initcap(prenom)),
  VILLE        varchar(20)
                constraint CK_CINE_CLIENT_VILLE
                check(ville=initcap(ville)),
  ANCIENNETE   integer,
  nbre_emprunt integer,
  constraint PK_CINE_CLIENT primary key(NCLIENT)
);


create table CINE_EMPRUNT(
  NEMPRUNT      INTEGER,
  NCLIENT       integer,
  NFILM         integer,
  DATEEMPRUNT   date,
  retour        char(3)
                not null
                constraint ck_cine_emprunt_date
                check (retour in('OUI','NON')),
  constraint PK_CINE_EMPRUNT primary key(NEMPRUNT)
);


create or replace trigger CINE_CLIENT_BIUR
before insert or update
on CINE_CLIENT
for each row
begin
  if INSERTING then
    :new.NCLIENT := CINE_CLIENT_SEQ.NEXTVAL;
    :new.NOM := UPPER(:new.NOM);
    :new.PRENOM := INITCAP(:new.PRENOM);
    :new.VILLE := INITCAP(:new.VILLE);
  else
    :new.NCLIENT := :old.NCLIENT;
    :new.NOM := :old.NOM;
    :new.PRENOM := :old.PRENOM;
  end if;
  if :new.ANCIENNETE is null then
  :new.ANCIENNETE := 0;
  end if;
  if :new.NBRE_EMPRUNT is null then
  :new.NBRE_EMPRUNT := 0;
  end if;
end;
/

create or replace trigger cine_emprunt_biur
before insert or update
on cine_emprunt
for each row
begin
  if inserting then
    :new.nemprunt := cine_emprunt_seq.nextval;
  else
    :new.nemprunt := :old.nemprunt;
    :new.dateemprunt := :old.dateemprunt;
  end if;
  if :new.dateemprunt is null then
  :new.dateemprunt :=sysdate;
  end if;
  if :new.retour is null then
  :new.retour := 'NON';
  end if;
end;
/

create or replace trigger cine_emprunt_biur2
before insert or update
on cine_emprunt
for each row
declare
count_client number(1);
count_film number(1);
nempr integer;
begin
select count(*)
into count_client
from cine_client D
where d.nclient = :new.nclient;

select count(*)
into count_film
from cine_film f
where f.nfilm= :new.nfilm;

select nbre_emprunt
into nempr
from cine_client
where nclient= :new.nclient;


if count_client = 0 then
raise_application_error(-20001, 'Nclient est inconnu');
end if;

if count_film = 0 then
raise_application_error(-20002,'Nfilm est inconnu');
end if;

if inserting then
if (nempr<=2) then
update cine_client
set nbre_emprunt = nbre_emprunt+1
where nclient = :new.nclient;
else raise_application_error(-20003,'Nbre max d''emprunt atteint');

end if;
end if;

if updating then
if :new.retour = 'OUI' then
update cine_client
set nbre_emprunt = nbre_emprunt-1
where nclient = :new.nclient;
end if;
end if;
end;
/

insert into CINE_CLIENT values(12, 'duPoNt','jeaN','marseille',30,null);
insert into CINE_CLIENT values(17, 'marie',null,null,null,null);

insert into Cine_emprunt values (null, 2, 2, null,null);
insert into cine_emprunt values (null, 2, 3, null,null);
insert into cine_emprunt values (null, 2, 4, null,null);
insert into Cine_emprunt values (null, 2, 5, null,null);

select * from CINE_CLIENT;

update cine_emprunt
set retour='OUI'
where nemprunt=1;

select * from cine_emprunt;
select * from cine_client;