IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Procédural MySQL Discussion :

Trigger de mise à jour de stock


Sujet :

SQL Procédural MySQL

  1. #1
    Membre à l'essai
    Homme Profil pro
    Étudiant
    Inscrit en
    octobre 2018
    Messages
    59
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Étudiant
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : octobre 2018
    Messages : 59
    Points : 16
    Points
    16
    Par défaut Trigger de mise à jour de stock
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  2. #2
    Membre éclairé
    Femme Profil pro
    Service informatique presque à moi seule (TPE), ex-architecte SI
    Inscrit en
    août 2017
    Messages
    279
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 53
    Localisation : France, Gard (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Service informatique presque à moi seule (TPE), ex-architecte SI
    Secteur : Finance

    Informations forums :
    Inscription : août 2017
    Messages : 279
    Points : 650
    Points
    650
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    Les problèmes sont des opportunités en vêtements de travail. Henry H. Kaiser
    Il n'est pas de problème dont une absence de solution ne finisse par venir à bout. Henri Queuille

  3. #3
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    4 731
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 4 731
    Points : 13 567
    Points
    13 567
    Par défaut
    Salut à tous.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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...
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  4. #4
    Membre à l'essai
    Homme Profil pro
    Étudiant
    Inscrit en
    octobre 2018
    Messages
    59
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Étudiant
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : octobre 2018
    Messages : 59
    Points : 16
    Points
    16
    Par défaut
    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 !

  5. #5
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    4 731
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 4 731
    Points : 13 567
    Points
    13 567
    Par défaut
    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.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  6. #6
    Membre à l'essai
    Homme Profil pro
    Étudiant
    Inscrit en
    octobre 2018
    Messages
    59
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Côte d'Ivoire

    Informations professionnelles :
    Activité : Étudiant
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : octobre 2018
    Messages : 59
    Points : 16
    Points
    16
    Par défaut
    Merci beaucoup Artemus24.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Trigger mise à jour état stock
    Par nouri_t dans le forum Requêtes
    Réponses: 6
    Dernier message: 19/05/2011, 15h57
  2. Trigger de mise à jour automatique du champ inséré
    Par popovitch130 dans le forum SQL Procédural
    Réponses: 2
    Dernier message: 06/08/2008, 10h05
  3. trigger en mise à jour
    Par k-eisti dans le forum PL/SQL
    Réponses: 4
    Dernier message: 17/01/2008, 14h54
  4. Opinion sur la mise à jour du stock en reseau
    Par kivoch dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 19/10/2007, 13h58
  5. triggers insertion, mise à jour, suppression
    Par bibi_forever dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 15/05/2007, 12h22

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo