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 !