[TRIGGER] insertion avec clef auto-increment dupliquer

Version imprimable

Bonjour,

Je possede une base de donnèes pour gerer utilisateur, boite de messagerie, message. Je crèè quelque trigger pour automatiser un peut la gestion:

- sur creation d'un utilisateur, creation de boite de messagerie standard
- sur suppression d'un utilisateur, suppression de ses boite de messagerie
- sur suppression des boite de messagerie, suppression de leur message

lors de la creation du 2ème(ou plus) utilisateur les boite de messagerie ne peuvent etre generer car la clef '0' est dupliquer.
la 1ere chose auquel on pense est "on assigne tout le temp 0 en clef primaire de boite de messagerie" mais non....(ou sinon je ne comprend plus se que je tape)

Creation de Table

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
DROP DATABASE IF EXISTS `mediamanager`;
CREATE DATABASE `mediamanager`;
USE `mediamanager`;
 
CREATE TABLE user(
	id_user VARCHAR(45) NOT NULL,
	userName VARCHAR(45) NOT NULL,
	password VARCHAR(45) NOT NULL,
	email VARCHAR(45) NOT NULL,
	question VARCHAR(45) NOT NULL,
	reponse VARCHAR(45) NOT NULL,
	PRIMARY KEY (`id_user`)
) ENGINE=InnoDB;
 
CREATE TABLE boite_message(
	id_boite_message INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
	id_user VARCHAR(45) NOT NULL,
	nom VARCHAR(45) NOT NULL,
	obligatoire BOOLEAN NOT NULL,
	PRIMARY KEY(`id_boite_message`)
) ENGINE=InnoDB;
 
CREATE TABLE message(
	id_message INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
	id_expediteur VARCHAR(45) NOT NULL,
	id_boite_message INTEGER UNSIGNED NOT NULL,
	titre VARCHAR(45) NOT NULL,
	corp TEXT NOT NULL,
	date DATETIME,
	lu BOOLEAN NOT NULL,
	PRIMARY KEY(`id_message`)
) ENGINE=InnoDB;
 
CREATE VIEW liste_message AS
	SELECT b.id_user AS 'id_user', b.id_boite_message, u1.userName AS 'expediteur', m.id_message, titre, corp, DATE_FORMAT(date,GET_FORMAT(DATE,'EUR')) AS 'date', lu
	FROM user u1, user u2, boite_message b, message m
	WHERE m.id_expediteur = u1.id_user AND u2.id_user = b.id_user AND b.id_boite_message = m.id_boite_message;

Creation des Triggers et Insertion d'un utilisateur:

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
USE `mediamanager`;
 
DELIMITER |
 
/* CREATION UTILISATEUR => CREATION BOITE DE MESSAGERIE STANDARD */
CREATE TRIGGER BoiteMessageStandard
 AFTER INSERT ON user
 FOR EACH ROW BEGIN
	INSERT INTO boite_message (id_user, nom, obligatoire) VALUES(NEW.id_user, 'Reception', '1');
	INSERT INTO boite_message (id_user, nom, obligatoire) VALUES(NEW.id_user, 'Emission', '1');
	INSERT INTO boite_message (id_user, nom, obligatoire) VALUES(NEW.id_user, 'Corbeille', '1');
 END;
|
 
/* SUPPRESSION UTILISATEUR => SUPPRESSION BOITE MESSAGERIE */
CREATE TRIGGER PurgeBoiteMessage
 AFTER DELETE ON user
 FOR EACH ROW BEGIN
    DELETE FROM boite_message WHERE id_user = DELETED.id_user;
 END;
|
 
/* SUPPRESSION BOITE MESSAGE => SUPPRESSION MESSAGE */
CREATE TRIGGER PurgeMessage
 AFTER DELETE ON boite_message
 FOR EACH ROW BEGIN
	DELETE FROM message WHERE id_boite_message = DELETED.id_boite_message;
 END;
|
 
DELIMITER ;
 
INSERT INTO user VALUES('1', 'Admin', 'admin', 'mail@mail.com', 'Quel est le nom de l''administrateur?', 'TRUC');

merci pour votre aide

18/03/2009, 11h43
StephaneC.

Mais au final, quelle est ta question ? :mouarf:

Stéphane
18/03/2009, 12h20
TheBlackReverand

:aie:

enfait d'après moi je ne definis en aucune maniere la clef primaire dans mon trigger mais uniquement les valeur non indexè. alors quel erreur ai je fait pour qu'il me dise "tu ne peut pas inseret une ligne avec la clef primaire '0' car elle existe deja"
18/03/2009, 16h05
StephaneC.
Si tu fais ça :
Code:

1 2 INSERT INTO user VALUES('1', 'Admin', 'admin', ...); INSERT INTO user VALUES('2', 'XXX', 'xxx', ...);
ça fonctionne très bien donc a priori pas d'erreur dans ton trigger.

Par contre, si tu fais ça :
Code:

