Discussion :

[bringer]

Bonjours à tous et merci de m'accueillir parmis vous.

Pour mon premier message, je fais appel à vos compétence car aprés plusieurs recherches (dans les tutos et sur le net), je n'est pas trouvé de reponse, ou tout du moins je n'est pas réussi à les mettre en oeuvre.

Je developpe en python une appli pour le boulot qui permet un suivi de pieces administratives qu'une personne doit fournir dans son dossier d'inscription.
Les personnes sont récupérées depuis une base externe via un fichier csv et intégrée dans la nouvelle base dans une table T_PERSONNE (pas de problème).
Les pieces administratives sont elles dans une table T_PIECE et sont renseignées 'manuellement'.
à l'entre deux, j'ai une table de jointure avec ID_personne, ID_piece, etat piece (0 absente, 1 presente).
Mon problème est que je ne parvient pas à mettre à jour cette table de jointure pour que à l'insertion ou à la suppression d'une personne ou d'une piece la table prenne la forme :
ID_personne1, ID_piece1, ETAT,
ID_personne1, ID_piece2, ETAT,
ID_personne1, ID_piece3, EAT,...etc pour chaque personne et chaque piece.
Je pense que cela doit se faire avec un trigger INSERT et DELETE mais je ne parvient pas à le coder.

Je suis sous SQLITE3 + python 2.5.2+linux ubuntu
Merci de votre aide

[bringer]

bonjour à tous,

je reviens vers vous pour ajouter quelques precisions qui peuvent peut-être aider.
J'ai déjà réfléchi sur untrigger en le formalisant litérralement et cela donne:

1 - créer in trigger trigger_name après insertion dans la table personne
2 - pour chaque ligne de la table des dossiers
3 - faire
4- inserer dans la table de jointure
5 - champs (ID_personne, ID_piece, etat_piece)
6 - valeurs (nouvel.ID_personne,
7 - valeur de ID_piece en cours de lecture dans la boucle
8 - ETAT_PIECE par defaut
9 - );
10 - fin boucle

en traduction dans le trigger j'ai réussi à faire :

Code :

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

1
2
3
4
5
6
7
8
 
CREATE TRIGGER TR_PERS_DOSS AFTER INSERT ON T_PERSONNE
FOR EACH ROW
BEGIN
INSERT INTO TJ_PERS_DOSS (ID_PERSONNE, ID_PIECE)
VALUES (new.ID_PERSONNE, !LA, JE NE SAIT PAS!, ETAT_PIECE SET DEFAULT);
END
END;

[bigane]

Bonjour,

La solution est simple, comme tu l'as annoncé il te faut passer par les triggers. C'est vrai qu'au début ils peuvent intimider, mais après quelques essais, on y trouve son bonheur.
Il n'y a aucune difficulté pour la suppression. Le véritable problème se situe dans le choix d'une pièce administrative lors de l'insertion d'une nouvelle personne. C'est impossible à créer automatiquement.
Je résume :
- insertion personne : (générer une pièce vide, mettre état à 0 dans la jointure).
- suppression personne : supprimer les enregistrements correspondants de la jointure.
- insertion dossier : ne rien faire (on ne peut savoir pour qui il est)
- suppression dossier : supprimer les enregistrements correspondants de la jointure.

Bien, maintenant comment faire ? Super simple : SQLite fait tout pour toi !
Pour y arriver, construisons trois tables pour un exemple :

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
create table T_PERSONNE (
  ID_personne integer primary key, 
  nom_personne text
);
create table T_PIECE (
  ID_piece integer primary key, 
  nom_piece text
);
create table T_ETAT (
  ID_personne integer,
  ID_piece integer,
  ETAT integer,
  FOREIGN KEY(ID_personne) REFERENCES T_PERSONNE,
  FOREIGN KEY(ID_piece) REFERENCES T_PIECE
);

