Discussion :

[sabrine2]

Bonjour,

J'ai une base de données SQLite, je veux enregistrer n'importe quelle modification que subit ma base dans une nouvelle table que j'ai créée dans la même base. J'ai pensé à utiliser les triggers mais je ne sais pas si c'est possible ou pas. Sinon y’a-t-il une autre manière pour le faire.

Merci de m'aider.

[al1_24]

Si SQLite accepte les triggers, c'est en effet la solution à privilégier puisque toutes les opérations peuvent être tracées (INSERT, UPDATE, DELETE)

[bigane]

Bonjour,

Effectivement on peut faire bien des choses avec les triggers, notamment les traces. Par contre, on ne sait jamais quelle est la requête exacte qui a été exécutée. Il faudrait pour cela détourner le code original de SQLite.
Voici un exemple de trace très détallée :

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
begin transaction;
 
drop table if exists traces;
create table traces (
  h_act text,
  h_tab text,
  h_col text,
  h_val text,
  h_ind text,
  h_dat text
);
 
drop table if exists essai;
create table essai (
  a integer,
  b real,
  c text,
  d blob
);
 
create trigger tai_essai after insert on essai
begin
  insert into traces values ( "insert","essai", "a", new.a, new.rowid, datetime());
  insert into traces values ( "insert","essai", "b", new.b, new.rowid, datetime());
  insert into traces values ( "insert","essai", "c", new.c, new.rowid, datetime());
  insert into traces values ( "insert","essai", "d", new.d, new.rowid, datetime());
end;
 
 
create trigger tau_a_essai after update of a on essai
begin
  insert into traces values ( "update","essai", "a", new.a, new.rowid, datetime());
end;
 
create trigger tau_b_essai after update of b on essai
begin
  insert into traces values ( "update","essai", "b", new.b, new.rowid, datetime());
end;
 
create trigger tau_c_essai after update of c on essai
begin
  insert into traces values ( "update","essai", "c", new.c, new.rowid, datetime());
end;
 
create trigger tad_essai after delete on essai
begin
  insert into traces values ( "delete","essai", "a,b,c,d", null, old.rowid, datetime());
end;
 
commit;

On saisis quelques lignes :

Code :

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

1
2
3
4
5
6
insert into essai (a,b) values (1, 2.2);
insert into essai (a,c) values (7, 'aaa');
insert into essai (a,b,c,d) values (5, 5.5, 'xxx','yyy');
update essai set c='zzzz' where b is not null;
delete from essai where b=2.2;
select * from traces;

Résultats :

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
insert  essai   a       1       1       2009-08- ...
insert  essai   b       2.2     1       2009-08- ...
insert  essai   c               1       2009-08- ...
insert  essai   d               1       2009-08- ...
insert  essai   a       7       2       2009-08- ...
insert  essai   b               2       2009-08- ...
insert  essai   c       aaa     2       2009-08- ...
insert  essai   d               2       2009-08- ...
insert  essai   a       5       3       2009-08- ...
insert  essai   b       5.5     3       2009-08- ...
insert  essai   c       xxx     3       2009-08- ...
insert  essai   d       yyy     3       2009-08- ...
update  essai   c       zzzz    1       2009-08- ...
update  essai   c       zzzz    3       2009-08- ...
delete  essai   a,b,c,d         1       2009-08- ...

a+

[bigane]

PS: Si le trigger de l'INSERT génère une seule ligne INSERT pour la première colonne puis autant de lignes UPDATE qu'il reste de colonnes dans la table, alors on a la possibilité d'utiliser la table TRACES pour rejouer exactement toutes les opérations les unes après les autres.
Il n'y a presque rien à changer dans le code précédent :

Code :

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

1
2
3
4
5
6
7
create trigger tai_essai after insert on essai
begin
  insert into traces values ( "insert","essai", "a", new.a, new.rowid, datetime());
  insert into traces values ( "update","essai", "b", new.b, new.rowid, datetime());
  insert into traces values ( "update","essai", "c", new.c, new.rowid, datetime());
  insert into traces values ( "update","essai", "d", new.d, new.rowid, datetime());
