Déterminer dans un trigger si on fait un insert ou un update

Version imprimable

Bonjour,

J'ai le trigger suivant qui se déclenche à l'insertion
Code:

1 2 3 4 5 6 7 8 9 10 DELIMITER $$ CREATE TRIGGER `parc_before_insert` BEFORE INSERT ON `parc` FOR EACH ROW BEGIN SET NEW.`data` = FLOOR(12345679 + (RAND() * 86419754)); END$$ DELIMITER ;
Et j'ai une requête d'insertion avec une clause ON DUPLICATE KEY UPDATE

Comment puis-je déterminer dans le trigger s'il s'agit réellement d'un INSERT et non pas d'un UPDATE car la clé est déjà existante ? Le champ data ne doit jamais être mis à jour après avoir été généré.

Salut pascal-od.

Citation:

Envoyé par pascal-od

Comment puis-je déterminer dans le trigger s'il s'agit réellement d'un INSERT et non pas d'un UPDATE

Ton trigger ne traite que des insert puisque tu as mis "BEFORE INSERT".

Si tu veux traiter des update, il faut faire un autre trigger traitant que les update.

Citation:

Envoyé par pascal-od

Le champ data ne doit jamais être mis à jour après avoir été généré.

Autrement dit, tu veix signaler une erreur si la colonne data vient à changer durant un "update".

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`        integer unsigned  NOT NULL AUTO_INCREMENT primary key,
  `lib`       varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `modif`
--------------
 
--------------
CREATE TRIGGER `modif`
BEFORE update ON `test`
FOR EACH ROW BEGIN
  DECLARE msg varchar(255);
 
  if (old.lib != new.lib) then
    SET MSG = concat('modification interdite : (id = ', cast(old.id as char), ') --- (lib = ', cast(old.lib as char), ')');
    SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = MSG, MYSQL_ERRNO = 7777;
  end if;
END
--------------
 
--------------
insert into test (lib) VALUES ('un'),('deux'),('trois')
--------------
 
--------------
update test set lib = 'quatre' where lib like 'deux'
--------------
 
ERROR 7777 (07777) at line 53: modification interdite : (id = 2) --- (lib = deux)
--------------
select * from test
--------------
 
+----+-------+
| id | lib   |
+----+-------+
|  1 | un    |
|  2 | deux  |
|  3 | trois |
+----+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

21/03/2016, 08h56
pascal-od

En cas de clé déjà existante l'insert se transforme en update avec l'instruction "ON DUPLICATE KEY UPDATE ....", et le trigger est déclenché dans ce cas
21/03/2016, 10h48
Artemus24

Salut pascal-od.

Citation:

Envoyé par Pascal-od

En cas de clé déjà existante l'insert se transforme en update avec l'instruction "ON DUPLICATE KEY UPDATE ....", et le trigger est déclenché dans ce cas

Est-ce que tu as testé cet exemple ?

Oui, le trigger update est déclenché et va vérifier si la colonne lib va être changée
Si la colonne lib change alors tu auras un message d'anomalie qui va rejeter la mise à jour.
Donnes un exemple plus complet où tu rencontres un problèmes et dis nous ce que tu veux obtenir comme résultat.

@+

Bon, j'ai un doute sur le fonctionnement des triggers. Soit les tests contredisent la doc de mysql, soit j'ai foiré quelque chose dans mon raisonnement (je penche pour la seconde solution).

D'après la doc de mysql http://dev.mysql.com/doc/refman/5.5/...e-trigger.html

Citation:

A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger activates for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.

je comprend qu'un trigger BEFORE INSERT est déclenché si on fait un INSERT ou un UPDATE parce que la clé existe déjà. Ce qui est tout à fait logique puisque avant de faire l'insert on ne sait pas encore si la clé existe déjà.

Or d'après le test suivant cela ne semble pas être le cas, car le champ data n'est pas mis à jour.
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 CREATE TABLE `parc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `serial` char(25) NOT NULL, `status` int(11) NOT NULL, `data` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `serial` (`serial`) ); DELIMITER $$ CREATE TRIGGER `parc_before_insert` BEFORE INSERT ON `parc` FOR EACH ROW BEGIN SET NEW.`data` = FLOOR(12345679 + (RAND() * 86419754)); END$$ DELIMITER ; INSERT INTO parc(`serial` , `status`) VALUES ("A1" , 10) , ("A2" , 10) , ("A3" , 10) ON DUPLICATE KEY UPDATE `status` = VALUES(`status`); -- Le champ data a bien été renseigné après ce premier insert INSERT INTO parc(`serial` , `status`) VALUES ("A1" , 11) , ("A2" , 12) , ("A3" , 13) ON DUPLICATE KEY UPDATE `status` = VALUES(`status`); -- Le champ status à été mis à jour, mais le champ data n'a pas été modifié, le trigger ne se serait pas déclenché contrairement à ce qu'indique la doc ?
Qu'est-ce que j'ai oublié dans mon raisonnement ?