On ouvre SQLite3 en mode commande et tape la commande ".genfkey"
(Tu peux lire la doc suivante sur la commande ".genfkey". :
http://www.sqlite.org/cvstrac/filevi...genfkey.README
)
Voici le résultat de la commande :

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
sqlite> .genfkey
-- Triggers for foreign key mapping:
--
--     T_ETAT(ID_piece) REFERENCES T_PIECE(ID_piece)
--     on delete RESTRICT
--     on update RESTRICT
--
CREATE TRIGGER genfkey1_insert_referencing BEFORE INSERT ON "T_ETAT" WHEN
    new."ID_piece" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "T_PIECE" WHERE new
."ID_piece" == "ID_piece")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;
CREATE TRIGGER genfkey1_update_referencing BEFORE
    UPDATE OF ID_piece ON "T_ETAT" WHEN
    new."ID_piece" IS NOT NULL AND
    NOT EXISTS (SELECT 1 FROM "T_PIECE" WHERE new."ID_piece" == "ID_piece")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;
CREATE TRIGGER genfkey1_delete_referenced BEFORE DELETE ON "T_PIECE" WHEN
    EXISTS (SELECT 1 FROM "T_ETAT" WHERE old."ID_piece" == "ID_piece")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;
CREATE TRIGGER genfkey1_update_referenced AFTER
    UPDATE OF ID_piece ON "T_PIECE" WHEN
    EXISTS (SELECT 1 FROM "T_ETAT" WHERE old."ID_piece" == "ID_piece")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;
 
-- Triggers for foreign key mapping:
--
--     T_ETAT(ID_personne) REFERENCES T_PERSONNE(ID_personne)
--     on delete RESTRICT
--     on update RESTRICT
--
CREATE TRIGGER genfkey2_insert_referencing BEFORE INSERT ON "T_ETAT" WHEN
    new."ID_personne" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "T_PERSONNE" WHE
RE new."ID_personne" == "ID_personne")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;
CREATE TRIGGER genfkey2_update_referencing BEFORE
    UPDATE OF ID_personne ON "T_ETAT" WHEN
    new."ID_personne" IS NOT NULL AND
    NOT EXISTS (SELECT 1 FROM "T_PERSONNE" WHERE new."ID_personne" == "ID_person
ne")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;
CREATE TRIGGER genfkey2_delete_referenced BEFORE DELETE ON "T_PERSONNE" WHEN
    EXISTS (SELECT 1 FROM "T_ETAT" WHERE old."ID_personne" == "ID_personne")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;
CREATE TRIGGER genfkey2_update_referenced AFTER
    UPDATE OF ID_personne ON "T_PERSONNE" WHEN
    EXISTS (SELECT 1 FROM "T_ETAT" WHERE old."ID_personne" == "ID_personne")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed');
END;

Yes !

Tu as la syntaxe exacte des triggers qui vérifient la table de jointure : on ne peut insérer un état que si une personne et une pièce correspondante existent.
Ce n'est pas exactement ce que tu désires, mais c'est un bon début et tu as un exemple de trigger qui fonctionne.

Pour ton problème, je dirais que les triggers sont du genre :

Code :

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

1
2
3
4
5
6
-- creer un lien sur une piece par défaut
CREATE TRIGGER tai_Personne AFTER INSERT ON "T_PERSONNE" 
BEGIN  
  INSERT INTO T_ETAT (ID_Personne, ID_Piece, ETAT) 
  VALUES (new.ID_Personne, 0, 0);
END;

Ainsi, toute nouvelle personne a une piece vide, dont l'ID est 0. Il te reste à respecter l'intégrité en créant cet enregistrement neutre dans la table des pièces.

Je ne peux aller plus loin, mais le reste est identique à cet exemple et tu devrais en avoir assez pour te débrouiller avec la syntaxe des triggers. Bonne chance.

a+

[bringer]

merci de ta réponse Bigane,

je vais tester tout ça et essayer de le coder pour mon cas. Jusqu'a présent, à defaut d'autre chose, je l'avais coder dans mon application en python, mais cela ne me semble pas des plus logique.

version de sqlite 3.4.2

Je tiens au courant... A+

[bigane]

Salut bringer,

D'après ta réponse postée presque en même temps que la mienne, ton problème est légèrement différent que ce que j'ai pu te proposer...

D'après ton algo, une personne possède un lien vers chaque pièce dès qu'elle est insérée. Donc voici une solution :

1) Création des tables

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
DROP TABLE IF EXISTS T_PERSONNE; 
CREATE TABLE T_PERSONNE(
  ID_PERSONNE INTEGER PRIMARY KEY, 
  NOM_PERSONNE TEXT
);
 
