Utilisation de la procédure stockée

Version imprimable

Bonjour,

Je développe une application SMS et la grande partie se fait du côté base des données MySQL que j'utilise, mais comme c'est une première expérience avec les procédures stockées, les déclencheurs et les transactions je rencontre quelque difficultés, voici la structure d'une des tables que où je rencontre de problème :
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE IF NOT EXISTS `cfco_transaction` ( `id_transac` int(11) NOT NULL AUTO_INCREMENT, `cost_paid` float NOT NULL, `phone_number` decimal(9,0) NOT NULL, `money_transac` decimal(8,0) NOT NULL, `total_transac` double NOT NULL, `transac_status` tinyint(1) NOT NULL DEFAULT '0', `when_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id_transac`), UNIQUE KEY `phone_number` (`phone_number`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Je voudrais créer une procédure qui vérifie aussitôt qu'il y a un enregistrement si la ligne cost_paid, money_transac sont différents de NULL et aussi que la sommes à l'intérieur de cost_paid est supérieur ou égale à 15000 si c'est le cas il met à jour la ligne transac_status de FALSE à TRUE sinon il reste FALSE puis il renregistre une erreur dans ma table de suivi des erreurs et tentative dont voici la structure
Code:

1 2 3 4 5 6 CREATE TABLE IF NOT EXISTS `cfco_ticket_errors` ( `id_error` int(11) NOT NULL AUTO_INCREMENT, `error` varchar(100) NOT NULL, PRIMARY KEY (`id_error`), KEY `error` (`error`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
Merci de votre assistance

15/07/2015, 13h02
fsmrel

Bonjour glodybiss4,

Citation:

Envoyé par glodybiss4

Je voudrais créer une procédure qui vérifie aussitôt qu'il y a un enregistrement si la ligne cost_paid, money_transac sont différents de NULL

Que voulez-vous dire par « vérifier aussitôt qu'il y a un enregistrement » ? Indépendamment du fait qu’en SQL on parle de lignes et pas d’enregistrements, voulez-vous dire que lors d’un insert, un trigger s’assure que les colonnes cost_paid et money_transac ne sont pas marquées NULL ? De toute façon, ces colonnes ont été déclares NOT NULL dans la table cfco_transaction, donc c’est MySQL qui se charge automatiquement d’assurer le contrôle.

Citation:

Envoyé par glodybiss4

et aussi que la sommes à l'intérieur de cost_paid est supérieur ou égale à 15000

Confusion du singulier et du pluriel : il s’agit de la somme ou des sommes ? Pour une ligne ? plusieurs lignes ?

Tout cela mériterait beaucoup plus de rigueur dans la rédaction : reformulez.

Merci pour la correction, il s'agit de la somme et non des sommes, désolé pour cela et désolé pour les erreurs dans la rédaction il s'agit bien des enregistrements.
Voici un peu ce que j'ai fait, j'ai utilisé donc une procédure stockée, pour chaque insertion dans une ligne, la procédure se charge de vérifier si la ligne contient bien de valeur sinon il ajoute une ligne d'erreur dans la table d'erreur, ce que j'ai fait ne fonctionne pas alors ne me tenez pas trop rigueur c'est ma première avec expérience avec tout ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 DELIMITER | DROP PROCEDURE OF EXISTS CHECK_STATUS | CREATE PROCEDURE CHECK_STATUS(p_transac_status boolean) BEGIN SELECT id_transac, cost_paid, phone_number FROM cfco_transaction; @coutp as cost_paid @phonet as phone_number IF coutp != 0 AND IF phonet != NULL THEN UPDATE cfco_transaction SET money_transac =1 WHERE id_transac = 1; END |

Bonsoir glodybiss4,

Citation:

Envoyé par glodybiss4

si la ligne cost_paid, money_transac sont différents de NULL

Je pense qu’il y a un quiproquo : comme je l’ai signalé précédemment, en SQL, NULL est un marqueur représentant l’absence d’information, ce qui ne peut se produire pour votre table cfco_transaction puisque pour chaque colonne vous avez codé la clause « NOT NULL ». J’ai l’impression que dans votre phrase « NULL » est à remplacer par « ZÉRO ».
En tout cas, dans ce qui suit je l’interprète ainsi.

Pour les contrôles à sous-traiter à MySQL, il faut :

— Un trigger BEFORE INSERT pour faire passer transac_status à 1 s’il n’ y a pas d’erreur, c'est-à-dire, en supposant que j’ai bien compris ce que vous dites, si cost_paid >= 15000 et money_transac > 0.

— Un trigger AFTER INSERT pour effectuer les inserts dans la table cfco_ticket_errors. En effet, quand le trigger BEFORE INSERT agit, la colonne id_transac n’est pas encore correctement valorisée : on doit attendre que les inserts dans la table cfco_transaction soient effectifs.

Si vous prévoyez des UPDATE de la table cfco_transaction, il faudra prévoir deux triggers supplémentaires, calqués sur ceux qui sont utilisés pour les inserts.

Je vous renvoie à la documentation de MySQL pour que vous y étudiiez l’instruction CREATE TRIGGER.

Une procédure ne sert à rien dans votre cas, mais ce sont bien des triggers dont vous avez besoin : avant d’effectuer un insert, MySQL passe d’abord la main au trigger BEFORE INSERT, en lui présentant la ligne à insérer : chaque colonne de cette ligne est accessible en préfixant son nom par « new.».

Quand l’insert a été effectué, c’est au trigger AFTER INSERT que MySQL passe la main.

Je joins un script pour tester tout ça :
USE temp ;

DROP TABLE IF EXISTS cfco_ticket_errors ;
DROP TABLE IF EXISTS cfco_transaction ;

CREATE TABLE cfco_ticket_errors 
(
  id_error INT NOT NULL AUTO_INCREMENT,
  the_error VARCHAR(100) NOT NULL,
  PRIMARY KEY (id_error)
) ;

CREATE TABLE cfco_transaction 
(
id_transac INT NOT NULL  AUTO_INCREMENT
, cost_paid INT NOT NULL
, transac_status INT NOT NULL DEFAULT '0'
 ,  phone_number INT NOT NULL
 ,  money_transac INT NOT NULL
 ,  total_transac INT NOT NULL
 ,  when_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
,  PRIMARY KEY (id_transac)
 ,  UNIQUE KEY phone_number (phone_number)
) ;

 COMMIT ;

DELIMITER GO

CREATE TRIGGER cfco_transaction_before_insert_tr BEFORE INSERT ON cfco_transaction 
FOR EACH ROW
    BEGIN 
        SET new.transac_status = 0 ;
        IF new.cost_paid >= 15000 AND new.money_transac > 0 THEN
            SET new.transac_status = 1 ; 
        END IF ;
     END 
GO 

CREATE TRIGGER cfco_transaction_after_insert_tr AFTER INSERT ON cfco_transaction 
FOR EACH ROW
    BEGIN 
        SET @erreur = CONCAT('id_transac = ', new.id_transac, ' : ') ;
        IF new.transac_status = 0 THEN
            IF new.cost_paid < 15000 THEN
                SET @erreur = CONCAT(@erreur, 'cost_paid doit être >= 15000. ') ;
            END IF ;
            IF new.money_transac = 0 THEN
                SET @erreur = CONCAT(@erreur, 'money_transac doit être > 0.') ;
            END IF ;            
            INSERT INTO cfco_ticket_errors (the_error)
                SELECT @erreur  ;
        END IF ;
     END 
GO 

DELIMITER ;


INSERT INTO cfco_transaction (cost_paid, phone_number, money_transac, total_transac) 
    VALUES (15000, 123456780, 1500, 250) ;

INSERT INTO cfco_transaction (cost_paid, phone_number, money_transac, total_transac)     
    VALUES (100, 123456781, 1000, 200) ;

INSERT INTO cfco_transaction (cost_paid, phone_number, money_transac, total_transac) 
    VALUES (1000, 123456782, 1000, 200) ;

INSERT INTO cfco_transaction (cost_paid, phone_number, money_transac, total_transac) 
    VALUES (0, 123456783, 1000, 200) ;

INSERT INTO cfco_transaction (cost_paid, phone_number, money_transac, total_transac) 
    VALUES (15000, 123456784, 0, 200) ;

INSERT INTO cfco_transaction (cost_paid, phone_number, money_transac, total_transac) 
    VALUES (0, 123456785, 0, 200) ;


SELECT * FROM cfco_transaction ;

SELECT * FROM cfco_ticket_errors ;
:!: Un rappel : en SQL il n'y a ni enregistrements ni champs, mais des lignes et des colonnes.