21/03/2016, 12h21
Artemus24

Salut pascal-od.

Citation:

Envoyé par pascal-od

je comprend qu'un trigger BEFORE INSERT est déclenché si on fait un INSERT ou un UPDATE parce que la clé existe déjà.

Votre erreur de raisonnement est que vous croyez qu'un trigger fait tout. Et bien non, il fait ce pourquoi il a été déclaré.

Un trigger ayant "before insert" va se déclencher avant l'insertion.
Un trigger ayant "after insert" va se déclencher après l'insertion.

Un trigger ayant "before update" va se déclencher avant la mise à jour.
Un trigger ayant "after update" va se déclencher après la mise à jour.

Un trigger ayant "before delete" va se déclencher avant la suppression.
Un trigger ayant "after delete" va se déclencher après la suppression.

Donc vous avez six cas à traiter selon ce que vous voulez faire !

Ensuite, il y a les requêtes suivantes :
--> insert
--> update
--> delete
--> replace = delete + insert

Citation:

Envoyé par pascal-od

Ce qui est tout à fait logique puisque avant de faire l'insert on ne sait pas encore si la clé existe déjà.

Non, votre logique est foireuse car vous ne tenez pas compte du qualificatif de votre trigger.
Un trigger "before insert" traite que de l'insertion et rien d'autre. Il se déclenche avant l'écriture dans la table.

Si vous demandez à faire une insertion alors que la ligne n'existe pas.
Le trigger "before insert" va se déclencher et venir renseigner la colonne "data" comme vous l'avez demandé.
Et c'est le comportement normal !

Dans le deuxième exemple, vous demandez à faire aussi une insertion, alors que la ligne existe déjà.
Le trigger "before insert" ne se déclenche pas car ici, il s'agit d'une mise à jour et non d'une insertion. Pourquoi ?
Car dans l'insert, vous avez indiqué ceci : "ON DUPLICATE KEY UPDATE `status` = VALUES(`status`)".
Or ceci correspond à un update !!!

Un insert seul correspond à une insertion.
Un insert avec un "on duplicate key update" correspond, selon l'existence de la ligne, soit à une insertion, soit une mise à jour.
Un update seul correspond à une mise à jour.

Dans votre exemple, je ne voie aucun trigger ayant soit "before update" ou "after update".
Votre demande, en cas d'existence de la clef, est de mettre à jour uniquement la colonne "status" et c'est bien ce que fait MySql.

Que cherchez-vous à faire ?

@+
21/03/2016, 12h39
pascal-od

Je cherche à comprendre cette partie de la documentation :) (ou plutôt pourquoi le comportement que je constate ne correspond pas à celui décrit dans la documentation).

Citation:

A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger activates for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.

Je comprends à la lecture que le trigger BEFORE INSERT est activé pour chaque ligne qu'on essaie d'insérer et non pas pour chaque ligne réellement insérée. Et que ensuite c'est soit AFTER INSERT ou BEFORE UPDATE et AFTER UPDATE qui sont déclenchés selon l'opération qui a réellement eu lieu (insert ou update).

J'ai fait le test suivant :

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
 
CREATE TABLE `parc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serial` char(25) NOT NULL,
  `status` int(11) NOT NULL,
  `data` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial` (`serial`)
);
 
CREATE TABLE `log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL,
  PRIMARY KEY (`id`)
);
 
DELIMITER $$
CREATE
    TRIGGER `parc_before_insert` BEFORE INSERT 
    ON `parc` 
    FOR EACH ROW BEGIN
            SET NEW.`data` = FLOOR(12345679 + (RAND() * 86419754));
			INSERT INTO log (`time`) VALUES (NOW());
    END$$
