kyYum9Xd

1Trigger 4:
2
3create table matiere (
4	id serial primary key,
5	libelle text
6);
7
8create table professeur (
9	id serial primary key,
10	nom text,
11	prenom text
12);
13
14create table est_qualifie (
15	id_prof integer references professeur(id),
16	id_mati integer references matiere(id),
17	primary key (id_prof, id_mati)
18);
19
20create table classe (
21	id serial primary key,
22	libelle text
23);
24
25create table enseigne (
26	id_prof integer references professeur(id),
27	id_mati integer references matiere(id),
28	id_clas integer references classe(id),
29	primary key (id_prof, id_mati, id_clas)
30);
31
32insert into matiere (libelle) values ('Math'),('Français'),('Anglais'),('Sport'),('SVT'),('Histoire'),('Allemand'),('Espagnol');
33
34insert into professeur (nom, prenom) values ('John','Doe'),('Jane','Doe'),('Rogger','Rabbit'),('Marc','Eure'),('Claire','Semer'),('Pat','Rick');
35
36insert into classe (libelle) values ('SIO1'),('SIO2'),('STMG1'),('STMG2'),('CGO1'),('CGO3');
37
38insert into est_qualifie (id_prof, id_mati) values (1,1),(2,2),(3,3),(3,7),(3,8),(4,4),(5,5),(6,6);
39
40
41create or replace function verif_prof_qualif() returns trigger as $$
42begin
43if not exists (select * from est_qualifie where id_prof = new.id_prof and id_mati = new.id_mati) then
44	raise exception 'Prof non qualifié';
45end if;
46return new;
47end;
48$$ language 'plpgsql';
49
50create trigger trg_verif_prof_qualif before insert or update on enseigne for each row execute procedure verif_prof_qualif();
51
52insert into enseigne (id_prof, id_mati, id_clas) values (1,2,1);