Trigger de mise à jour de stock

Version imprimable

Bonjour les amis, je peine depuis un long moment un trigger de mise à jour.
c'est la première fois que j'utilise les triggers . j'ai besoin de votre coups de main.

Ma préoccupation est de faire la mise à jour de mon stock après livraison de produit

le principe est:
- si l'unité de la quantité livrée est la même à l'unité de base qui se trouve dans produit alors pas de conversion
- sinon conversion

je l'ai fais avec un trigger ci-dessous mais le résultat donne null

Mes tables

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
CREATE TABLE produit(
   ProduitId INT AUTO_INCREMENT  NOT NULL,
   Produitlibelle VARCHAR(50) NOT NULL,
   ProduitReference VARCHAR(20) NOT NULL,
   ProduitCodebar VARCHAR(50) NOT NULL,
   ProduitCaracteristique VARCHAR(50),
   ProduitImage longblob,
   MarqueId INT NOT NULL,
   ModelId INT NOT NULL,
   TvaId INT NOT NULL,
   SousfamilleId INT NOT NULL,
   UniteId INT NOT NULL,
   StockinitialDate DATE,
   StockInitial DECIMAL(15,2),
   ProduitStockalerte DECIMAL(15,2),
   ModifieLe 			DATETIME 	NOT NULL,
   ModifiePar			VARCHAR(50) NOT NULL,
   PRIMARY KEY(ProduitId),
   UNIQUE(ProduitReference),
   FOREIGN KEY(MarqueId) REFERENCES marque(MarqueId) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(ModelId) REFERENCES modele(ModelId) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(TvaId) REFERENCES tva(TvaId) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(SousfamilleId) REFERENCES sousfamille(SousfamilleId) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(UniteId) REFERENCES unite(UniteId) ON DELETE CASCADE ON UPDATE CASCADE
);
 
CREATE TABLE lignelivraisonclient(
   livraisonclientId INT NOT NULL,
   lignelivraisonclientId INT AUTO_INCREMENT  NOT NULL,
   lignelivraisonclientQuantite DECIMAL(15,2) NOT NULL,
   lignelivraisonclientPrixvente DECIMAL(15,2) NOT NULL,
   lignelivraisonclientTva DECIMAL(5,2),
   ProduitId INT NOT NULL,
   UniteId INT NOT NULL,
   PRIMARY KEY(lignelivraisonclientId, livraisonclientId),
   FOREIGN KEY(UniteId) REFERENCES unite(UniteId) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(ProduitId) REFERENCES produit(ProduitId) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(livraisonclientId) REFERENCES livraisonclient(livraisonclientId) ON DELETE CASCADE ON UPDATE CASCADE
);
 
