Trigger table historique

Version imprimable

Bonjour !

Voilà, j'ai un projet CakePHP avec plusieurs tables, dont une table Operations.
Je suis sur phpMyAdmin avec MySQL.
Je souhaiterais que quand un ajout/modification/suppression soit fait sur cette table, un enregistrement soit fait dans une table d'historique.

Ma table Operations ressemble à ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE TABLE `operations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `client` varchar(40) NOT NULL, `name` varchar(40) NOT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, `year` int(4) NOT NULL, `open_recipe` varchar(10) NOT NULL, `open_prod` varchar(10) NOT NULL, `open_retraction` varchar(10) NOT NULL, `reducing_delivery` varchar(10) NOT NULL, `final_delivery` varchar(10) NOT NULL, `user_id` int(11) NOT NULL, `visible` tinyint(1) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Et je sais qu'il faut que je créée une autre table avec les mêmes champs, en en rajoutant d'autres.
Il faut que je sauvegarde la date à laquelle a été faite cette modif/suppression/ajout, mais en même temps je me dis qu'avec les champs created et modified déjà dans la table, ce n'est peut-être pas nécessaire d'en ajouter ? Et il faut que je garde aussi en mémoire l'utilisateur qui a modifié/supprimé. Celui l'ayant ajouté se trouve déjà dans la table.

Après un peu de recherche, j'ai vu que je pouvais faire cela avec un trigger, sauf que je ne connais pas grand chose sur ce sujet :s

Pouvez-vous m'aider en me donnant des pistes ? En m'expliquant comment faire ?

Merci par avance !
Ju'

Salut anju44.

Voici un exemple d'historisation des interventions sur une table 'test'.

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET session collation_connection=latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `hist`
--------------
 
--------------
CREATE TABLE `hist`
( `id`        integer unsigned  NOT NULL,
  `lib`       varchar(255)      NOT NULL,
  `hist_date` date              NOT NULL,
  `hist_time` time              NOT NULL,
  `hist_user` varchar(255)      NOT NULL,
  `hist_comm`    char(006)      NOT NULL,
  `hist_rang` integer unsigned  NOT NULL auto_increment primary key
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`   integer unsigned  NOT NULL AUTO_INCREMENT primary key,
  `lib`  varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `ajout`
--------------
 
--------------
CREATE TRIGGER `ajout`
AFTER INSERT ON `test`
FOR EACH ROW BEGIN
        insert into `hist` (`id`,`lib`,`hist_date`,`hist_time`,`hist_user`,`hist_comm`) values
        (new.id, new.lib, current_date(), current_time(), user(), "insert");
END
--------------
 
--------------
DROP TRIGGER IF EXISTS `modif`
--------------
 
--------------
CREATE TRIGGER `modif`
BEFORE update ON `test`
FOR EACH ROW BEGIN
        insert into `hist` (`id`,`lib`,`hist_date`,`hist_time`,`hist_user`,`hist_comm`) values
        (old.id, old.lib, current_date(), current_time(), user(), "update");
END
--------------
 
--------------
DROP TRIGGER IF EXISTS `suppr`
--------------
 
--------------
CREATE TRIGGER `suppr`
BEFORE delete ON `test`
FOR EACH ROW BEGIN
        insert into `hist` (`id`,`lib`,`hist_date`,`hist_time`,`hist_user`,`hist_comm`) values
        (old.id, old.lib, current_date(), current_time(), user(), "delete");
END
--------------
 
--------------
insert into test (lib) VALUES ('un'),('deux'),('trois')
--------------
 
--------------
update test set lib = 'quatre' where lib like 'deux'
--------------
 
--------------
delete from test where lib like 'trois'
--------------
 
--------------
insert into test (lib) VALUES ('cinq')
--------------
 
--------------
select * from test
--------------
 
+----+--------+
| id | lib    |
+----+--------+
|  1 | un     |
|  2 | quatre |
|  4 | cinq   |
+----+--------+
--------------
select * from hist
--------------
 
