Discussion :

[nicocardo]

Bonjour,
Dans le cadre d'un projet je suis en train de concevoir une BDD (MySQL 8).

Dans cette base j'ai une table "Seance" qui contient la colonne "seance_position".
A chaque nouvelle insertion dans la table Seance, il faut gérer la position de la nouvelle seance parmi les autres d'un même programme (table "Programme").
Pour celà j'ai conçu un trigger qui gère seance_position.

Il met la position la plus haute si on ne specifie rien ('' ou null). Et cette partie du trigger fonctionne .

Mais si on specifie la position,

Code :

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

INSERT INTO `seance` (`seance_id`, `seance_nom`, `seance_duree`, `seance_position`, `seance_programme_id`) VALUES (NULL, 'test6', '0', POSITION SPECIFIQUE, '1');

le trigger doit mettre position+1 à toutes les autres seances (du même programme) ayant une position superieure ou égale à celle de l'insertion.

Or, au lancement de cet INSERT, dans ce cas de figure, le trigger ne fonctionne pas et on me renvoie le message d'erreur:

#1442 - Can't update table 'seance' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Je ne comprends pas où est le problème car mon trigger "me semble" cohérent:

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
 
CREATE TRIGGER `seance_before_insert` BEFORE INSERT ON `seance`
FOR EACH ROW 
BEGIN
	DECLARE nb_ligne INT;
	DECLARE max_pos INT;
	SET nb_ligne := (SELECT COUNT(*) FROM Seance WHERE seance_programme_id = NEW.seance_programme_id);
 
	IF (NEW.seance_position = "") THEN
    		-- chaine vide = null
    		SET NEW.seance_position := NULL;
	END IF;
 
	IF NEW.seance_position IS NULL THEN
    		IF (nb_ligne < 1) THEN
        		SET NEW.seance_position = 1;
        	ELSE
        		SET max_pos := (SELECT MAX(seance_position) FROM Seance WHERE seance_programme_id = NEW.seance_programme_id) + 1;
 
            		SET NEW.seance_position := max_pos;
        	END IF;
	ELSE
    		IF (NEW.seance_position > 0) THEN
            		----------------------- ce qui ne fonctionne pas --------------------------
            		UPDATE Seance 
			SET seance_position = seance_position + 1 
			WHERE seance_programme_id = NEW.seance_programme_id
            		AND seance_position >= NEW.seance_position;
            		---------------------------------------------------------------------------
        	ELSE -- position <= 0
        		SIGNAL SQLSTATE "45000" 
			SET MESSAGE_TEXT = "Position 0 est interdit !\nInsertion annulée";
		END IF;
	END IF; 
END

Je me retrouve bien embêté .

Merci d'avance pour vos réponses.

[escartefigue]

Bonjour,

Le trigger est invoqué par une mise à jour sur la même table que celle qu'il met à jour, d'où une invocation récursive du trigger...

[tatayo]

Bonjour,
Je pense que ton problème vient de la ligne 28: ton trigger tente de modifier la ligne "qui a déclenché" le trigger, et tout part en boucle.
Et le problème se propage aussi aux lignes qui sont mises à jour dans ton trigger: chaque mise à jour déclenche le trigger, qui met à jour des lignes, ce qui déclenche le trigger...

Il faut voir si MySQL permet de ne pas déclencher les triggers en cascade.

Tatayo.

[nicocardo]

Je ne crois pas que le trigger se déclenche en boucle, c'est un trigger "before insert", mais la ligne 28 est un update.
Je commence à comprendre que l'on ne peut pas faire de modification sur la même table qui est ciblée par le trigger (ce que je trouve abérent).
En partant de ce principe, ce que je veut faire me parait impossible
A moins que je doive utiliser un "after insert" ??

[escartefigue]

Comme dit plus haut, MySQL considère qu'il s'agit d'un appel récursif.

Cf. la doc officielle ici qui stipule notamment :

A.5.9.

Can triggers access tables?

A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

[escartefigue]

Avec n'importe quel autre SGBD que MySQL, il aurait suffit de faire une requête update du genre

Code SQL :

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

1
2
UPDATE ma_table
SET ma_colonne=ma_colonne+1

Sauf que MySQL ne valide pas la transaction de façon ensembliste mais ligne à ligne et considère donc que cette requête génère des doublons
Mais, puisque vous avez la V8 de MySQL, plutôt que de calculer un rang qui peut être modifié à chaque insertion, pourquoi ne pas tout simplement le calculer quand vous en avez besoin avec l'une des fonctions analytiques apportées par la V8 (ROW_NUMBER(), RANK(), DENSE_RANK()). Ca me semble bien plus simple
Par contre, pour utiliser l'une de ces fonctions, il faut un critère de tri, par exemple un horodatage de création, y en a -t-il un dans la table ?

[nicocardo]

Avec n'importe quel autre SGBD que MySQL, il aurait suffit de faire une requête update du genre

Code SQL :

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

1
2
UPDATE ma_table
SET ma_colonne=ma_colonne+1

Sauf que MySQL ne valide pas la transaction de façon ensembliste mais ligne à ligne et considère donc que cette requête génère des doublons
Mais, puisque vous avez la V8 de MySQL, plutôt que de calculer un rang qui peut être modifié à chaque insertion, pourquoi ne pas tout simplement le calculer quand vous en avez besoin avec l'une des fonctions analytiques apportées par la V8 (ROW_NUMBER(), RANK(), DENSE_RANK()). Ca me semble bien plus simple
Par contre, pour utiliser l'une de ces fonctions, il faut un critère de tri, par exemple un horodatage de création, y en a -t-il un dans la table ?