create table CONVERSION ( /* 1 bobine contient 125 mètres */
  UniteId_In INT NOT NULL,/* bobine */
  UniteId_Out INT NOT NULL,/* mètres */
  Con_Quante_In DECIMAL(5, 2) NOT NULL,/*1*/
  Con_Quante_Out DECIMAL(5, 2) NOT NULL,/*125*/
  ProduitId INT NOT NULL,
  CONSTRAINT CONVERSION_PK PRIMARY KEY(UniteId_In, UniteId_Out,ProduitId),
  CONSTRAINT UNITE_IN_FK FOREIGN KEY(UniteId_In) REFERENCES unite(UniteId),
  CONSTRAINT UNITE_OUT_FK FOREIGN KEY(UniteId_Out) REFERENCES unite(UniteId),
  CONSTRAINT PRODUIT_FK FOREIGN KEY(ProduitId) REFERENCES produit(ProduitId) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;
 
 
CREATE TABLE stock(
   StockId INT NOT NULL AUTO_INCREMENT,
   ProduitId INT NOT NULL,
   StockQuantite DECIMAL(15,2),
   ModifieLe 		DATETIME 	NOT NULL,
   ModifiePar			VARCHAR(50) NOT NULL,
   PRIMARY KEY(StockId, ProduitId),
   FOREIGN KEY(ProduitId) REFERENCES produit(ProduitId) ON DELETE CASCADE ON UPDATE CASCADE
);

mon trigger

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
DELIMITER //
CREATE TRIGGER insert_lignelivraisonclient AFTER INSERT ON lignelivraisonclient
FOR EACH ROW
BEGIN
	/* DECLARE les variables */
	DECLARE stock_Quantite decimal(15,2);
	DECLARE Unite_livree int;
	DECLARE Unite_base int;
	DECLARE coefficient_Unite_produit decimal(15,2);
 
	/* Recuperer les valeurs */
	select lignelivraisonclientQuantite, UniteId into stock_Quantite, Unite_livree from lignelivraisonclient where lignelivraisonclientId=NEW.lignelivraisonclientId;
    select distinct p.UniteId into Unite_base from produit p  where p.ProduitId=NEW.ProduitId;
 
    /* Mettre à jour les valeurs */
    if (Unite_livree <> Unite_base) then
    begin
		select Con_Quante_Out into coefficient_Unite_produit from conversion c where ProduitId=NEW.ProduitId and c.UniteId_In =(select u.UniteId from unite u where u.UniteId=Unite_livree);
		UPDATE stock SET StockQuantite = StockQuantite - (stock_Quantite*coefficient_Unite_produit) WHERE ProduitId = NEW.ProduitId;
    end;
    else
    begin
		UPDATE stock SET StockQuantite = StockQuantite - stock_Quantite WHERE ProduitId = NEW.ProduitId;
    end;
    end if;
END
//
DELIMITER ;

je vous remercie d'avance

04/12/2020, 01h08
Paraffine
Il faut dire que MySQL n'est pas le roi des triggers.
La gestion des erreurs est indigente.
Tu trouveras dans ce tutoriel une astuce pour générer des messages d'erreur à ta main.

Pourquoi appelles-tu stock_Quantite la variable qui représente la quantité livrée ; ça n'aide pas à clarifier la lecture.
Et pourquoi relire l'enregistrement ; tu utilises bien NEW.ProduitId, NEW.lignelivraisonclientId, qu'est-ce que les autres colonnes t'ont fait pour ne pas utiliser NEW.lignelivraisonclientQuantite, etc ?
Rien ne t'empêche d'écrire par exemple:
Code:

1 2 DECLARE Qte_Livree decimal(15,2); set Qte_Livree = NEW.lignelivraisonclientQuantite;
Sinon; est-ce que la conversion se fait toujours dans le même sens ?
Pour que ton trigger fonctionne, il faut trouver dans la table de conversion le couple (unité de base pour le produit, unité commandée) dans les colonnes (UniteId_Out, UniteId_In) => est-ce bien toujours le cas ?
Pourquoi aurait-on besoin d'une colonne Con_Quante_In dans la table Conversion si c'est pour considérer qu'elle vaut toujours 1 ; es-tu sûr que tu ne devrais pas plutôt utiliser:

Code:

select Con_Quante_Out/ Con_Quante_In into coefficient_Unite_produit

Attention, la colonne StockQuantite de la table stock est nullable :?
Sache que pour un enregistrement stock avec une valeur NULL dans la colonne quantité, toutes les opérations donneront la valeur NULL.

De plus, ton modèle ne semble pas garantir qu'il n'existe qu'un seul stock par produit, tu pourrais donc potentiellement retirer la quantité livrée de plusieurs stocks :?

Enfin, tu ne mets pas à jour les colonnes "ModifieLe" et "ModifiePar" dans ta procédure.

Bon, le premier trigger est toujours le plus difficile ;)

Salut à tous.

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `produit`
--------------
 
