Trigger avec procédure stockée

**annabil23** · 05/07/2016, 03h40

bonjour tous le monde

j,appelle a votre aide

une partie de ma base de donnee et la suivante :

Nom : MCD.png
Affichages : 934
Taille : 37,6 Ko

j,ai cinq tables

table1 clients

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
-- Table structure for clients
-- ----------------------------
DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
  `ID_CL` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `NOM_CL` varchar(25) NOT NULL,
  `CREDIT_TOT` decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (`ID_CL`),
  UNIQUE KEY `idx_CL` (`NOM_CL`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
 
-- ----------------------------
-- Records of clients
-- ----------------------------
INSERT INTO `clients` VALUES ('1', 'NABIL', '677.00');
INSERT INTO `clients` VALUES ('2', 'HICHEM', '210.00');
INSERT INTO `clients` VALUES ('3', 'SALIH', '0.00');
INSERT INTO `clients` VALUES ('4', 'DOGHMANE', '0.00');
INSERT INTO `clients` VALUES ('5', 'SALIM', '0.00');

table 2

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
-- Table structure for comptes
-- ----------------------------
DROP TABLE IF EXISTS `comptes`;
CREATE TABLE `comptes` (
  `ID_COMPT` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `COMPTE` varchar(45) NOT NULL,
  `ID_CL` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID_COMPT`),
  UNIQUE KEY `IDX_COMP` (`COMPTE`),
  KEY `fk_COMPTES_CLIENTS_1` (`ID_CL`),
  CONSTRAINT `fk_COMPTES_CLIENTS_1` FOREIGN KEY (`ID_CL`) REFERENCES `clients` (`ID_CL`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
 
-- ----------------------------
-- Records of comptes
-- ----------------------------
INSERT INTO `comptes` VALUES ('1', '0662382208', '1');
INSERT INTO `comptes` VALUES ('2', '0551283290', '1');
INSERT INTO `comptes` VALUES ('3', '0668225118', '1');
INSERT INTO `comptes` VALUES ('4', '0661131415', '2');
INSERT INTO `comptes` VALUES ('5', '0553419518', '3');

table 3 credits

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
-- ----------------------------
-- Table structure for credits
-- ----------------------------
DROP TABLE IF EXISTS `credits`;
CREATE TABLE `credits` (
  `ID_CRD` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CRD` decimal(10,2) NOT NULL DEFAULT '0.00',
  `date_CRD` date NOT NULL,
  `id_TRANSF` int(10) unsigned NOT NULL,
  `ID_CL` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID_CRD`),
  KEY `fk_table_2_table_1_1` (`ID_CL`),
  CONSTRAINT `fk_table_2_table_1_1` FOREIGN KEY (`ID_CL`) REFERENCES `clients` (`ID_CL`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
 
-- ----------------------------
-- Records of credits
-- ----------------------------
INSERT INTO `credits` VALUES ('1', '110.00', '2016-07-04', '8', '1');
INSERT INTO `credits` VALUES ('2', '0.00', '2016-06-01', '9', '1');
INSERT INTO `credits` VALUES ('3', '0.00', '2016-06-16', '10', '1');
INSERT INTO `credits` VALUES ('4', '120.00', '2016-07-05', '17', '1');
INSERT INTO `credits` VALUES ('5', '123.00', '2016-07-04', '18', '1');
INSERT INTO `credits` VALUES ('6', '164.00', '2016-07-05', '19', '1');
INSERT INTO `credits` VALUES ('7', '160.00', '2016-07-05', '20', '1');
INSERT INTO `credits` VALUES ('8', '110.00', '2016-07-05', '21', '1');
INSERT INTO `credits` VALUES ('9', '210.00', '2016-07-04', '25', '2');

table 4

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
-- ----------------------------
-- Table structure for verssements
-- ----------------------------
DROP TABLE IF EXISTS `verssements`;
CREATE TABLE `verssements` (
  `ID_VERSSE` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `verssement` decimal(10,2) NOT NULL DEFAULT '0.00',
  `date_vers` date NOT NULL,
  `ID_CL` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID_VERSSE`),
  KEY `fk_table_3_table_1_1` (`ID_CL`),
  CONSTRAINT `fk_table_3_table_1_1` FOREIGN KEY (`ID_CL`) REFERENCES `clients` (`ID_CL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

table 5 articles

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
DROP TABLE IF EXISTS `articles`;
CREATE TABLE `articles` (
  `ID_TRANSF` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DESIGNATION_TRANSF` varchar(45) NOT NULL,
  `DATE_TRANSF` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `PRIX_VENT` decimal(10,2) NOT NULL DEFAULT '0.00',
  `MARGE` decimal(10,2) DEFAULT '0.00',
  `CRD` tinyint(4) NOT NULL DEFAULT '0',
  `ID_COMPTE` int(10) unsigned NOT NULL,
  `TRANSF` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID_TRANSF`),
  KEY `fk_ARTICLES_COMPTES_1` (`ID_COMPTE`),
  CONSTRAINT `fk_ARTICLES_COMPTES_1` FOREIGN KEY (`ID_COMPTE`) REFERENCES `comptes` (`ID_COMPT`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1;
 
-- ----------------------------
-- Records of articles
-- ----------------------------
INSERT INTO `articles` VALUES ('4', 'flexy', '2016-07-04 06:48:53', '100.00', '10.00', '0', '1', '0');
INSERT INTO `articles` VALUES ('6', 'flexy', '2016-07-04 07:06:29', '10000000.99', '10000.00', '0', '2', '0');
INSERT INTO `articles` VALUES ('7', 'storm', '2016-07-05 02:25:18', '10000000.00', '0.00', '0', '1', '0');
INSERT INTO `articles` VALUES ('8', 'flexy', '2016-07-04 07:09:47', '100.00', '10.00', '0', '1', '1');
INSERT INTO `articles` VALUES ('9', 'arsselli', '2016-06-01 07:10:25', '100.00', '10.00', '1', '1', '1');
INSERT INTO `articles` VALUES ('10', 'felxy ', '2016-06-16 07:26:43', '100.00', '10.00', '1', '1', '1');
INSERT INTO `articles` VALUES ('12', 'arrselli', '2016-07-05 02:25:18', '120.00', '0.00', '0', '1', '1');
INSERT INTO `articles` VALUES ('13', 'flexy', '2016-07-05 02:25:18', '90.00', '0.00', '1', '1', '2');
INSERT INTO `articles` VALUES ('17', 'flexy', '2016-07-05 07:45:56', '120.00', '0.00', '1', '1', '1');
INSERT INTO `articles` VALUES ('18', 'trans', '2016-07-04 07:48:01', '123.00', '0.00', '1', '1', '1');
INSERT INTO `articles` VALUES ('19', 'trans', '2016-07-05 07:48:33', '114.00', '50.00', '1', '1', '1');
INSERT INTO `articles` VALUES ('20', 'TRANS', '2016-07-05 02:05:55', '150.00', '10.00', '1', '2', '1');
INSERT INTO `articles` VALUES ('21', 'trans', '2016-07-05 02:08:09', '100.00', '10.00', '1', '3', '1');
INSERT INTO `articles` VALUES ('25', 'trans', '2016-07-04 02:09:12', '200.00', '10.00', '1', '4', '1');

chaque client a 0 ou un compte
chaque compte peut avoie de 0 a plusieurs credit aur les articles

alors jai utiliser un trigger after insert et before update sur article
que si le clients veut faire un credit sur cette article
alors le triiger me fait une insertion sur la table credit
el la table credit atraver un autre trigger mise ajour le credit total de clients

jusque la toutes et bon

les triggers

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
DROP TRIGGER IF EXISTS `TRG_CRD`;
DELIMITER ;;
CREATE TRIGGER `TRG_CRD` AFTER INSERT ON `articles` FOR EACH ROW BEGIN 
declare  cl  int;
declare  PRX decimal (10,2);
set  cl  = (SELECT ID_CL from comptes where comptes.ID_COMPT = New.ID_COMPTE);
set prx = new.PRIX_VENT+new.MARGE ;
 if( new.transf = 1 and   new.crd = 1 )
then
insert into credits (credits.CRD,credits.date_crd,credits.ID_TRANSF,credits.ID_CL)
values (prx,new.date_transf,new.ID_TRANSF,cl);
end if;
END
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `TRG_UPCRD`;
DELIMITER ;;
CREATE TRIGGER `TRG_UPCRD` BEFORE UPDATE ON `articles` FOR EACH ROW BEGIN 
IF NOT EXISTS (SELECT ID_TRANSF FROM credits WHERE CREDITS.ID_TRANSF = NEW.ID_TRANSF) THEN
BEGIN 
declare  cl  int;
declare  PRX decimal (10,2);
set  cl  = (SELECT ID_CL from comptes where comptes.ID_COMPT = New.ID_COMPTE);
set prx = new.PRIX_VENT+new.MARGE ;
 if( new.transf = 1 and   new.crd = 1 )
then
insert into credits (credits.CRD,credits.date_crd,credits.ID_TRANSF,credits.ID_CL)
values (prx,new.date_transf,new.ID_TRANSF,cl);
end if;
END;
end if;
END
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `trg_INS_CRD`;
DELIMITER ;;
CREATE TRIGGER `trg_INS_CRD` AFTER INSERT ON `credits` FOR EACH ROW begin
declare crd decimal ;
declare crd1 decimal;
set crd =( select CREDIT_TOT from CLIENTS where CLIENTS.ID_CL = new.ID_CL);
set CRD1 = (crd + new.CRD);
update CLIENTS  set CLIENTS.CREDIT_TOT = CRD1 where CLIENTS.ID_CL = new.ID_CL;
end
;;
DELIMITER ;

mon problème c est lorsque il un versement d'un client

je veut faire la mise ajour suivante

si le versement = crédit totale donc
crédit totale = 0 est dans la table crédit détaille je remis tous les enregistrement de crédit détaille seront remise a la valeur 0;

mais si le versement < crédit totale
je veut 1 : crédit totale = crédit totale - versement
2 : il commence par le premier enregistrement du crédit détaille
si le versement >= crédit détaille alors
versement - crédit détaille et il remis cet enregistrement a 0
et versement sera égale a versement - crédit détaille;

si le versement < crédit détaille alors
crédit détaille - versement et il remis cet enregistrement a reste
jusque ’a versement sera = 0

j'ai fait une procédure avec un curseur

mais ????

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
 
CREATE PROCEDURE `PROC_VERS_CRD`(IN `vers` decimal(10,2) , IN cli int)
BEGIN
DECLARE @cmpt int;
DECLARE @CREDITT DECIMAL(10,2);
DECLARE fin TINYINT DEFAULT 0; 
DECLARE verssem CURSOR FOR SELECT verssement,ID_CL FROM verssements WHERE ID_CL = @CLI;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = TRUE;
OPEN verssem;
 select ID_COMPTE into @cmpt ,((PRIX_VENT) + (MARGE))into @CREDTT from articles;
    loop_VERSSEMENT:LOOP
        FETCH verssem into @VERS,@CLI;
        IF fin THEN
            LEAVE loop_VERSSEMENT;
        END IF;
        IF @vers >= @CREDTT THEN
            UPDATE ARTICLES SET (PRIX_VENT) = 0,(MARGE)=0 WHERE ID_COMTE = @cmpt;
           @vers = @vers - @CREDTT
        ELSE
            UPDATE ARTICLES SET (PRIX_VENT) =  @CREDTT-@vers ,(MARGE)=0 WHERE ID_COMTE = @cmpt;
             @vers = 0;
        END IF;
    END LOOP;
CLOSE verssem;
END
 
END;;

je m'excuse pour mon francais

**annabil23** · 06/07/2016, 07h20

BONJOUR

comment corriger cet erreur SVP

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE VERCUR CURSOR FOR SELECT CRD from CREDITS Where ID_CL = NEW.ID_CL ;
' at line 11

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
DELIMITER $$
 
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `test_triggers`.`TRG_VERS_AFT` AFTER INSERT
    ON `test_triggers`.`VERSSEMENTS`
    FOR EACH ROW BEGIN
    DECLARE REST DECIMAL (10,2);
    DECLARE CRD1 DECIMAL (10,2);
    DECLARE fin TINYINT DEFAULT 0;
    SET REST = NEW.VERSSEMENT;
 
     DECLARE VERCUR CURSOR FOR SELECT CRD FROM CREDITS WHERE ID_CL = NEW.ID_CL ;
 
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = TRUE; 
     OPEN VERCUR;
     read_loop: LOOP
      FETCH VERCUR INTO CRD1;
      IF done THEN
      LEAVE read_loop;END IF
      IF REST >= CRD1 THEN       
      UPDATE CREDITS SET CRD = 0,DATE_CRD = NEW.DATE_VERS ;
      REST = REST - CRD1;
      IF REST < CRD1 THEN  
      REST = CRD1 - REST;     
      UPDATE CREDITS SET CRD = REST,DATE_CRD = NEW.DATE_VERS ;
      IF REST = 0 THEN
      CLOSE VERCUR;
      END IF;
      END LOOP;
      CLOSE vercur;
    END$$
 
DELIMITER ;

**annabil23** · 06/07/2016, 13h17

bonjour

j'ai bien peur que les curssors en MySQL ne prend pas en charge les mise a jours de plusieurs lignes des tables

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

http://dev.mysql.com/doc/refman/5.7/en/cursor-restrictions.html

y'a t'il d'autre méthode ou un autre moyen pour remédier ce problème ????

Trigger avec procédure stockée

SQL Procédural MySQL

Discussions similaires

Partager

Partager