[Trigger] mettre à jour la disponibilité d'un article lorsque la qte en stock change

[youss20]

Bonjour à toutes et à tous,

je suis débutant et je travaille sur une application en Java avec BD Postgres pour mon TFE.
Pour résumer, j'ai une table
Article(idArticle,nomArticle,prixArticle,qteStock,qteMini,idDispo)
et une table
Disponibilité(idDispo,nomDispo) qui regroupe 3 différents degrés de disponibilité d'un article.

id| libDispo
--| --------
1 | Rupture
2 | Stock faible
3 | En stock

Mon souci est que je voudrais qu'à chaque modification de la quantité en stock (qteStock) d'un article (issue d'une commande client par exemple,...) il y ait une mise à jour de la disponibilité.
En fait, il faudrait que idDispo de Article change en fontion de qteEnStock.
Donc, si qteEnStock = 0 alors idDispo = 1 (Rupture de stock)
si qteEnStock < qteMini alors idDispo = 2 (Stock faible)
si qteEnStock > qteMini alors idDispo = 3 (En stock)

Dois-je utiliser un trigger?
Merci d'avance pour votre aide qui me sera très précieuse.

[youss20]

Quelqu'un peut-il m'aider SVP?

[bricecol]

Voici les tables que je te propose pour ma réponse :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
CREATE TABLE article(
idarticle numeric,
nomarticle character varying,
prixarticle float,
qtestock numeric);
 
CREATE TABLE dispo(
iddispo numeric,
disponibilite character varying);
 
--EN SACHANT QUE idarticle=iddispo

Code :

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
31
32
33
34
35
36
37
38
39
40
41
 
 
CREATE FUNCTION maj_dispo() returns TRIGGER AS'
declare
idarticle numeric;
disponible character varying;
qte numeric;
 
begin
     idarticle=new.idarticle;
     disponible=(select disponibilite
                     from dispo
                     where iddispo=idarticle);
 
     if disponible=''RUPTURE'' then
          raise exception ''% indisponible.'',new.nomarticle;
     else
          update table article
          set qtestock=qtestock-1
          where iddispo=idarticle;
 
          qte=(select qtestock
                  from article
                  where iddispo=idarticle);
 
          if qte=0 then
               update table dispo
               set disponibilite=''RUPTURE''
               where idarticle=iddispo;
          end if;
 
          raise ''Achat effectué.'';
          return new;
     end if;
 
end;
'LANGUAGE 'plpgsql';
 
CREATE TRIGGER tmaj_dispo before UPDATE
ON article FOR each row
execute procedure maj_dispo();

Voilà. J'ai sûrement fait des fautes à la vitesse où j'ai fait çà mais le gros du truc est là et encore on peut faire bcp mieux. Mais comme çà, tu as la trame.

[youss20]

Salut Bricecol,

Merci pour ta réponse mais ce n'est pas exactement ce que j'ai demandé. Peut-être que mon explication n'était pas assez explicite.
Aussi je n'ai pas compris pourquoi tu as mis idDispo = idArticle.
En fait j'ai déjà mes tables comme je l'ai expliqué plus haut:

Article(idArticle,nomArticle,prixArticle,qteStock,qteMini,idDispo) où idDispo est une clé étrangère.

idArticle | nomArticle | prixArticle | qteStock | qteMini | idDispo
----------|-------------|-------------|------------|---------|---------
1---------|CD1---------|5.99--------|15---------|5--------|3-------
2---------|DVD1-------|8.99---------|0----------|10-------|1-------
3---------|DVD2-------|10.99-------|8-----------|10-------|2-------

Disponibilité(idDispo,nomDispo)

id| nomDispo
--| --------
1 | Rupture
2 | Stock faible
3 | En stock

Pour les disponibilités je me base sur ma règle ci-dessous:
si qteEnStock = 0 alors idDispo = 1 (Rupture de stock)
si qteEnStock < qteMini alors idDispo = 2 (Stock faible)
si qteEnStock > qteMini alors idDispo = 3 (En stock)

Au niveau programmation y a pas de souci puisque je mets à jour moi-même les disponibilités de chaque article au moment de son insertion ou modification. Mais j'aimerais en fait automatiser cela au niveau de la BD et donc que la clé étrangère idDispo se mette à jour toute seule à chaque fois que la quantité en stock (qteStock) d'un article change.

J'espère que j'ai été plus clair cette fois-ci et merci.

[youss20]

Vraiment personne pour m'aider? C'est si difficile que ça?

[bricecol]

C'est pas que c'est difficile. C'est seulement trop tordu comme idée.

A ta place, j'aurai fait une table article comme toi tu l'as faite (mais sans qtemini et iddispo).

Ensuite dans mon code PHP par exemple, je récupère la quantité du produit et je met selon le cas 'en rupture' ou 'en stock' ou 'qte faible'.....

Tu te compliques vraiment la tache pour rien. Ya des gens qui n'automatise rien, d'autre qui essaient de tt automatiser. La solution c'est un peu des deux je crois.

Si tu veux vraiment automatisé, rajoute juste une table dispo comme tu las fait, et dans ta fonction, fait a peu pres comme je t'ai montré (tu pe personnaliser un peu comme tu m'as repondu).