DROP TABLE IF EXISTS T_PIECE; 
CREATE TABLE T_PIECE(
  ID_PIECE INTEGER PRIMARY KEY, 
  NOM_PIECE TEXT
);
 
DROP TABLE IF EXISTS TJ_PERS_DOSS;
CREATE TABLE TJ_PERS_DOSS (
  ID_PERSONNE INTEGER, 
  ID_PIECE INTEGER,
  ETAT_PIECE INTEGER DEFAULT 0,
  FOREIGN KEY(ID_PERSONNE) REFERENCES T_PERSONNE,
  FOREIGN KEY(ID_PIECE) REFERENCES T_PIECE
);

2) Création des triggers

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
CREATE TRIGGER TRAI_PERSONNE AFTER INSERT ON "T_PERSONNE" 
BEGIN  
  INSERT INTO TJ_PERS_DOSS (ID_PERSONNE, ID_PIECE)
  SELECT new.ID_PERSONNE, ID_PIECE FROM T_PIECE;  
END;
 
CREATE TRIGGER TRAD_PERSONNE AFTER DELETE ON "T_PERSONNE" 
BEGIN  
  DELETE FROM TJ_PERS_DOSS 
  WHERE ID_PERSONNE = old.ID_PERSONNE;
END;
 
CREATE TRIGGER TRAI_PIECE AFTER INSERT ON "T_PIECE" 
BEGIN  
  INSERT INTO TJ_PERS_DOSS (ID_PERSONNE, ID_PIECE)
  SELECT new.ID_PIECE,ID_PERSONNE FROM T_PERSONNE;  
END;
 
CREATE TRIGGER TRAD_PIECE AFTER DELETE ON "T_PIECE" 
BEGIN  
  DELETE FROM TJ_PERS_DOSS 
  WHERE ID_PIECE = old.ID_PIECE;
END;

3) Quelques tests pour vérifier :

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
Insert into T_PIECE (NOM_PIECE) values ('Doc A');
Insert into T_PIECE (NOM_PIECE) values ('Doc B');
Insert into T_PIECE (NOM_PIECE) values ('Doc C');
 
Insert into T_PERSONNE (NOM_PERSONNE) values ('Pierre');
Update TJ_PERS_DOSS set ETAT_PIECE = 1 where ID_PIECE = 1 and ID_PERSONNE = 1;
 
Insert into T_PERSONNE (NOM_PERSONNE) values ('Paul');
Update TJ_PERS_DOSS set ETAT_PIECE = 1 where ID_PIECE = 3 and ID_PERSONNE = 2;
 
Insert into T_PERSONNE (NOM_PERSONNE) values ('Jacques');
Update TJ_PERS_DOSS set ETAT_PIECE = 1 where ID_PIECE = 2 and ID_PERSONNE = 3;
 
Delete from T_PIECE where NOM_PIECE = 'Doc B';
Delete from T_PERSONNE where NOM_PERSONNE = 'Paul';
 
.mod tab
Select * from TJ_PERS_DOSS;

4) Résultats :

Code :

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

1
2
3
4
5
 
1       1       1
1       3       0
3       1       0
3       3       0

On a bien la génération automatique de la jointure avec une valeur par défaut et il faut modifier cette valeur avec des updates.

c'est bon ?

a+

[bringer]

merci à toi Bigane de m'avoir sorti de ce problème, cela faisait un moment que je cherchais.
Maintenant que la solution est là, elle parait en effet assez simple, mais quand on est débutant, on peut passer à coter de certaines évidences.

Bonne continuation
A+