Merci pour votre réponse.
Je viens de me renseigner sur les fonctions ROW_NUMBER(), RANK() et DENSE_RANK() (que je ne connaissais pas ). Je ne pense pas pouvoir les appliquer dans mon cas de figure. En effet, je doit laisser la possibilité aux utilisteurs de modifier l'ordre (seance_position) à leur bon vouloir.

La situation est la suivante:
Il y a (par exemple) un Programme "prog1".
Si on veut créer 4 seances (francais, math, histoire, géo) à prog1 sans preciser leur position, mon trigger ci-dessus va mettre automatiquement fr = 1, math =2,... et ça ça fonctionne 👌.
Mais admettons que je decide de rajouter "sport" en position 2, je doit decaler tout le reste de 1 : fr = 1, sport = 2, math = 3... Et ça je ne peux pas le faire.
Je ne comprends pas, ça me semble pourtant basique sur le principe, c'est fou que ce soit impossible sur MySQL !

[nicocardo]

Très embêtant cette histoire, je vois sur les forums qu'il y a pleins de personnes dans ma situation

Juste pour savoir, car j'ai "encore" le temps de reprendre la base à plus ou moins 0.
Est ce que si je refais toute ma base sous MariaDB, le problème existera toujours ?

[isabelle.letrong]

Est ce que si je refais toute ma base sous MariaDB, le problème existera toujours ?

Non voir https://mariadb.com/kb/en/stored-function-limitations/: 'Cannot make changes to a table that is already in use (reading or writing) by the statement invoking the stored function.' => idem MySQL

Malheureusement vous ne pourrez traiter cela via un trigger.
Je règle ce genre de problèmes via une procédure stockée qui réalisera l'update ensembliste et dont le propriétaire est le seul à avoir accès en mise à jour ce qui garantit en prod l'intégrité de mise à jour sur la table (la table ne peut être mise à jour que par du code validé)

[Artemus24]

Salut à tous.

Sous Mysql (ainsi que sous mariadb), on ne peut pas mettre à jour les lignes d'une table, autre que celle déclenchée par le trigger.
De même, utiliser une procédure stockée dans un trigger aura le même effet.

La solution n'est pas très compliqué à mettre en œuvre.
Dans ce cas, il ne faut pas utiliser un trigger mais une procédure stockée.
Vous allez simuler une insertion, comme dans mon exemple ci-après :

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `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,
  `rang`       integer unsigned     NULL,
  unique index `idx` (`rang`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP PROCEDURE IF EXISTS `maj`
--------------
 
--------------
CREATE PROCEDURE `maj` (IN In_Lib  varchar(255),
                        IN In_Rang integer)
BEGIN
  DECLARE _rang integer DEFAULT NULL;
 
  IF (In_Rang is null) THEN SET _rang=(select coalesce(max(rang),0)+1 from `test`);
  ELSE
     SET _rang = In_Rang;
     update `test` set rang=rang+1 where rang >= In_Rang order by rang desc;
  END IF;
 
  INSERT INTO `test` (`lib`,`rang`) values (In_Lib, _rang);
 
END
--------------
 
--------------
call `maj` ('français', NULL)
--------------
 
--------------
call `maj` ('math',     NULL)
--------------
 
--------------
call `maj` ('histoire', NULL)
--------------
 
--------------
call `maj` ('géo',      NULL)
--------------
 
--------------
select * from `test` order by `rang`
--------------
 
+----+----------+------+
| id | lib      | rang |
+----+----------+------+
|  1 | français |    1 |
|  2 | math     |    2 |
|  3 | histoire |    3 |
|  4 | géo      |    4 |
+----+----------+------+
--------------
call `maj` ('sport',    2)
--------------
 
--------------
select * from `test` order by `rang`
--------------
 
+----+----------+------+
| id | lib      | rang |
+----+----------+------+
|  1 | français |    1 |
|  5 | sport    |    2 |
|  2 | math     |    3 |
|  3 | histoire |    4 |
|  4 | géo      |    5 |
+----+----------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

[nicocardo]

Merci pour les explications

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
 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `rangerSeance`(IN `evenement` VARCHAR(128), IN `nom` VARCHAR(128), IN `position` INT, IN `parent` INT)
    NO SQL
BEGIN
	DECLARE nb_ligne INT;
    DECLARE max_pos INT;
 
	IF (evenement = "insert") THEN  
        SET nb_ligne = (SELECT COUNT(*) FROM Seance WHERE seance_programme_id = parent);
 
        IF position IS NULL THEN
 
            IF (nb_ligne < 1) THEN
                SET position = 1;
            ELSE
                SET max_pos = (SELECT MAX(seance_position) FROM Seance WHERE seance_programme_id = parent) + 1;
                SET position = max_pos;
            END IF;
        ELSE
            IF (position > 0) THEN
                UPDATE Seance SET seance_position = seance_position + 1 WHERE seance_programme_id = parent AND seance_position >= position;
            ELSE -- si position <= 0
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Insert Rejeté";
            END IF;
        END IF; 
 
        INSERT INTO seance (seance_id, seance_nom, seance_duree, seance_position, seance_programme_id) VALUES (NULL, nom, NULL, position, parent);
	END IF;
END$$
DELIMITER ;

Voilà ma procedure stockée FONCTIONNELLE 🎉