create table prof(
    ide int,
    nom varchar(20),
    specialite varchar(20),
    serviceMex int
);

create table cours(
    idc int,
    titre varchar(10),
    specialite VARCHAR(10),
    volume int,
    semestre int,
    ide int
);

create table limite(
    valeur int
);

insert into prof values (1, 'Bruce', 'BD', 175);
insert into prof values (2, 'waller', 'BD', 150);
insert into cours values (1, 'SGBD ', 'BD',48,2,1);
insert into cours values (2, 'BDD', 'BD',60,1,null);
insert into cours values (3, 'BLA ', 'BD',48,2,1);
insert into cours values (4, 'AVN', 'BD',60,1,2);

drop table archive;
create table archive(
    idc int,
    idpa int,
    idpn int, 
    idcnp int
);
create table cour_prof(
    idc int,
    ide int
);
insert into cour_prof values (1,1);
insert into cour_prof values (4,1);
insert into cour_prof values (3,1);
insert into cour_prof values (2,2);

create or replace TRIGGER modif_affect
  before update 
   on cours 
   for each row
 declare 
   cursor c is 
    select idc
    from  cour_prof
    where ide = :new.ide; 
  l_idc cours.idc%type; 
 begin 
 dbms_output.put_line(:new.ide||' '|| :old.ide);
  open c;   
  fetch c into l_idc;
  while c%found loop
    insert into archive values(:old.idc, :old.ide, :new.ide, l_idc );
    fetch c into l_idc;
 end loop;
end;
/






create or replace TRIGGER modif_affect
  before update 
   on cours 
   for each row
 declare 
   cursor c is 
    select idc
    from  cours 
    where ide = :old.ide; 
  l_idc cours.idc%type; 
 begin 
 dbms_output.put_line(:new.ide||' '|| :old.ide);
  open c;   
  fetch c into l_idc;
  while c%found loop
    insert into archive values(:old.idc, :old.ide, :new.ide, l_idc );
    fetch c into l_idc;
 end loop;
end;
/