1 2 INSERT INTO user VALUES('1', 'Admin', 'admin', ...); INSERT INTO user VALUES('1', 'XXX', 'xxx', ...);
alors ça ne fonctionne plus...mais c'est normal et ça ne vient pas de ton trigger.
C'est simplement que tu as une primary key et que tu essaies d'insérer plusieurs lignes avec une même valeur de primary key !

Si tu définissais plutôt le champ id_user de ta table user comme un INT auto_increment, tu n'as plus besoin de t'occuper de peupler ce champ et tu n'auras plus de problème. Et en bonus, InnoDB sera encore plus content avec une primary key en INT.

Stéphane
18/03/2009, 19h31
TheBlackReverand

euh je suis pas sur que l'on se soit compris....

ma table User n'as aucun problème d'ajout mais lorsque je fait un ajout dans User cela doit ajouter 3 enregistrement dans BoiteMessagerie et se sont ces 3 insertion qui ne fonctionne pas

il reagit comme si j'avais defini la clef primaire de mes 3 nouvelle BoiteDeMessagerie alors que ce n'est pas le cas, j'ai fait des test, le conflit de clef primaire interviens dans la table "BoiteMessagerie"

Euh, en effet, on ne doit pas se comprendre ...

Après avoir défini la table et les triggers comme tu l'as fait dans ton 1er post, je fais les manips suivantes :

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
 
mysql> INSERT INTO user VALUES('1', 'Admin', 'admin', 'mail@mail.com', 'Quel est le nom de l''administrateur?', 'TRUC');
Query OK, 1 row affected (0.03 sec)
 
mysql> SELECT * FROM boite_message;
+------------------+---------+-----------+-------------+
| id_boite_message | id_user | nom       | obligatoire |
+------------------+---------+-----------+-------------+
|                1 | 1       | Reception |           1 | 
|                2 | 1       | Emission  |           1 | 
|                3 | 1       | Corbeille |           1 | 
+------------------+---------+-----------+-------------+
3 rows in set (0.00 sec)
 
mysql> INSERT INTO user VALUES('2', 'Admin2', 'admin2', 'mail2@mail.com', 'Quel est le nom de l''administrateur?2', 'TRUC2');
Query OK, 1 row affected (0.01 sec)
 
mysql> SELECT * FROM boite_message;
+------------------+---------+-----------+-------------+
| id_boite_message | id_user | nom       | obligatoire |
+------------------+---------+-----------+-------------+
|                1 | 1       | Reception |           1 | 
|                2 | 1       | Emission  |           1 | 
|                3 | 1       | Corbeille |           1 | 
|                4 | 2       | Reception |           1 | 
|                5 | 2       | Emission  |           1 | 
|                6 | 2       | Corbeille |           1 | 
+------------------+---------+-----------+-------------+
6 rows in set (0.00 sec)

Tout fonctionne donc bien comme prévu.
Peux-tu m'indiquer des requêtes qui te provoquent une erreur ?

Stéphane

19/03/2009, 19h01
TheBlackReverand

:bug:

c'est exactement ce que tu viens de fair que je ne peut pas fair :aie:

sa fonctionne uniquement si il n'y a pas de clef primaire = '0' dans la table "BoiteMessagerie" sinon sa ne fonctionne pas (donc sa bug après au niveau du 2ème utilisateur crèè)

problème de version?? :(
MySql Administrator identifie mon serveur comme la version 6.0.3 beta
20/03/2009, 21h59
StephaneC.

Et as-tu fait un test sur une version 5.1 ?

On ne sait jamais, tu as peut-être trouvé un bug sur la v6 :lol:

Stéphane
23/03/2009, 18h10
TheBlackReverand

mauvaise nouvel pour nous, bonne pour MySql

le code ne fonctionne pas sous la v5.1....

pas de bug dans le MySQL 6 mais gros bug pour mon application!!

:aie:

personne ne vois se qui pourrais se passer?
j'ai seulement installer MySQL 5.1, les MySQL Tools 5
executer le code citè plus haut

et hop le 2ème utilisateur fait bugger le truc!!
23/03/2009, 21h58
StephaneC.

Peux-tu donner l'intégralité des requêtes SQL que tu joues pour avoir ton erreur ?
Il doit bien y avoir une explication à ton problème 8O

Stéphane

merci pour ton aide, je vais tout refair est noter chaque etape en allant

1) verification via MySQL Administrator
version = 5.1.22-rc-community via TCP/IP
ip = 127.0.0.1

2) execution des 2 script suivant via MySQL Query Brower

