-- ex_mutating.sql
-------------------------------------------------------------------------------------

set serveroutput on

drop table enseignant;
create table enseignant(
    ide int,
    nom varchar2(20),
    specialite varchar2(20),
    serviceMax int
);

drop table cours;
create table cours(
    idc int,
    titre varchar2(10),
    specialite varchar2(10),
    volume int,
    semestre int,
    ide int
);

drop table limite;
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_modif;
create table archive_modif(
    idc int,
    ide int,
    ide_new int
);

drop table archive_les_cours;
create table archive_les_cours(
    ide int, 
    idc int
);

-- impossible => changer enonce : 

create or replace TRIGGER traitement1
    before delete
    -- before update of ide
    on cours 
    for each row
begin 
    insert into archive_modif values(:old.idc, :old.ide, :new.ide);
    dbms_output.put_line(:new.ide||' '|| :old.ide);
    for x in (
        select idc
	    from cours
	    where ide = :old.ide) loop
        insert into archive_les_cours values(:new.ide, x.idc );
    end loop;
end;
/

show errors

delete cours;
insert into cours values (1, 'SGBD ', 'BD',48,2,1);
update cours set ide = 1 where idc = 2;

-------------------------------------------------------------------------------------
-- etu :
/*
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;
/
*/

-------------------------------------------------------------------------------------