[youss20]

Peut-être bien que c'est tordu mais là n'est pas la question, et en plus mon analyse a déjà été validée et donc je dois la respecter, je ne peux plus changer. Ce que je demande est faisable je pense, non?
J'ai déjà essayé mais en vain. Ca doit être un truc du genre:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
BEGIN
IF NEW.qtestock = 0
THEN
UPDATE ARTICLE SET iddispo = 1; -- Rupture de stock
ELSE
   IF NEW.qtenstock < NEW.qtemini
   THEN
      UPDATE ARTICLE SET iddispo = 2; -- Stock faible
   ELSE
      IF NEW.qtestock > NEW.qtemini
      THEN
         UPDATE ARTICLE SET iddispo = 3; -- En stock
      END IF;
   END IF;
END IF;
END;

Mais je suis sûr qu'il manque des trucs, je débute en plpgsql.
Aidez-moi svp

[youss20]

Ce post a été vu 55 fois et toujours nada. He ben ça fuse les réponses ici c'est sympa en tout cas.
Merci Bricecol de t'être au moins donné la peine de me répondre c'est cool.

[bricecol]

de rien et desolé pr toi.
si t pas pressé, je pourrai voir ca cett semaine qd j'aurai le temps.
envoi moi ton adress mail en pm.

[okparanoid]

Bon je lis le topic et desole je post pas pour y repondre mais l'exemple me fait penser a un autre cas.

Est-ce qu'un specialiste en BD peut il confirmer qu'il est utile dans un tel cas de creer une table pour stocker juste des chaines de caractere dans un champ et les associer a leurs id.

Ici pour stocker la "disponibilite", mais dans d'autres cas une liste de prenom, de nom, etc