DELIMITER ;
 
INSERT INTO parc(`serial` , `status`)
VALUES
	("A1" , 10) ,
	("A2" , 10) ,
	("A3" , 10) ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);
 
 
INSERT INTO parc(`serial` , `status`)
VALUES
	("A1" , 11) ,
	("A2" , 14) ,
	("A3" , 13) ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);

J'ai au final 6 lignes dans la table log et 3 dans la table parc, donc le trigger BEFORE INSERT est bien déclenché même si la requête INSERT est transformée en UPDATE parce que la clé serial est déjà existante.

Par contre le champ data n'est pas mis à jour lors du second INSERT du test.

C'est ce point précis que j'aimerai éclaircir : pourquoi SET NEW.`data` = FLOOR(12345679 + (RAND() * 86419754)); n'est pas pris en compte dans le second insert ?

Salut pascal-od.

Voici un test un peu plus complet :

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
  DEFAULT CHARACTER SET `latin1`
  DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `hist`
--------------
 
--------------
CREATE TABLE `hist`
( `id`   integer unsigned NOT NULL auto_increment primary key,
  `lib`  varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `parc`
--------------
 
--------------
CREATE TABLE `parc`
( `id`        integer unsigned  NOT NULL auto_increment primary key,
  `serial`    char(25)          NOT NULL,
  `status`    integer unsigned  NOT NULL,
  `data`      integer           NOT NULL,
  UNIQUE KEY `serial` (`serial`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
CREATE TRIGGER `parc_before_insert`
BEFORE INSERT ON `parc`
FOR EACH ROW BEGIN
  SET NEW.`data` = FLOOR(12345679 + (RAND() * 86419754));
  insert into `hist` (`lib`) values (concat('before insert : ', cast(new.serial as char), ' -- ', cast(new.status as char), ' -- ', cast(new.data as char)));
END
--------------
 
--------------
CREATE TRIGGER `parc_after_insert`
AFTER INSERT ON `parc`
FOR EACH ROW BEGIN
  insert into `hist` (`lib`) values (concat('after  insert : ', cast(new.serial as char), ' -- ', cast(new.status as char), ' -- ', cast(new.data as char)));
END
--------------
 
--------------
CREATE TRIGGER `parc_before_update`
BEFORE UPDATE ON `parc`
FOR EACH ROW BEGIN
  insert into `hist` (`lib`) values (concat('before update : ', cast(old.serial as char), ' -- ', cast(new.status as char), ' -- ', cast(new.data as char)));
END
--------------
 
--------------
CREATE TRIGGER `parc_after_update`
AFTER UPDATE ON `parc`
FOR EACH ROW BEGIN
  insert into `hist` (`lib`) values (concat('after  update : ', cast(old.serial as char), ' -- ', cast(new.status as char), ' -- ', cast(new.data as char)));
END
--------------
 
--------------
select * from parc
--------------
 
--------------
INSERT INTO `parc` (`serial`, `status`) VALUES
 ("A1", 10), ("A2", 10), ("A3", 10)
 ON DUPLICATE KEY UPDATE status = values(`status`)
--------------
 
--------------
select * from parc
--------------
 
+----+--------+--------+----------+
| id | serial | status | data     |
+----+--------+--------+----------+
|  1 | A1     |     10 | 76447882 |
|  2 | A2     |     10 | 33267429 |
|  3 | A3     |     10 | 97487333 |
+----+--------+--------+----------+
--------------
INSERT INTO `hist` (`lib`) VALUES ('------------------------------------')
--------------
 
--------------
INSERT INTO `parc` (`serial`, `status`) VALUES
 ("A1", 11), ("A2", 12), ("A3", 13)
 ON DUPLICATE KEY UPDATE status = values(`status`)
--------------
 
--------------
select * from parc
--------------
 
+----+--------+--------+----------+
| id | serial | status | data     |
+----+--------+--------+----------+
|  1 | A1     |     11 | 76447882 |
|  2 | A2     |     12 | 33267429 |
|  3 | A3     |     13 | 97487333 |
+----+--------+--------+----------+
--------------
select * from hist
--------------
 
+----+--------------------------------------+
| id | lib                                  |
+----+--------------------------------------+
|  1 | before insert : A1 -- 10 -- 76447882 |
|  2 | after  insert : A1 -- 10 -- 76447882 |
|  3 | before insert : A2 -- 10 -- 33267429 |
|  4 | after  insert : A2 -- 10 -- 33267429 |
|  5 | before insert : A3 -- 10 -- 97487333 |
|  6 | after  insert : A3 -- 10 -- 97487333 |
|  7 | ------------------------------------ |
|  8 | before insert : A1 -- 11 -- 29609686 |
|  9 | before update : A1 -- 11 -- 76447882 |
| 10 | after  update : A1 -- 11 -- 76447882 |
| 11 | before insert : A2 -- 12 -- 16080262 |
| 12 | before update : A2 -- 12 -- 33267429 |
| 13 | after  update : A2 -- 12 -- 33267429 |
| 14 | before insert : A3 -- 13 -- 65646331 |
| 15 | before update : A3 -- 13 -- 97487333 |
| 16 | after  update : A3 -- 13 -- 97487333 |
+----+--------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

On voit bien que dans l'insertion seul, les trigger before et after insert se déclenche.

Dans le cas de l'insertion avec mise-à-jour, il y a bien un before insert afin de tester l'existence de la ligne.
Puis ensuite, il y a un before et after update.

Dans ce test, on remarque que le passage dans le before insert recalcule la valeur du data.
Mais comme la ligne existe déjà, MySql n'en tient pas compte.

@+

21/03/2016, 13h29
escartefigue

Citation:

Envoyé par Artemus24

Non, votre logique est foireuse car vous ne tenez pas compte du qualificatif de votre trigger.
[. . .]
Le trigger "before insert" ne se déclenche pas car ici, il s'agit d'une mise à jour et non d'une insertion. Pourquoi ?
Car dans l'insert, vous avez indiqué ceci : "ON DUPLICATE KEY UPDATE `status` = VALUES(`status`)".
Or ceci correspond à un update

Non, la documentation explique clairement que ce n'est pas comme ça que cela fonctionne dans le cas particulier d'un insert avec "on duplicate" :
Exécution du trigger "before insert" quoiqu'il arrive si présent, puis, si clef en double, exécution du trigger "before update" et/ou "after update", si pas de clef en double, exécution du trigger "after insert" si présent.
Le test de pascal-od, confirme que le trigger "before insert" est bien exécuté dans tous les cas. Il ne confirme pas le lancement de trigger before ou after update ... faute de trigger update ;)
21/03/2016, 13h34
Artemus24

Salut Escartefigue.

Que le before insert soit exécuté, mon test le confirme.
Inversement, MySql ne tient pas compte de ce before insert car il sert juste à déterminer l'existence de la ligne.
La preuve se trouve dans la valeur de la colonne data qui bien que recalculé dans le before insert, n'est pas écrasé par la suite.

@+
21/03/2016, 13h45
escartefigue

Relisez votre post et le mien : vous avez écrit "Le trigger "before insert" ne se déclenche pas" , or c'est faux, CQFD
21/03/2016, 16h58
Artemus24
Salut Escartefigue.

Après m'avoir relu, il semble que mes explications ne sont pas très claires.

Nous parlons d'une requête de ce genre :
Code:

1 2 INSERT INTO `parc` (`serial`, `status`) VALUES ("A1", 11), ("A2", 12), ("A3", 13) ON DUPLICATE KEY UPDATE status = values(`status`);
Je tiens à préciser que je parle de l'impact sur la table 'parc' et non sur d'autres tables.

Cette requête est composée de deux parties : l'insertion et la mise à jour.

OUI, on passe bien dans un trigger "before insert" afin de tester l'existence de la ligne.
Et NON, aucune des modifications qui sont faites dans le trigger "before insert" seront appliquées à la ligne. Pourquoi ?

Car la requête demande à faire une insertion. Elle vérifie si la ligne existe déjà.
Si la ligne n'existe pas, tout ce passe comme un insert normal.
Si la ligne existe déjà, tout ce passe comme un update normal.

Donc pour vous répondre Escartefigue, même si le trigger "before insert" se déclenche, il ne sert à rien sauf à tester l'existence de la ligne.

@+
21/03/2016, 17h11
escartefigue

Oui je pense que maintenant tout le monde est d'accord sur ce qui se passe
Pascal élargissait le débat à l'ensemble des tables concernées et notamment la table log qui se retrouve bien avec 6 records avec son jeu d'essais.
Cet exemple est intéressant, car c'est vrai que le cas du "on duplicate" est un peu particulier
21/03/2016, 17h15
Artemus24

Si l'on veut aller plus loin, il faut comparer de part le fonctionnement des trigger, le REPLACE.
Normalement, un REPLACE = DELETE + INSERT.
Mais je pense que d'un SGBD à un autre, le fonctionnement n'est pas le même.

J'ai fait un test, croyant retrouver ce DELETE + INSERT et je m'aperçois que j'obtiens :
--> insert before.
--> update before.
--> update after.
--> insert after.

Au final, je retrouve bien dans la table parc ce à quoi je m'attends mais je ne comprends pas cette succession des trigger.

@+
21/03/2016, 17h40
pascal-od

En tout cas le signalement potentially confusing example sur la doc est pleinement justifié :)

Merci à vous deux pour vos réponses.

Ma question initiale est résolue, puisque le trigger ne va pas modifier la table parc dans le cas d'une clé déjà existante. Je pensais que c'était le cas après lecture de la doc, et c'est pourquoi je souhaitais pouvoir déterminer si on était dans le cas d'un insert ou d'un update.

Salut à tous.

Voici un récapitulatif :

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
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
  DEFAULT CHARACTER SET `latin1`
  DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `hist`
--------------
 
--------------
CREATE TABLE `hist`
( `id`   integer unsigned NOT NULL AUTO_INCREMENT primary key,
  `old`  varchar(255)         NULL,
  `new`  varchar(255)         NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `parc`
--------------
 
--------------
CREATE TABLE `parc`
( `id`      integer unsigned  NOT NULL primary key,
  `serial`  char(25)          NOT NULL,
  `status`  integer unsigned  NOT NULL,
  `data`    integer           NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
CREATE TRIGGER `parc_before_insert`
BEFORE INSERT ON `parc`
FOR EACH ROW BEGIN
  DECLARE _new  varchar(255);
 
  SET NEW.`data` = FLOOR(12345679 + (RAND() * 86419754));
  SET _new       = concat('before insert : ', cast(new.serial as char), ' -- ', cast(new.status as char), ' -- ', cast(new.data as char));
 
  INSERT INTO `hist` (`old`,`new`) values ('', _new);
END
--------------
 
--------------
CREATE TRIGGER `parc_after_insert`
AFTER INSERT ON `parc`
FOR EACH ROW BEGIN
  DECLARE _new  varchar(255);
 
  SET _new = concat('after  insert : ', cast(new.serial as char), ' -- ', cast(new.status as char), ' -- ', cast(new.data as char));
 
  INSERT INTO `hist` (`old`,`new`) values ('', _new);
END
--------------
 
--------------
CREATE TRIGGER `parc_before_update`
BEFORE UPDATE ON `parc`
FOR EACH ROW BEGIN
  DECLARE _old varchar(255);
  DECLARE _new  varchar(255);
 
  SET _old = concat('before update : ', cast(old.serial as char), ' -- ', cast(old.status as char), ' -- ', cast(old.data as char));
  SET _new = concat('before update : ', cast(new.serial as char), ' -- ', cast(new.status as char), ' -- ', cast(new.data as char));
 
  INSERT INTO `hist` (`old`,`new`) values (_old, _new);
END
--------------
 
--------------
CREATE TRIGGER `parc_after_update`
AFTER UPDATE ON `parc`
FOR EACH ROW BEGIN
  DECLARE _old varchar(255);
  DECLARE _new  varchar(255);
 
  SET _old = concat('after  update : ', cast(old.serial as char), ' -- ', cast(old.status as char), ' -- ', cast(old.data as char));
  SET _new = concat('after  update : ', cast(new.serial as char), ' -- ', cast(new.status as char), ' -- ', cast(new.data as char));
 
  INSERT INTO `hist` (`old`,`new`) values (_old, _new);
END
--------------
 
--------------
CREATE TRIGGER `parc_before_delete`
BEFORE DELETE ON `parc`
FOR EACH ROW BEGIN
  DECLARE _old varchar(255);
 
  SET _old = concat('before delete : ', cast(old.serial as char), ' -- ', cast(old.status as char), ' -- ', cast(old.data as char));
 
  INSERT INTO `hist` (`old`,`new`) values (_old, '');
END
--------------
 
--------------
CREATE TRIGGER `parc_after_delete`
AFTER DELETE ON `parc`
FOR EACH ROW BEGIN
  DECLARE _old varchar(255);
 
  SET _old = concat('after  delete : ', cast(old.serial as char), ' -- ', cast(old.status as char), ' -- ', cast(old.data as char));
 
  INSERT INTO `hist` (`old`,`new`) values (_old, '');
END
--------------
 
--------------
INSERT INTO `parc` (`id`, `serial`, `status`) VALUES
 (1, "A1", 10), (2, "A2", 10), (3, "A3", 10)
 ON DUPLICATE KEY UPDATE status = values(`status`)
--------------
 
--------------
select * from parc
--------------
 
+----+--------+--------+----------+
| id | serial | status | data     |
+----+--------+--------+----------+
|  1 | A1     |     10 | 61205804 |
|  2 | A2     |     10 | 54741178 |
|  3 | A3     |     10 | 77742801 |
+----+--------+--------+----------+
--------------
INSERT INTO `hist` (`old`,`new`) VALUES ('------------------------------------', '------------------------------------')
--------------
 
--------------
INSERT INTO `parc` (`id`, `serial`, `status`) VALUES
 (1, "A1", 11), (2, "A2", 12), (3, "A3", 13)
 ON DUPLICATE KEY UPDATE status = values(`status`)
--------------
 
--------------
select * from parc
--------------
 
+----+--------+--------+----------+
| id | serial | status | data     |
+----+--------+--------+----------+
|  1 | A1     |     11 | 61205804 |
|  2 | A2     |     12 | 54741178 |
|  3 | A3     |     13 | 77742801 |
+----+--------+--------+----------+
--------------
INSERT INTO `hist` (`old`,`new`) VALUES ('------------------------------------', '------------------------------------')
--------------
 
--------------
REPLACE INTO `parc` (`id`, `serial`, `status`) VALUES (1, "A1", 14), (2, "A2", 15), (3, "A3", 16)
--------------
 
--------------
select * from parc
--------------
 
+----+--------+--------+----------+
| id | serial | status | data     |
+----+--------+--------+----------+
|  1 | A1     |     14 | 72113386 |
|  2 | A2     |     15 | 92004406 |
|  3 | A3     |     16 | 58496688 |
+----+--------+--------+----------+
--------------
INSERT INTO `hist` (`old`,`new`) VALUES ('------------------------------------', '------------------------------------')
--------------
 
--------------
REPLACE INTO `parc` (`id`, `serial`, `status`) VALUES (4, "A1", 14), (5, "A2", 15), (6, "A3", 16)
--------------
 
--------------
select * from parc
--------------
 
+----+--------+--------+----------+
| id | serial | status | data     |
+----+--------+--------+----------+
|  1 | A1     |     14 | 72113386 |
|  2 | A2     |     15 | 92004406 |
|  3 | A3     |     16 | 58496688 |
|  4 | A1     |     14 | 90544299 |
|  5 | A2     |     15 | 92046250 |
|  6 | A3     |     16 | 89832920 |
+----+--------+--------+----------+
--------------
select * from hist
--------------
 
+----+--------------------------------------+--------------------------------------+
| id | old                                  | new                                  |
+----+--------------------------------------+--------------------------------------+
|  1 |                                      | before insert : A1 -- 10 -- 61205804 |
|  2 |                                      | after  insert : A1 -- 10 -- 61205804 |
|  3 |                                      | before insert : A2 -- 10 -- 54741178 |
|  4 |                                      | after  insert : A2 -- 10 -- 54741178 |
|  5 |                                      | before insert : A3 -- 10 -- 77742801 |
|  6 |                                      | after  insert : A3 -- 10 -- 77742801 |
|  7 | ------------------------------------ | ------------------------------------ |
|  8 |                                      | before insert : A1 -- 11 -- 39305288 |
|  9 | before update : A1 -- 10 -- 61205804 | before update : A1 -- 11 -- 61205804 |
| 10 | after  update : A1 -- 10 -- 61205804 | after  update : A1 -- 11 -- 61205804 |
| 11 |                                      | before insert : A2 -- 12 -- 37372114 |
| 12 | before update : A2 -- 10 -- 54741178 | before update : A2 -- 12 -- 54741178 |
| 13 | after  update : A2 -- 10 -- 54741178 | after  update : A2 -- 12 -- 54741178 |
| 14 |                                      | before insert : A3 -- 13 -- 56599032 |
| 15 | before update : A3 -- 10 -- 77742801 | before update : A3 -- 13 -- 77742801 |
| 16 | after  update : A3 -- 10 -- 77742801 | after  update : A3 -- 13 -- 77742801 |
| 17 | ------------------------------------ | ------------------------------------ |
| 18 |                                      | before insert : A1 -- 14 -- 72113386 |
| 19 | before delete : A1 -- 11 -- 61205804 |                                      |
| 20 | after  delete : A1 -- 11 -- 61205804 |                                      |
| 21 |                                      | after  insert : A1 -- 14 -- 72113386 |
| 22 |                                      | before insert : A2 -- 15 -- 92004406 |
| 23 | before delete : A2 -- 12 -- 54741178 |                                      |
| 24 | after  delete : A2 -- 12 -- 54741178 |                                      |
| 25 |                                      | after  insert : A2 -- 15 -- 92004406 |
| 26 |                                      | before insert : A3 -- 16 -- 58496688 |
| 27 | before delete : A3 -- 13 -- 77742801 |                                      |
| 28 | after  delete : A3 -- 13 -- 77742801 |                                      |
| 29 |                                      | after  insert : A3 -- 16 -- 58496688 |
| 30 | ------------------------------------ | ------------------------------------ |
| 31 |                                      | before insert : A1 -- 14 -- 90544299 |
| 32 |                                      | after  insert : A1 -- 14 -- 90544299 |
| 33 |                                      | before insert : A2 -- 15 -- 92046250 |
| 34 |                                      | after  insert : A2 -- 15 -- 92046250 |
| 35 |                                      | before insert : A3 -- 16 -- 89832920 |
| 36 |                                      | after  insert : A3 -- 16 -- 89832920 |
+----+--------------------------------------+--------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

21/03/2016, 18h07
Artemus24
Salut Pascal-od.

Citation:

Envoyé par pascal-od

Ma question initiale est résolue, puisque le trigger ne va pas modifier la table parc dans le cas d'une clé déjà existante.

Tout dépend comment tu vas intervenir sur la table.

En faisant cette requête :
Code:

1 2 INSERT INTO `parc` (`id`, `serial`, `status`) VALUES (1, "A1", 11), (2, "A2", 12), (3, "A3", 13) ON DUPLICATE KEY UPDATE status = values(`status`);
en effet, tu auras le comportement que tu souhaites.

Si maintenant, tu fais cette requête :

Code:

REPLACE INTO `parc` (`id`, `serial`, `status`) VALUES (1, "A1", 11), (2, "A2", 12), (3, "A3", 13);

MySql va détruire l'ancienne ligne et créer la nouvelle ligne. De ce fait, la colonne 'data' va changer de valeur.

Citation:

Envoyé par Pascale-od

Je pensais que c'était le cas après lecture de la doc, et c'est pourquoi je souhaitais pouvoir déterminer si on était dans le cas d'un insert ou d'un update.

Si tu veux un insert, alors fait un insert. Si tu veux un update alors fait un update.
Si maintenant, tu utilises soit la première ou la seconde requête ci-dessus, tu auras pas nécessairement le comportement d'un insert simple ou d'un update simple.

Si tu es débutant, il vaut mieux en php, faire un select et voir le résultat que tu obtiens.
Si la ligne existe déjà alors fait ensuite un update sinon un insert.

En gérant le "insert" simple et le "update" simple, tu n'auras aucun effet de bord sur les trigger.
Et c'est pourquoi, l'usage d'un trigger doit correspondre à un comportement spécifique.
Maintenant, si c'est juste pour résoudre la valeur par défaut de la colonne 'data', il faut mieux traiter cela en php, que de le faire en MySql.

@+