end;

Ce qui donne :

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
insert  essai   a       1       1       2009-08-...
update  essai   b       2.2     1       2009-08-...
update  essai   c               1       2009-08-...
update  essai   d               1       2009-08-...
insert  essai   a       7       2       2009-08-...
update  essai   b               2       2009-08-...
update  essai   c       aaa     2       2009-08-...
update  essai   d               2       2009-08-...
insert  essai   a       5       3       2009-08-...
update  essai   b       5.5     3       2009-08-...
update  essai   c       xxx     3       2009-08-...
update  essai   d       yyy     3       2009-08-...
update  essai   c       zzzz    1       2009-08-...
update  essai   c       zzzz    3       2009-08-...
delete  essai   a,b,c,d         1       2009-08-...

Ainsi, si on exécute ces ordres un par un jusqu'à une date précise, on peut retrouver l'état exact de la table à cet instant précis.

a+

[sabrine2]

Merci beaucoup pour les réponses.
Vos réponses m’ont été très utiles Bigane, je créé le trigger mais je rencontre un problème :
Après l’avoir créer je peux plus insérer des données dans la table correspondante.
Je travaille avec java, voilà le code avec lequel j’ai créé le trigger :

Code :

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

1
2
3
4
5
6
private static final String DB_CREATE_TICONTACT =
"CREATE TRIGGER ti_contact after INSERT ON contact " + 
	"begin" +
		" INSERT INTO traces VALUES ( 'contact','insert', new._idcontact, datetime());" +
	" end; ";			  
public void addticontact(){mDb.execSQL(DB_CREATE_TICONTACT);}

Quelqu’un a t-il une idée sur ceci ?

Merci.

[sabrine2]

J'ai oublié de noter que je suis entrain d'exécuter mon programme sur Android.

[bigane]

Bonjour,

Lorsque l'on crée un trigger qui agit sur une table ou une colonne qui n'existe pas, cela ne provoque pas d'erreur. Par contre cela génère une erreur d'exécution lors du déclenchement du trigger. Aussi, cela peut donner ce type de résultat : la création du trigger est OK.. mais impossible d'insérer une ligne car à chaque déclenchement il y a une erreur dans le trigger.

Pour pister le problème, il faut, dans un premier temps, isoler le bug en le séparant du code. Je m'explique. Le mieux, c'est de prendre une copie de la base et de l'ouvrir en ligne de commande avec sqlite3. Si le système d'exploitation ne peux avoir l'utilitaire, alors on récupère le fichier et on l'ouvre sur un autre système (linux, windows) qui lui l'accepte.

Ensuite, il faudrait s'assurer que le trigger est bien créé dans la base depuis un shell avec la commande suivante (ou son équivalent suivant l'OS ou la version de SQLite) :

Code :

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

sqlite3 ma_base.db ".schema contact"

Dernière étape, on s'assure qu'un INSERT dans la table Contact est bien possible et qu'il génère bien au moins une ligne dans la table Traces.

Si l'insertion provoque une erreur (comme la table traces n'existe pas, ou elle a plus ou moins de colonnes que le nombre de paramètres de l'insert, ou datetime() n'est pas compris par la version de SQLite...) alors on corrige l'erreur et on retente en ligne de commande jusqu'à la résolution du bug.

Si la base est OK et que l'on ne comprend toujours pas, le problème vient soit des ordres Insert plus loin dans le code (qui sont mal écrits), soit de la version de SQLite de ce Java (qui ne supporte pas les Triggers ou datetime() mais là j'ai des doutes), soit ... je n'ai plus d'idée.

Voilà les pistes,
bonne traque du bug,
a+

[sabrine2]

C'est résolu, c'étais un problème de non correspondance de type entre les données de la table originale et les valeurs insérées par le trigger.

Merci beaucoup Bigane.