1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| create or replace trigger verif_valid_prereq
before update on inscription
for each row
declare
v_num number :=0;
v_codm varchar2(8);
v_compt number;
v_moy number := 0;
begin
select :new.numetud into v_num from inscription;
select codmodprereq into v_codm from prerequis
where codmodprereq = :new.codmod;
select count(codexam) into v_compt from examen
where codmod = v_codm;
if ((select count(note) from resultat
where numetud = v_num and codmod = v_codm) = v_compt) then
select round(avg(note),2) into v_moy from resultat
where numetud = v_num and codmod = v_codm;
if ((select noteprereq from prerequis
where codmodprereq = v_codm)<v_moy )
raise erreur;
end if;
else raise erreur;
end if;
exception
when erreur then Raise_application_error(-20114,
'Cet etudiant ne remplie pas les conditions pour s inscrire au
module '||:new.codmod);
end;
/ |
Partager