--------------
CREATE TABLE `produit`
(  `produit_id`  integer unsigned  NOT NULL Primary key,
   `unite_id`    integer unsigned  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `produit` (`produit_id`,`unite_id`) VALUES (12,125),(37,237)
--------------
 
--------------
select * from  `produit`
--------------
 
+------------+----------+
| produit_id | unite_id |
+------------+----------+
|         12 |      125 |
|         37 |      237 |
+------------+----------+
--------------
DROP TABLE IF EXISTS `stock`
--------------
 
--------------
CREATE TABLE `stock`
(  `produit_id`   integer unsigned  NOT NULL Primary key,
   `stock_quant`  decimal(15,2)     NOT NULL,
  CONSTRAINT `FK_01` FOREIGN KEY (`produit_id`) REFERENCES `produit` (`produit_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `stock` (`produit_id`,`stock_quant`) VALUES (12,1000),(37,1000)
--------------
 
--------------
select * from  `stock`
--------------
 
+------------+-------------+
| produit_id | stock_quant |
+------------+-------------+
|         12 |     1000.00 |
|         37 |     1000.00 |
+------------+-------------+
--------------
DROP TABLE IF EXISTS `conversion`
--------------
 
--------------
CREATE TABLE `conversion`
(  `produit_id`  integer unsigned  NOT NULL,
   `unite_id`    integer unsigned  NOT NULL,
   `conv_quant`  decimal(5,2)      NOT NULL,
   primary key(`produit_id`,`unite_id`),
  CONSTRAINT `FK_02` FOREIGN KEY (`produit_id`) REFERENCES `produit` (`produit_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `conversion` (`produit_id`,`unite_id`,`conv_quant`) VALUES (12,125,1.20),(37,237,1.40),(37,432,1.60)
--------------
 
--------------
select * from  `conversion`
--------------
 
+------------+----------+------------+
| produit_id | unite_id | conv_quant |
+------------+----------+------------+
|         12 |      125 |       1.20 |
|         37 |      237 |       1.40 |
|         37 |      432 |       1.60 |
+------------+----------+------------+
--------------
DROP TABLE IF EXISTS `livraison`
--------------
 
--------------
CREATE TABLE `livraison`
( `id`          integer unsigned  NOT NULL auto_increment Primary key,
  `quantite`    decimal(15,2)     NOT NULL,
  `unite_id`    integer unsigned  NOT NULL,
  `produit_id`  integer unsigned  NOT NULL,
  CONSTRAINT `FK_03` FOREIGN KEY (`produit_id`) REFERENCES `produit` (`produit_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_04` FOREIGN KEY (`produit_id`) REFERENCES `stock`   (`produit_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
DROP TRIGGER IF EXISTS `modif_unite`
--------------
 
--------------
CREATE TRIGGER `modif_unite`
BEFORE INSERT ON `livraison`
FOR EACH ROW
BEGIN
  DECLARE _base     decimal(15,2) DEFAULT NULL;
  DECLARE _coeff    decimal(15,2) DEFAULT NULL;
  DECLARE _retrait  decimal(15,2) DEFAULT NULL;
 
  SET _base    = (select unite_id    from produit    where produit_id = NEW.produit_id);
  SET _coeff   = (select Conv_Quant  from conversion where produit_id = NEW.produit_id and unite_id = NEW.unite_id);
  SET _retrait = NEW.quantite;
 
  IF (_base <> NEW.unite_id) THEN
     SET _retrait = _retrait * _coeff;
  END IF;
 
  UPDATE stock SET stock_quant = stock_quant - _retrait WHERE produit_id = NEW.produit_id;
END
--------------
 
--------------
INSERT INTO `livraison` (`produit_id`, `quantite`,`unite_id`) VALUES
 (12,25,125),(37,33, 432)
--------------
 
--------------
select * from  `livraison`
--------------
 
+----+----------+----------+------------+
| id | quantite | unite_id | produit_id |
+----+----------+----------+------------+
|  1 |    25.00 |      125 |         12 |
|  2 |    33.00 |      432 |         37 |
+----+----------+----------+------------+
--------------
select * from  `stock`
--------------
 
+------------+-------------+
| produit_id | stock_quant |
+------------+-------------+
|         12 |      975.00 |
|         37 |      947.20 |
+------------+-------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

04/12/2020, 12h28
Zeus_Appolos

Merci vous pour vos prompt réponses.
j'ai appliqué la procédure de Artemus24, ça marche super. merci beaucoup.
mai j'ai une question concernant de trigger de Artemus24. Vous avez utilisé un BEFORE INSERT. j'essayé avec AFTER INSERT ça marche aussi.
je veux savoir c'est à quel moment utilisé chacun ?

MERCI BEAUCOUP !
05/12/2020, 02h14
Artemus24

Salut Zeus_Appolos.

Le problème concerne la mise à jour de votre stocke qui doit être fait une seule fois.
Le mieux est d'intervenir lors de l'insertion et uniquement à l'insertion.

Citation:

Envoyé par Zeus_Appolos

je veux savoir c'est à quel moment utilisé chacun ?

La différence entre le "BEFORE INSERT" et le "AFTER INSERT" est dans l'absence ou la présente de la ligne dans la table "livraison".
Alors lequel utiliser ?

Dans mon exemple, j'utilise une clef primaire auto incrémentée.
Avec le "BEFORE INSERT", la valeur de cette clef n'existe pas.

Mais le plus important dans cette différence est qu'avec le "BEFORE INSERT", vous pouvez modifier les valeurs avant leur stockage, pas avec le "AFTER INSERT".
Dans votre cas, come vous ne modifiez rien, il n'y a aucune différence dans leur utilisation.

@+
08/12/2020, 13h57
Zeus_Appolos

Merci beaucoup Artemus24.