Trigger table historique

**Anju44** · 15/03/2016, 17h03

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 :

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
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'

**Artemus24** · 15/03/2016, 19h22

Salut anju44.

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

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
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.

@+

**Anju44** · 16/03/2016, 09h06

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

**escartefigue** · 16/03/2016, 09h20

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

**Anju44** · 16/03/2016, 10h57

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 :

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

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 !

**Artemus24** · 16/03/2016, 13h06

Salut Anju44.

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.

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 :

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

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

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

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.

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 :

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
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...

@+

**Anju44** · 16/03/2016, 15h07

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 ?

**Artemus24** · 16/03/2016, 15h23

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.

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

)
Oui, en effet, il suffit de faire un "if", comme ci-après :

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
--------------
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 ?

@+

**Anju44** · 16/03/2016, 15h27

C'est super ! Merci beaucoup pour ton aide

**escartefigue** · 16/03/2016, 16h10

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

Trigger table historique

SQL Procédural MySQL

Vue hybride

Discussions similaires

Partager

Partager