Est-il recommandé de proceder de cette maniere ou remplir directement la chaine de caractere dans la table principale est suffisant (afin d'économiser sur les jointures) ?

table principale
id idnom idprenom

table noms
idnom chaine

table prenoms
idprenom chaine

en lieu et place de
table principale
id nom(chaine) prenom(chaine)

Pour ce qui est de ton probleme je comprends franchement pas pourquoi la disponibilite ne depend pas directement d'une fontion php qui la calcule a partir du stock disponible...

Cordialement

[Spoutnik]

Salut, une piste ...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE article(
idarticle int,
nomarticle character varying,
prixarticle float,
qtestock int,
iddispo int,
seuilQtite int)
 
CREATE TABLE dispo(
iddispo int,
disponibilite character varying);

Code :

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
31
32
33
CREATE FUNCTION maj_dispo() returns TRIGGER AS'
 
begin
 
    IF EXISTS (
         SELECT * 
         FROM new
         WHERE new.qtestock = 0)
      THEN
         UPDATE article SET (qtestock=0)
         WHERE idarticle = new.idarticle
      ELSEIF EXISTS (
            SELECT * 
            FROM new
            WHERE new.qtestock > 0
            AND new.qtestock <= seuilQtite)
         THEN
            UPDATE article SET (qtestock=1)
            WHERE idarticle = new.idarticle
      ELSEIF EXISTS (
            SELECT * 
            FROM new
            WHERE new.qtestock >  seuilQtite)
         THEN
            UPDATE article SET (qtestock=2)
            WHERE idarticle = new.idarticle
      END IF;
end;
'LANGUAGE 'plpgsql';
 
CREATE TRIGGER tmaj_dispo before UPDATE
ON article FOR each row
execute procedure maj_dispo();

Pas testé, ni vérifié la validité en compilation. Et déso d'avance si je mélange les syntaxes de différents SGBDR, mais le principe devrait être là.

++

[Spoutnik]

Envoyé par okparanoid

confirmer qu'il est utile dans un tel cas de creer une table pour stocker juste des chaines de caractere dans un champ et les associer a leurs id.


Est-il recommandé de proceder de cette maniere ou remplir directement la chaine de caractere dans la table principale est suffisant (afin d'économiser sur les jointures) ?

Dans la plupart des cas oui! Il est moins couteux en terme de place de stocker un entier (l'id) qu'une chaine de caractère n fois.

Ensuite, un SGBDR (R pour relationnel est fait pour faire des relation, des jointures. Et enfin, si on ne fait pas cette jointure, on n'est pas en 3FN au niveau de la modélisation.
(Qu'on me corrige surtout si je dis des conneries )

Envoyé par okparanoid

Pour ce qui est de ton probleme je comprends franchement pas pourquoi la disponibilite ne depend pas directement d'une fontion php qui la calcule a partir du stock disponible...

Pour la même raison qu'il existe une intégrité référentielle implantée dans la base. Que si tu compte sur PHP pour ne jamais planter, gérér les transactions, les orphelins, etc ... je donne pas cher de ta base!
Il est BEAUCOUP plus sur et plus rapide de l'implémenter dans la base de données via un trigger que d'espérer que PHP le fasse ( avec + de requetes, de transferts réseau, de risque de plantage dans le serveur php, de conflits de concurence, etc ...).

[youss20]

Enfin des réponses !!! Merci à Spoutnik pour tes éclaircissements et ton code que je vais d'ailleurs essayer dès que j'ai un peu de temps. Je te tiendrai au courant du résultat. Merci.

[Spoutnik]

ca pourrait pt mieux marcher :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE FUNCTION maj_dispo() returns TRIGGER AS'
 
begin
         UPDATE article SET (qtestock=0)
         FROM new
         WHERE idarticle = new.idarticle
         AND new.qtestock = 0;
 
            UPDATE article SET (qtestock=1)
            FROM new
            WHERE idarticle = new.idarticle
            AND new.qtestock > 0
            AND new.qtestock <= seuilQtite;
 
            UPDATE article SET (qtestock=2)
            FROM new
            WHERE idarticle = new.idarticle
            AND new.qtestock >  seuilQtite;
 
end;
'LANGUAGE 'plpgsql';

Je pense qu'utiliser un "FOR STATEMENT" est plus rentable ici (déclenchement une seule fois par "lot", et non pas par ligne)

Code :

1
2
3
CREATE TRIGGER tmaj_dispo AFTER UPDATE
ON article FOR each STATEMENT
execute procedure maj_dispo();

A tester

[okparanoid]

Envoyé par Spoutnik

Dans la plupart des cas oui! Il est moins couteux en terme de place de stocker un entier (l'id) qu'une chaine de caractère n fois.

Ensuite, un SGBDR (R pour relationnel est fait pour faire des relation, des jointures. Et enfin, si on ne fait pas cette jointure, on n'est pas en 3FN au niveau de la modélisation.
(Qu'on me corrige surtout si je dis des conneries )

3FN c'est quand un champ depend d'un autre dans une meme table (deux colonnes), ce n'est pas vraiment le cas enonce...
J'aimerais bcp qu'un specialiste SGBD reponde a cette question sur le judicieux de creer une table des prenoms et d'inserer dans une table des personnes l'id du prenom en lieu et place du prenom, idem pour le nom...

Envoyé par Spoutnik

Pour la même raison qu'il existe une intégrité référentielle implantée dans la base. Que si tu compte sur PHP pour ne jamais planter, gérér les transactions, les orphelins, etc ... je donne pas cher de ta base!
Il est BEAUCOUP plus sur et plus rapide de l'implémenter dans la base de données via un trigger que d'espérer que PHP le fasse ( avec + de requetes, de transferts réseau, de risque de plantage dans le serveur php, de conflits de concurence, etc ...).

Il n'est pas question ici ni de gerer des transactions ni de risquer une quelconque creation d'orphelinat ni de provoquer des conflits (bien m'en garde !), mais juste de constater qu'avoir l'etat numeraire d'un stock est deja en soi une indication sur sa disponibilite, tu parlais de redondance des données trois lignes plus haut :p...

Au pire si on a besoin d'avoir ce renseignement directement depuis la base on cree une vue qui fait le calcul en direct sans stocker physiquement le resultat inutilement vu que l'information de base est deja presente (et plus complete) et que pour le coup on est plus en forme normale vu que la colonne dispo depend directement des deux autres (qte et qte_mini)

[GrandFather]

Bonjour,

Envoyé par okparanoid

J'aimerais bcp qu'un specialiste SGBD reponde a cette question sur le judicieux de creer une table des prenoms et d'inserer dans une table des personnes l'id du prenom en lieu et place du prenom, idem pour le nom...

Je ne suis pas un spécialiste SGBD, mais le simple bon sens m'inciterait plutôt à ne pas placer les noms et les prénoms dans des tables séparées. Gérer des tables séparées complique le schéma et les requêtes, et grève les performances. Les traitements sont plus complexes : à l'insertion d'un nouvel enregistrement dans la table principale, il faut rechercher le nom et le prénom dans les tables correspondantes, les créer s'ils n'existent pas puis remonter leur id. Bref, beaucoup de complications pour un gain de place minime, sachant que la redondance n'est pas énorme quand il s'agit de noms propres (un peu plus pour les prénoms, peut-être).

Envoyé par okparanoid

Au pire si on a besoin d'avoir ce renseignement directement depuis la base on cree une vue qui fait le calcul en direct sans stocker physiquement le resultat inutilement vu que l'information de base est deja presente (et plus complete) et que pour le coup on est plus en forme normale vu que la colonne dispo depend directement des deux autres (qte et qte_mini)

C'est exactement ce que je ferais aussi, je passerais par une vue...

[Spoutnik]

Envoyé par okparanoid

3FN c'est quand un champ depend d'un autre dans une meme table (deux colonnes), ce n'est pas vraiment le cas enonce...

j'ai effectivement répondu un peu vite. Pour ma défense, je dirais juste que j'ai répondu d'un point de vue général, pas suffisemment spécifique, et du coup à coté de la question.

Envoyé par okparanoid

Au pire si on a besoin d'avoir ce renseignement directement depuis la base on cree une vue qui fait le calcul en direct sans stocker physiquement le resultat inutilement vu que l'information de base est deja presente (et plus complete) et que pour le coup on est plus en forme normale vu que la colonne dispo depend directement des deux autres (qte et qte_mini)

vi, je suis assez d'accord sur le fait d'utiliser une vue plutot que de stocker la dispo. Je n'avais pas vraiment réfléchi à ceci, et probablement lu un peu vite (encore ), et mal compris ta remarque.