Script 1 : detruit toute la base et crèèr les table
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 DROP DATABASE IF EXISTS `mediamanager`; CREATE DATABASE `mediamanager`; USE `mediamanager`; CREATE TABLE user( id_user VARCHAR(45) NOT NULL, userName VARCHAR(45) NOT NULL, password VARCHAR(45) NOT NULL, email VARCHAR(45) NOT NULL, question VARCHAR(45) NOT NULL, reponse VARCHAR(45) NOT NULL, PRIMARY KEY (`id_user`) ) ENGINE=InnoDB; CREATE TABLE boite_message( id_boite_message INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, id_user VARCHAR(45) NOT NULL, nom VARCHAR(45) NOT NULL, obligatoire BOOLEAN NOT NULL, PRIMARY KEY(`id_boite_message`) ) ENGINE=InnoDB; CREATE TABLE message( id_message INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, id_expediteur VARCHAR(45) NOT NULL, id_boite_message INTEGER UNSIGNED NOT NULL, titre VARCHAR(45) NOT NULL, corp TEXT NOT NULL, date DATETIME, lu BOOLEAN NOT NULL, PRIMARY KEY(`id_message`) ) ENGINE=InnoDB; CREATE VIEW liste_message AS SELECT b.id_user AS 'id_user', b.id_boite_message, u1.userName AS 'expediteur', m.id_message, titre, corp, DATE_FORMAT(date,GET_FORMAT(DATE,'EUR')) AS 'date', lu FROM user u1, user u2, boite_message b, message m WHERE m.id_expediteur = u1.id_user AND u2.id_user = b.id_user AND b.id_boite_message = m.id_boite_message;
Script 2 : crèè les triggers puis ajoute le compte admin
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 USE `mediamanager`; DELIMITER | /* CREATION UTILISATEUR => CREATION BOITE DE MESSAGERIE STANDARD */ CREATE TRIGGER BoiteMessageStandard AFTER INSERT ON user FOR EACH ROW BEGIN INSERT INTO boite_message (id_user, nom, obligatoire) VALUES(NEW.id_user, 'Reception', '1'); INSERT INTO boite_message (id_user, nom, obligatoire) VALUES(NEW.id_user, 'Emission', '1'); INSERT INTO boite_message (id_user, nom, obligatoire) VALUES(NEW.id_user, 'Corbeille', '1'); END; | /* SUPPRESSION UTILISATEUR => SUPPRESSION BOITE MESSAGERIE */ CREATE TRIGGER PurgeBoiteMessage AFTER DELETE ON user FOR EACH ROW BEGIN DELETE FROM boite_message WHERE id_user = DELETED.id_user; END; | /* SUPPRESSION BOITE MESSAGE => SUPPRESSION MESSAGE */ CREATE TRIGGER PurgeMessage AFTER DELETE ON boite_message FOR EACH ROW BEGIN DELETE FROM message WHERE id_boite_message = DELETED.id_boite_message; END; | DELIMITER ; INSERT INTO user VALUES('1', 'Admin', 'admin', 'mail@mail.com', 'Quel est le nom de l''administrateur?', 'TRUC');
3) verification
utilisateur "Admin" crèè
3 boite de messagerie ajouter(id_boite_messagerie = 0;1;2)

4) insertion d'un utilisateur supplementaire

Code:

INSERT INTO user VALUES('2', 'Utilisateur2', 'uti2', 'mail2@mail.com', 'Quel est le nom de l''administrateur?', 'TRUC2');

=> erreur generer(n° 1062)

Citation:

Duplicate entry '0' for key 'PRIMARY'

TEST SUR LA CLEF GENANTE
1) modification(via "edit" du mysql query browser) des "id_boite_messagerie"
0=>10
1=>11
2=>12

2) insertion d'un utilisateur

Code:

INSERT INTO user VALUES('2', 'Utilisateur2', 'uti2', 'mail@mail.com', 'Quel est le nom de l''administrateur?', 'TRUC');

=> aucune erreur

3) etat des table
utilisateur => admin + Utilisateur2
boite_messagerie => id_boite_messagerie = 0;7;8;9;10;11;12

on peut donc en conclure que la 1er requete d'ajout de mon trigger veut toujour un "id_boite_messagerie" = 0
le pourquoi du comment?! :aie:

encore plus bizarre, comment mon champ et unsigned l'auto_increment doit commencer a 0!!
il commence a 0 si je fait une insertion manuel mais pas via le trigger, donc mon trigger ne laisse pas le choix a ma table pour le choix de l'id_boite_messagerie ce qui est anormal car mes requete d'ajout ne l'assigne pas....

[RESOLU][INCOMPRI] :yaisse2:
bon j'ai resolus le problème, mais je sais pas comment(enfin si mais sa ni rien comprendre!)

j'ai tout simplement changer la formulation de mon ajout. j'ai tout simplement resumè les 3 ajout en 1 seul requete, je ne sais pas si sa a eu pour consequence de redonner la main a la table mais sa fonctionne...

voici donc mon nouveau trigger
Code:

1 2 3 4 5 CREATE TRIGGER BoiteMessageStandard AFTER INSERT ON user FOR EACH ROW BEGIN INSERT INTO boite_message (id_user, nom, obligatoire) VALUES(NEW.id_user, 'Reception', '1'),(NEW.id_user, 'Emission', '1'),(NEW.id_user, 'Corbeille', '1'); END;
si quelqu'un peut m'expliquer ce comportement... :roll:

en tout cas merci a tous