Forums PostgreSQL.fr

Kevin Côté

Membre

Trigger pour vérifier si un ajout concorde avec une autre table

Bonjour,
J'ai deux tables, une table "Compagnies" et une table "Admin".

Ma table "Compagnies" comprends 3 colonnes: id_comp, c_nom et MUS_NM_MUN qui est une clé secondaire avec la table "Admin" et qui représente le nom de la ville dans laquelle se situe la compagnie.

Je veux créer un trigger qui vérifie, lors de l'ajout ou de la modification d'une ligne dans la table compagnies, que la ville ajouté dans la colonne "MUS_NM_MUN" existe bien dans la table "Admin". Si la ville n'existe pas dans la table "Admin", le trigger refuse l'insertion et affiche un message d'erreur.

Voici le code que je pensais être correct mais qui ne fonctionne pas car il est possible d'ajouter des compagnies dont la ville n'existe pas. Je ne sais pas quoi faire avec le RETURN.  Pouvez vous me dire ce qui ne fonctionne pas dans ce code ?! Je suis sur que ce n'est pas bien compliqué....

create function verifier_ville() returns trigger as $verif_ville$
declare
ville_ajoutee character(100) := NEW."MUS_NM_MUN";
begin
if ville_ajoutee not like (select "MUS_NM_MUN" from "admin" where "MUS_NM_MUN" like ville_ajoutee) then
raise exception 'La ville ne fait pas partie du territoire du Québec';
end if;
return;
end;
$verif_ville$ LANGUAGE plpgsql;

create trigger $verif_ville$ before insert or update on compagnies
for eachrow execute procedure verifier_ville();

Merci beaucoup

MitsuTomoe

Membre

Re : Trigger pour vérifier si un ajout concorde avec une autre table

Au lieu d'un trigger, pourquoi ne pas déclarer une foreign key ?
ALTER TABLE ONLY compagnies
ADD CONSTRAINT ville_fkey FOREIGN KEY (mus_nm_mun) REFERENCES admin(mus_nm_mun) ON DELETE RESTRICT;

Sinon, pour le trigger, j'aurais fait :

create function verifier_ville() returns trigger as $verif_ville$
declare
ville_ajoutee varchar;
tst varchar;
begin
ville_ajoutee := NEW.mus_nm_mun;
select into tst mus_nm_mun from admin where mus_nm_mun like ville_ajoutee;
if not found then ....

Le nom de la table est-il vraiment en capitales ? Dans ce cas il faut bien mettre les "" .
Des experts ne sont pas loin, ils auront sûrement de meilleures suggestions.

Kevin Côté

Membre

Re : Trigger pour vérifier si un ajout concorde avec une autre table

Merci pour la réponse rapide. Je dois absolument utiliser un trigger et non une foreign key (c'est dans les conditions du travail).
Le nom de la table est effectivement en capitales.

Il n'y a cependant pas de return dans la fonction que tu me proposes et ca me donne cette erreur:

Erreur SQL :

ERROR:  control reached end of trigger procedure without RETURN
CONTEXT:  PL/pgSQL function "verifier_ville"
Dans l'instruction :
INSERT INTO "public"."Compagnies" ("id_comp", "c_nom", "MUS_NM_MUN") VALUES ('23', 'adfa', 'adasda')

Que devrais-je ajouter comme RETURN pour que la fonction fonctionne ? Merci beaucoup !

Kevin Côté

Membre

Re : Trigger pour vérifier si un ajout concorde avec une autre table

Voici la fonction avec tes conseils:

create or replace function verifier_ville() returns trigger as $verif_ville$
declare
ville_ajoutee varchar;
tst varchar;
begin
ville_ajoutee := NEW."MUS_NM_MUN";
select into tst "MUS_NM_MUN" from admin where "MUS_NM_MUN" like ville_ajoutee;
if not found then
raise notice 'Ca marche pas';
end if;
end;
$verif_ville$ LANGUAGE plpgsql;

Si je met return new; il ajoute le tuple à la base de donnée même si la ville n'existe pas...

Dernière modification par Kevin Côté (03/09/2012 00:27:13)

gleu

Administrateur

Re : Trigger pour vérifier si un ajout concorde avec une autre table

La fonction doit faire un RETURN NEW si la ville n'existe pas, et un RETURN NULL si elle existe. De plus, il doit s'agit d'un trigger BEFORE EACH ROW.

Si cela ne fonctionne pas, merci de mettre le code de la fonction qui ne fonctionne pas, le test effectué ainsi que le message d'erreur exact.

Et concernant le débat trigger/Foreign Key, le trigger sera beaucoup plus lent que la Foreign Key. Il serait donc bien de revoir les "conditions du travail" si vous souhaitez avoir de bonnes performances.

---

Guillaume.