Trigger ne marche pas comme souhaité

**baba13** · 26/12/2020, 16h31

Bonjour j ai un trigger SQL que j'ai crée mais qui ne marche pas comme souhaité
En effet il y a plusieurs conditions IF mais seule la première condition marche.

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
DELIMITER $$
CREATE TRIGGER `before_insert_reg3` AFTER INSERT ON `reglement` FOR EACH ROW BEGIN
    IF NEW.montantA != 0    
 
      THEN
       INSERT INTO `A`(`montant`, `operation`, `id_origine`, `comm`)                                                            VALUES(NEW.dollar,"reglement",New.id,NEW.id_c);
 
 
 
    END IF; 
 
    IF NEW.montantB !=0 
    THEN    
    INSERT INTO `B`(`montant`, `operation`, `id_origine`, `comm`)                                                            VALUES(NEW.euro,"reglement",New.id,NEW.id_c);
  END IF; 
 
     IF NEW.montantC !=0 THEN    
       INSERT INTO `C`(`montant`, `operation`, `id_origine`, `comm`)                                                            VALUES(NEW.cfa,"reglement",New.id,NEW.id_c);
    END IF;    
 
 
END
$$
DELIMITER ;

Merci de m'aider SVP

**Artemus24** · 27/12/2020, 03h58

Salut baba13.

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
--------------
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 `A`
--------------
 