+----+-------+------------+-----------+----------------+-----------+-----------+
| id | lib   | hist_date  | hist_time | hist_user      | hist_comm | hist_rang |
+----+-------+------------+-----------+----------------+-----------+-----------+
|  1 | un    | 2016-03-15 | 19:21:12  | root@localhost | insert    |         1 |
|  2 | deux  | 2016-03-15 | 19:21:12  | root@localhost | insert    |         2 |
|  3 | trois | 2016-03-15 | 19:21:12  | root@localhost | insert    |         3 |
|  2 | deux  | 2016-03-15 | 19:21:12  | root@localhost | update    |         4 |
|  3 | trois | 2016-03-15 | 19:21:12  | root@localhost | delete    |         5 |
|  4 | cinq  | 2016-03-15 | 19:21:12  | root@localhost | insert    |         6 |
+----+-------+------------+-----------+----------------+-----------+-----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Tu me dis si tu veux améliorer cet exemple.

@+

16/03/2016, 09h06
Anju44

Bonjour Artemus24 !

Merci pour ta réponse ! :)
Je vais tester tout ça, et je reviendrais vers toi pour te tenir au courant :)

Je souhaiterais juste savoir: à quoi servent les trois premières lignes ? :o
16/03/2016, 09h20
escartefigue

A noter, que contrairement à d'autres SGBD, MySQL ne permet pas :
- de créer un trigger unique pour plusieurs actions : par exemple AFTER UPDATE OR INSERT est interdit
- de créer un trigger dont les actions se substituent à l'ordre SQL : pas de "INSTEAD OF", seuls BEFORE et AFTER sont possibles

Alors ! Mon trigger fonctionne c'est bon :)
Sauf que le user() ne récupère pas l'id de l'utilisateur qui a fait l'action. Dans la colonne user, il me met juste 0 :/

Autre soucis :
Pour mon trigger de suppression, en fait, ce serait plutôt un trigger de modification spécifique.
En gros, si le champs "visible" de ma table opérations est modifié (passant de 0 à 1), alors, on enregistre l'historique avec en action "delete".
J'ai vu qu'on pouvait faire des if dans un trigger, comment faire alors ?

Voici ce que j'ai fais pour le moment, mais qui ne fonctionne pas, puisqu'une opération n'est jamais supprimée de la BDD, mais seulement du site.
En gros, si mon champ "visible" est à 0, alors l'enregistrement est affiché sur le site, mais s'il passe à 1, alors il n'est plus visible sur le site.
Code:

1 2 3 4 5 6 7 8 9 DELIMITER | CREATE TRIGGER `suppr_operation` BEFORE UPDATE ON `operations` FOR EACH ROW BEGIN INSERT INTO `hist_operations` (`id`, `client`, `name`, `created`, `modified`, `year`, `open_recipe`, `open_prod`, `open_retraction`, `reducing_delivery`, `final_delivery`, `user_id`, `hist_date`, `hist_time`, `hist_user`, `hist_comm`) VALUES (old.id, old.client, old.name, old.created, old.modified, old.year, old.open_recipe, old.open_prod, old.open_retraction, old.reducing_delivery, old.final_delivery, old.user_id, current_date(), current_time(), user(), 'delete'); END | DELIMITER ;
Je ne sais pas trop si je me suis fais comprendre ^^' Donc je le refais une fois :
Si l'utilisateur "supprime" l'enregistrement du site (donc modifie le champ "visible"), alors dans la table hist_operations, l'enregistrement doit se trouver avec écrit "delete" dans le champs hist_comm :/

Merci pour votre aide !

Salut Anju44.

Citation:

Envoyé par Anju44

Je souhaiterais juste savoir: à quoi servent les trois premières lignes ? :o

Comme tu as pu le constater dans cet exemple, les tables utilisent le moteur "InnoDB".
Les deux premières servent à fonctionner en mode transaction.
La validation se fait par un "commit" et le rejet par un "rollback".
La troisième ligne est en trop. Je l'ai supprimé dans le nouvel exemple.

