corr_app_5b1.sql
----------------------------------------------------------------------------

create table archive(ids int, idc int, idv int, jour int, avoir int);
select * from archive;

create or replace trigger ex5b1
    before delete -- after ok aussi
    on sejour
    for each row
declare
    qqchose client.avoir%type;
begin
    select avoir
        into qqchose
        from client
	where idc = :old.idc;
    insert into archive values(
        :old.ids, :old.idc,:old.idv,:old.jour,qqchose);
    dbms_output.put_line(:old.ids||' '||:old.idc||' '||:old.idv||' '||
                         :old.jour||' '||qqchose);
end;
/

delete sejour where ids = 106;

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

create or replace trigger ex1
    -- before delete 
    after delete 
    on t
begin
    dbms_output.put_line('ex1 : un delete sur t a eu lieu');
end;
/

create or replace trigger ex2
    before delete  -- rem : after et before meme effet
    on t
    for each row  -- corps declenche pour chaque ligne
begin
    dbms_output.put_line('ex2 : un delete sur une ligne de t a eu lieu');
end;
/

create or replace trigger ex3
    before delete 
    on t
    for each row
begin
    dbms_output.put_line('ex3 : ligne de t detruite : '||:old.a||','||:old.b);
      -- :old : "variable ligne" contenant la ligne detruite courante (cf
      -- clause delete) ; rem : que si for each row
end;
/

create or replace trigger ex4
    after update
    on t
    for each row
        when (old.b > new.b)  -- condition sur ligne courante concernee update
                              -- rem : que si for each row
                              -- si pas vraie trigger ne se declenche pas
begin
    dbms_output.put_line('ancien b : '||:old.b||', nouveau : '||:new.b);
       -- :old : ligne avant update, :new : ligne apres, cf after update
end;
/

-------------------------------------------------------------------------------
-- ex : meme question, mais memoriser ancienne valeur de b dans nouvelle table

drop table old_t_b;
create table old_t_b(old_b int);
select * from old_t_b;

create or replace trigger ex4bis
    after update
    on t
    for each row
        when (old.b > new.b)
begin
    insert into old_t_b values(:old.b);
    dbms_output.put_line('insere '||:old.b||' dans old_t_b');
end;
/

-- re-remplir t
update t set b = 11 where a = 1;
update t set b = 6 where a = 4;
select * from old_t_b;

-- rem : tous les trigers sur update t se declenchent
-- rem : s'il y a un trigger sur old_t_b, le insert peut le declencher, etc.,
-- d'ou recursion possible
-------------------------------------------------------------------------------
--ex : bug : when et :old specifiques a for each row 

create or replace trigger ex1bis
    before delete 
    on t
    -- for each row
    --    when (5<7)  -- bug : decommenter pour tester
           -- ORA-04077: WHEN clause cannot be used with table level triggers
begin
    dbms_output.put_line('un delete sur t a eu lieu');
    -- dbms_output.put_line(:old.a);  -- bug : decommenter pour tester
        -- ORA-04082: NEW or OLD references not allowed in table level triggers
end;
/

-------------------------------------------------------------------------------
-- ex : illustrer difference entre before et after

create or replace trigger ex5
    before insert  -- affiche : 5
    on t
declare 
    n integer;
begin
    select count(*) into n from t;
    dbms_output.put_line('taille t before (ex5): '||n);
end;
/

create or replace trigger ex6
    after insert or update of a  -- affiche : 6  -- rem : udpate pas utilise
    on t
declare 
    n integer;
begin
    select count(*) into n from t;
    dbms_output.put_line('taille t after (ex6): '||n);
end;
/

-- rem : un trigger for each row ne peut pas acceder a une table en cours de 
-- modification
create or replace trigger ex6bis
    after insert or update of a 
    on t
    for each row
declare 
    n integer;
begin
    select count(*) into n from t;
    -- ORA-04091: table WALLER_A.T is mutating, trigger/function may not see it
    dbms_output.put_line('taille t after + for each row (ex7): '||n);
end;
/

-- re-remplir t
insert into t values(11, 11);

drop trigger ex6bis;

-------------------------------------------------------------------------------
-- gestion de contrainte non SQL : premiere tentative (insuffisante)
-------------------------------------------------------------------------------
-- ex : somme t.a doit etre inferieure ou egale a 20

drop trigger ex5;
drop trigger ex6;

create or replace trigger ex7
    after insert -- after oblige, cause verifier apres mise a jour -- or update
    on t
declare 
    n integer;
begin
    select sum(a) into n from t;
    dbms_output.put_line('ex7 : declenchement, sum t.a : '||n);
    if n > 20 -- la somme !
        then dbms_output.put_line('constraint violated : taille t>20 : '||n);
    end if;
end;
/

-- re-remplir t
insert into t values(1,1);
insert into t values(11,11);

-- rem : message affiche (possible aussi memoriser valeur dans base), mais la
-- mise a jour n'a pas ete empechee ; sera gere parfaitement avec exceptions

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