Triggers et procédure stockée : ERROR 1336 [Résolu]

[spg40]

Bonjour,

j'ai un problème lors de l'utilisation de triggers sur une table.

J'ai une table t1(id VARCHAR, dpt VARCHAR, nom VARCHAR, adr VARCHAR ) et des tables t1_10, t1_15, t1_20 avec la même structure que t1 mais qui contiennent les données de t1 avec dpt=10, 15, 20 respectivement.
Lorsque j'insère un nouvel enregistrement dans t1, je voudrais l'insérer aussi dans la table t1_dpt correspondant à l'enregistrement. De même, en cas de modification ou de suppression. J'ai donc créée une procédure stockée qui est appelée par les triggers.

J'ai donc les triggers et la procédure suivants :

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
 
DELIMITER $$
 
DROP TRIGGER t1_INS $$
CREATE TRIGGER ETABUFR_INS AFTER INSERT ON t1 FOR EACH ROW
  CALL  MAJ_t1_dpt(NEW.Dpt, NEW.Id, 'INSERT') $$
 
DROP TRIGGER t1_DEL $$
CREATE TRIGGER ETABUFR_DEL BEFORE DELETE ON t1 FOR EACH ROW
  CALL  MAJ_t1_dpt(OLD.Dpt, OLD.Id, 'DELETE') $$
 
DROP TRIGGER t1_UPD $$
CREATE TRIGGER ETABUFR_UPD AFTER UPDATE ON t1 FOR EACH ROW
  CALL  MAJ_t1_dpt(NEW.Dpt, NEW.Id, 'UPDATE') $$
 
DROP PROCEDURE MAJ_t1_dpt $$
CREATE PROCEDURE MAJ_t1_dept (IN vDept VARCHAR(8), IN id VARCHAR(32), IN typeAction VARCHAR(16))
BEGIN
  DECLARE NameTable VARCHAR(30) DEFAULT 't1_';
  SET NameTable = CONCAT('t1_', vDept) ;
 
  IF ( typeAction = 'INSERT' ) THEN
		-- Début De La Requête D'Insertion
		SET @Stmt := Concat('INSERT INTO ', Nametable ,' Select * From t1 Where Id = \'', id ,'\' ;');
 
		-- On Prépare Le Stmt Avec @Stmt Qui Contient Notre Requête
		Prepare Stmt FROM @Stmt;
		-- On Exécute Donc La Requête
		Execute Stmt;
		-- On Supprime Le Stmt
		Deallocate Prepare Stmt;
 
	ELSEIF ( typeAction = 'DELETE' ) THEN
		-- Début De La Requête De Suppression
		SET @Stmt := Concat('DELETE FROM ', Nametable ,' Where Id = \'', id ,'\' ;');
 
		-- On Prépare Le Stmt Avec @Stmt Qui Contient Notre Requête
		Prepare Stmt FROM @Stmt;
		-- On Exécute Donc La Requête
		Execute Stmt;
		-- On Supprime Le Stmt
		Deallocate Prepare Stmt;
 
	ELSE
		-- Début De La Requête De Suppression
		SET @Stmt := Concat('DELETE FROM ', Nametable ,' Where Id = \'', id ,'\' ;');
 
		-- On Prépare Le Stmt Avec @Stmt Qui Contient Notre Requête
		Prepare Stmt FROM @Stmt;
		-- On Exécute Donc La Requête
		Execute Stmt;
		-- On Supprime Le Stmt
		Deallocate Prepare Stmt;
 
		-- Début De La Requête D'Insertion
		SET @Stmt := Concat('INSERT INTO ', Nametable ,' Select * From t1 Where Id = \'', id ,'\' ;');
 
		-- On Prépare Le Stmt Avec @Stmt Qui Contient Notre Requête
		Prepare Stmt FROM @Stmt;
		-- On Exécute Donc La Requête
		Execute Stmt;
		-- On Supprime Le Stmt
		Deallocate Prepare Stmt;
	END IF;
 
END $$
 
DELIMITER ;

Quand je crée les triggers et la procédure stockée, j'ai seulement :

Code :

Query OK, 0 rows affected (0.00 sec)

Mais quand je veux insérer 1 enregistrement dans t1 :

Code :

1
2
 
INSERT INTO t1 VALUES ('975-01','10','test 975', 'adr 975') ;

j'obtiens l'erreur suivante :

Code :

ERROR 1336 (0A000): Dynamic SQL IS NOT allowed IN stored FUNCTION OR TRIGGER

.

Ma version de MySql est 5.0.45-community-nt.
J'ai créé d'autres procédures pour générer les tables t1_dpt et là, il n'y a pas de problème.

Si vous avez des idées, d'avance merci.

spg40.

[spg40]

Bonjour,

après de nombreuses tentatives infructueuses, j'ai trouvé une solution. Ce n'est peut-être pas la meilleure mais elle fonctionne.

Le problème était généré par la procédure appelée : en effet, elle contenait du SQL dynamique qui n'est pas supporté par les triggers. A l'exécution, le trigger allait bien dans la procédure, mais dès le "DECLARE" il plantait.

J'ai donc résolu mon problème en faisant des triggers avec des CASE de cette manière :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER $$
 
DROP TRIGGER t1_INS $$
CREATE TRIGGER t1_INS AFTER INSERT ON t1 FOR EACH ROW
BEGIN
	CASE
		WHEN NEW.Dpt = '10' THEN INSERT INTO t1_10 SELECT * FROM t1 WHERE Id = NEW.Id ;
                ...
		WHEN NEW.Dpt = '30' THEN INSERT INTO t1_30 SELECT * FROM t1 WHERE Id = NEW.Id ;
	END CASE ;
END $$
 
DELIMITER ;

On fait de la même manière pour les 3 triggers.

Il faut donc que le nom de la table impacté soit "en dur" (on ne peut pas le modifier dynamiquement).

A+.

spg40.

[Alain Defrance]

Bonjour,

Ce que je trouve très moche c'est t1 t1_10 t1_15 t1_20 avec la même structure.
Çà me parait mal modélisé, et même si on peut se permettre de dévier un peu du modèle parfait, a un moment ça deviens gênant (ici faut que tu répète pas mal de code).

Je ne sais pas ou en est ton projet, mais si c'est possible sans tout chambouler je te conseil vraiment de revoir la conception.