Citation:

Envoyé par Anju44

Sauf que le user() ne récupère pas l'id de l'utilisateur qui a fait l'action. Dans la colonne user, il me met juste 0 :/

Quand tu lances un script mysql, il faut indiquer le user et le password, voire aussi l'hôte, comme dans l'exemple suivant :

Code:

shell> mysql --host=localhost --user=root --password=toor

Sinon, au lieu de mettre la fonction "user()", tu peux mettre "current_user()".

Citation:

Envoyé par Anju44

En gros, si le champs "visible" de ma table opérations est modifié (passant de 0 à 1), alors, on enregistre l'historique avec en action "delete".

En général, le "1" signifie "oui" et le "0" signifie "non".
J'ai modifié mon exemple, en créant une colonne supplémentaire de nom "visible".
J'ai modifié le trigger "update" afin de faire apparaître le mot "erase" quand la colonne "visible" passe de "1" à "0".
Et bien sûr, j'ai ajouté un test afin de voir le résultat que produit cette modification.

Citation:

Envoyé par Anju44

J'ai vu qu'on pouvait faire des if dans un trigger, comment faire alors ?

Oui, c'est possible. Voir l'exemple.

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `hist`
--------------
 
--------------
CREATE TABLE `hist`
( `id`        integer unsigned  NOT NULL,
  `visible`   tinyint           NOT NULL,
  `lib`       varchar(255)      NOT NULL,
  `hist_date` date              NOT NULL,
  `hist_time` time              NOT NULL,
  `hist_user` varchar(255)      NOT NULL,
  `hist_comm`    char(006)      NOT NULL,
  `hist_rang` integer unsigned  NOT NULL auto_increment primary key
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`      integer unsigned  NOT NULL AUTO_INCREMENT primary key,
  `visible` tinyint           NOT NULL,
  `lib`     varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `ajout`
--------------
 
--------------
CREATE TRIGGER `ajout`
AFTER INSERT ON `test`
FOR EACH ROW BEGIN
        insert into `hist` (`id`,`lib`,`visible`,`hist_date`,`hist_time`,`hist_user`,`hist_comm`) values
        (new.id, new.lib, new.visible, current_date(), current_time(), current_user(), "insert");
END
--------------
 
--------------
DROP TRIGGER IF EXISTS `modif`
--------------
 
--------------
CREATE TRIGGER `modif`
BEFORE update ON `test`
FOR EACH ROW BEGIN
        declare _comm char(06);
 
        if (new.visible = 0) then
             set _comm = "erase ";
        else set _comm = "update";
        end if;
 
        insert into `hist` (`id`,`lib`,`visible`,`hist_date`,`hist_time`,`hist_user`,`hist_comm`) values
        (old.id, old.lib, old.visible, current_date(), current_time(), current_user(), _comm);
END
--------------
 
--------------
DROP TRIGGER IF EXISTS `suppr`
--------------
 
--------------
CREATE TRIGGER `suppr`
BEFORE delete ON `test`
FOR EACH ROW BEGIN
        insert into `hist` (`id`,`lib`,`visible`,`hist_date`,`hist_time`,`hist_user`,`hist_comm`) values
        (old.id, old.lib, old.visible, current_date(), current_time(), current_user(), "delete");
END
--------------
 
--------------
insert into test (lib,visible) VALUES ('un',1),('deux',1),('trois',1)
--------------
 
--------------
update test set lib = 'quatre' where lib like 'deux'
--------------
 
--------------
delete from test where lib like 'trois'
--------------
 
--------------
insert into test (lib,visible) VALUES ('cinq',1)
--------------
 
--------------
update test set visible = 0 where lib like 'quatre'
--------------
 
--------------
select * from test
--------------
 
+----+---------+--------+
| id | visible | lib    |
+----+---------+--------+
|  1 |       1 | un     |
|  2 |       0 | quatre |
|  4 |       1 | cinq   |
+----+---------+--------+
--------------
select * from hist
--------------
 
+----+---------+--------+------------+-----------+----------------+-----------+-----------+
| id | visible | lib    | hist_date  | hist_time | hist_user      | hist_comm | hist_rang |
+----+---------+--------+------------+-----------+----------------+-----------+-----------+
|  1 |       1 | un     | 2016-03-16 | 13:05:10  | root@localhost | insert    |         1 |
|  2 |       1 | deux   | 2016-03-16 | 13:05:10  | root@localhost | insert    |         2 |
|  3 |       1 | trois  | 2016-03-16 | 13:05:10  | root@localhost | insert    |         3 |
|  2 |       1 | deux   | 2016-03-16 | 13:05:10  | root@localhost | update    |         4 |
|  3 |       1 | trois  | 2016-03-16 | 13:05:10  | root@localhost | delete    |         5 |
|  4 |       1 | cinq   | 2016-03-16 | 13:05:10  | root@localhost | insert    |         6 |
|  2 |       1 | quatre | 2016-03-16 | 13:05:10  | root@localhost | erase     |         7 |
+----+---------+--------+------------+-----------+----------------+-----------+-----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

16/03/2016, 15h07
Anju44

Super, merci ça fonctionne :)

Une dernière petite question, et normalement ça devrait être bon !

Si un champ en particulier est modifié, comment faire pour ne pas qu'il y ait d'insertion dans la table d'historique ?
Par exemple, j'ai un champ "nbr_connexion" dans ma table users, qui se modifie à chaque connexion. Du coup, à chaque connexion, un nouvel enregistrement s'ajoute dans la table d'historique, comment faire pour que ce soit pas le cas ? Est-ce que je peux mettre l'insert dans le if ?

Salut Anju44.

Tu n'es pas obligé de transférer toutes les colonnes de la table "TEST" dans ta table "HIST", mais juste ceux qui te sont utiles.

Citation:

Envoyé par Anju44

Si un champ en particulier est modifié, comment faire pour ne pas qu'il y ait d'insertion dans la table d'historique ?

Pour pouvoir le tester, il faut avoir le "old." et le "new." à ta disposition dans le trigger.
Or dans le trigger "insert", tu disposes que du "new.", puisque le "old." n'existe pas.
Dans le trigger "delete", tu disposes que du "old." puisque le "new." n'existe pas.
Cela ne pose pas de problème sur ces deux trigger car c'est une opération de mise à jour (update).

Dans le troisième trigger, celui du update, tu disposes du "old." et du "new." ( ça tombe bien :P )
Oui, en effet, il suffit de faire un "if", comme ci-après :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 -------------- CREATE TRIGGER `modif` BEFORE update ON `test` FOR EACH ROW BEGIN declare _comm char(06); if (new.visible = 0) then set _comm = "erase "; else set _comm = "update"; end if; if (old.connexion = new.connexion) then insert into `hist` (`id`,`lib`,`visible`,`hist_date`,`hist_time`,`hist_user`,`hist_comm`) values (old.id, old.lib, old.visible, current_date(), current_time(), current_user(), _comm); end if; END --------------
L'insertion dans la table "HIST" se fera uniquement quand la valeur de connexion n'aura pas changé.

Et ton problème de user. Est-il résolu ?

@+

16/03/2016, 15h27
Anju44

C'est super ! Merci beaucoup pour ton aide :)
16/03/2016, 16h10
escartefigue

Citation:

Envoyé par Artemus24

if (old.connexion = new.connexion) then
insert into `hist` (`id`,`lib`,`visible`,`hist_date`,`hist_time`,`hist_user`,`hist_comm`) values
(old.id, old.lib, old.visible, current_date(), current_time(), current_user(), _comm);
end if;
END[/code]
L'insertion dans la table "HIST" se fera uniquement quand la valeur de connexion n'aura pas changé.

il y a une petite coquille, mais le principe ne change pas ;)