--------------
CREATE TABLE `A`
(  `id`          integer unsigned  NOT NULL auto_increment Primary key,
   `montant`     decimal(15,2)     NOT NULL,
   `operation`   varchar(255)      NOT NULL,
   `id_origine`  integer unsigned  NOT NULL,
   `comm`        integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `B`
--------------
 
--------------
CREATE TABLE `B`
(  `id`          integer unsigned  NOT NULL auto_increment Primary key,
   `montant`     decimal(15,2)     NOT NULL,
   `operation`   varchar(255)      NOT NULL,
   `id_origine`  integer unsigned  NOT NULL,
   `comm`        integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `C`
--------------
 
--------------
CREATE TABLE `C`
(  `id`          integer unsigned  NOT NULL auto_increment Primary key,
   `montant`     decimal(15,2)     NOT NULL,
   `operation`   varchar(255)      NOT NULL,
   `id_origine`  integer unsigned  NOT NULL,
   `comm`        integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `reglement`
--------------
 
--------------
CREATE TABLE `reglement`
(  `id`        integer unsigned  NOT NULL auto_increment Primary key,
   `dollar`    decimal(15,2)     NOT NULL,
   `euro`      decimal(15,2)     NOT NULL,
   `cfa`       decimal(15,2)     NOT NULL,
   `id_c`      integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `ajout`
--------------
 
--------------
CREATE TRIGGER `ajout`
AFTER INSERT ON `reglement`
FOR EACH ROW
BEGIN
        IF NEW.dollar != 0 THEN
                INSERT INTO `A` (`montant`,`operation`,`id_origine`,`comm`) VALUES(NEW.dollar,'reglement',New.id,NEW.id_c);
        END IF;
 
        IF NEW.euro !=0 THEN
                INSERT INTO `B` (`montant`,`operation`,`id_origine`,`comm`) VALUES(NEW.euro,'reglement',New.id,NEW.id_c);
        END IF;
 
        IF NEW.cfa !=0 THEN
                INSERT INTO `C` (`montant`,`operation`,`id_origine`,`comm`) VALUES(NEW.cfa,"reglement",New.id,NEW.id_c);
        END IF;
END
--------------
 
--------------
INSERT IGNORE INTO `reglement` (`dollar`,`euro`,`cfa`,`id_c`) VALUES
  (12.5,  0.0,  0.0, 100),
  ( 0.0, 25.0,  0.0, 200),
  ( 0.0,  0.0, 75.0, 300),
  (10.0, 20.0, 30.0, 400)
--------------
 
--------------
select * from `reglement`
--------------
 
+----+--------+-------+-------+------+
| id | dollar | euro  | cfa   | id_c |
+----+--------+-------+-------+------+
|  1 |  12.50 |  0.00 |  0.00 |  100 |
|  2 |   0.00 | 25.00 |  0.00 |  200 |
|  3 |   0.00 |  0.00 | 75.00 |  300 |
|  4 |  10.00 | 20.00 | 30.00 |  400 |
+----+--------+-------+-------+------+
--------------
select * from `A`
--------------
 
+----+---------+-----------+------------+------+
| id | montant | operation | id_origine | comm |
+----+---------+-----------+------------+------+
|  1 |   12.50 | reglement |          1 |  100 |
|  2 |   10.00 | reglement |          4 |  400 |
+----+---------+-----------+------------+------+
--------------
select * from `B`
--------------
 
+----+---------+-----------+------------+------+
| id | montant | operation | id_origine | comm |
+----+---------+-----------+------------+------+
|  1 |   25.00 | reglement |          2 |  200 |
|  2 |   20.00 | reglement |          4 |  400 |
+----+---------+-----------+------------+------+
--------------
select * from `C`
--------------
 
+----+---------+-----------+------------+------+
| id | montant | operation | id_origine | comm |
+----+---------+-----------+------------+------+
|  1 |   75.00 | reglement |          3 |  300 |
|  2 |   30.00 | reglement |          4 |  400 |
+----+---------+-----------+------------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

**baba13** · 29/12/2020, 11h16

Merci beaucoup , j'ai ajouté des points virgules et les délimiter pour faire marcher ton code

Code sql :

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
 
 
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
 
 
--
-- Base de données :  `base`
--
 
-- --------------------------------------------------------
 
--
-- Structure de la table `a`
--
 
DROP TABLE IF EXISTS `a`;
CREATE TABLE IF NOT EXISTS `a` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `montant` decimal(15,2) NOT NULL,
  `operation` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `id_origine` int(10) UNSIGNED NOT NULL,
  `comm` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `b`
--
 
DROP TABLE IF EXISTS `b`;
CREATE TABLE IF NOT EXISTS `b` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `montant` decimal(15,2) NOT NULL,
  `operation` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `id_origine` int(10) UNSIGNED NOT NULL,
  `comm` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `c`
--
 
DROP TABLE IF EXISTS `c`;
CREATE TABLE IF NOT EXISTS `c` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `montant` decimal(15,2) NOT NULL,
  `operation` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `id_origine` int(10) UNSIGNED NOT NULL,
  `comm` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `reglement`
--
 
DROP TABLE IF EXISTS `reglement`;
CREATE TABLE IF NOT EXISTS `reglement` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `dollar` decimal(15,2) NOT NULL,
  `euro` decimal(15,2) NOT NULL,
  `cfa` decimal(15,2) NOT NULL,
  `id_c` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED;
 
--
-- Déclencheurs `reglement`
--
DROP TRIGGER IF EXISTS `ajout`;
DELIMITER $$
CREATE TRIGGER `ajout` AFTER INSERT ON `reglement` FOR EACH ROW BEGIN
        IF NEW.dollar != 0 THEN
                INSERT INTO `A` (`montant`,`operation`,`id_origine`,`comm`) VALUES(NEW.dollar,'reglement',New.id,NEW.id_c);
        END IF;
 
        IF NEW.euro !=0 THEN
                INSERT INTO `B` (`montant`,`operation`,`id_origine`,`comm`) VALUES(NEW.euro,'reglement',New.id,NEW.id_c);
        END IF;
 
        IF NEW.cfa !=0 THEN
                INSERT INTO `C` (`montant`,`operation`,`id_origine`,`comm`) VALUES(NEW.cfa,"reglement",New.id,NEW.id_c);
        END IF;
END
$$
DELIMITER ;
COMMIT;

**Artemus24** · 29/12/2020, 11h48

Salut baba13.

Si tu voulais le source, il suffisait de le demande :

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
START TRANSACTION;
 
set session collation_connection = "latin1_general_ci";
 
-- ======================
-- Base de Données `base`
-- ======================
 
DROP DATABASE IF EXISTS `base`;
 
CREATE DATABASE IF NOT EXISTS `base`
	DEFAULT CHARACTER SET `latin1`
	DEFAULT COLLATE       `latin1_general_ci`;
 
USE `base`;
 
-- =========
-- Table `A`
-- =========
 
DROP TABLE IF EXISTS `A`;
 
CREATE TABLE `A`
(  `id`          integer unsigned  NOT NULL auto_increment Primary key,
   `montant`     decimal(15,2)     NOT NULL,
   `operation`   varchar(255)      NOT NULL,
   `id_origine`  integer unsigned  NOT NULL,
   `comm`        integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =========
-- Table `B`
-- =========
 
DROP TABLE IF EXISTS `B`;
 
CREATE TABLE `B`
(  `id`          integer unsigned  NOT NULL auto_increment Primary key,
   `montant`     decimal(15,2)     NOT NULL,
   `operation`   varchar(255)      NOT NULL,
   `id_origine`  integer unsigned  NOT NULL,
   `comm`        integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =========
-- Table `C`
-- =========
 
DROP TABLE IF EXISTS `C`;
 
CREATE TABLE `C`
(  `id`          integer unsigned  NOT NULL auto_increment Primary key,
   `montant`     decimal(15,2)     NOT NULL,
   `operation`   varchar(255)      NOT NULL,
   `id_origine`  integer unsigned  NOT NULL,
   `comm`        integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =================
-- Table `reglement`
-- =================
 
DROP TABLE IF EXISTS `reglement`;
 
CREATE TABLE `reglement`
(  `id`        integer unsigned  NOT NULL auto_increment Primary key,
   `dollar`    decimal(15,2)     NOT NULL,
   `euro`      decimal(15,2)     NOT NULL,
   `cfa`       decimal(15,2)     NOT NULL,
   `id_c`      integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ===============
-- Trigger `ajout`
-- ===============
 
DROP TRIGGER IF EXISTS `ajout`;
 
DELIMITER $$
CREATE TRIGGER `ajout`
AFTER INSERT ON `reglement`
FOR EACH ROW
BEGIN
	IF NEW.dollar != 0 THEN
		INSERT INTO `A` (`montant`,`operation`,`id_origine`,`comm`) VALUES(NEW.dollar,'reglement',New.id,NEW.id_c);
	END IF;
 
	IF NEW.euro !=0 THEN
		INSERT INTO `B` (`montant`,`operation`,`id_origine`,`comm`) VALUES(NEW.euro,'reglement',New.id,NEW.id_c);
	END IF;
 
	IF NEW.cfa !=0 THEN
		INSERT INTO `C` (`montant`,`operation`,`id_origine`,`comm`) VALUES(NEW.cfa,"reglement",New.id,NEW.id_c);
	END IF;
END$$
DELIMITER ;
 
-- =======================
-- INSERT dans `reglement`
-- =======================
 
INSERT IGNORE INTO `reglement` (`dollar`,`euro`,`cfa`,`id_c`) VALUES
  (12.5,  0.0,  0.0, 100),
  ( 0.0, 25.0,  0.0, 200),
  ( 0.0,  0.0, 75.0, 300),
  (10.0, 20.0, 30.0, 400);
 
-- =====================
-- vidage de `reglement`
-- =====================
 
select * from `reglement`;
 
-- =============
-- vidage de `A`
-- =============
 
select * from `A`;
 
-- =============
-- vidage de `B`
-- =============
 
select * from `B`;
 
-- =============
-- vidage de `C`
-- =============
 
select * from `C`;
 
-- ===
-- FIN
-- ===
 
COMMIT;
EXIT

@+

Trigger ne marche pas comme souhaité

SQL Procédural MySQL

Discussions similaires

Partager

Partager