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
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `email`
--------------
--------------
CREATE TABLE `email`
(
`id` integer unsigned NOT NULL AUTO_INCREMENT,
`contenue` varchar(255) NOT NULL,
`envoyer` datetime NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
select * from email
--------------
--------------
DROP TABLE IF EXISTS `admin`
--------------
--------------
CREATE TABLE `admin`
(
`userid` integer unsigned NOT NULL AUTO_INCREMENT,
`firstname` varchar(255) NOT NULL,
`lastname` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`username` varchar(255) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TRIGGER IF EXISTS `transfert`
--------------
--------------
CREATE TRIGGER `transfert`
BEFORE insert ON `admin`
FOR EACH ROW BEGIN
if exists (select 1 from email where contenue = new.username) then
update email set envoyer = now(), date = new.created where contenue = new.username;
else insert into email (`contenue`,`envoyer`,`date`) values (new.username, now(), new.created);
end if;
END
--------------
--------------
select * from admin
--------------
--------------
select * from email
--------------
--------------
insert into `admin` (`firstname`, `lastname`, `email`, `username`, `created`) values
('toto', 'rond', 'toto@rond.com', 'ut255347', '2015-12-17 15:25:56'),
('tata', 'carre', 'tata@carre.com', 'ut255347', '2016-02-03 11:05:23'),
('titi', 'cercle', 'titi@cercle.com', 'ut844972', '2014-05-08 09:17:35')
--------------
--------------
select * from admin
--------------
+--------+-----------+----------+-----------------+----------+---------------------+
| userid | firstname | lastname | email | username | created |
+--------+-----------+----------+-----------------+----------+---------------------+
| 1 | toto | rond | toto@rond.com | ut255347 | 2015-12-17 15:25:56 |
| 2 | tata | carre | tata@carre.com | ut255347 | 2016-02-03 11:05:23 |
| 3 | titi | cercle | titi@cercle.com | ut844972 | 2014-05-08 09:17:35 |
+--------+-----------+----------+-----------------+----------+---------------------+
--------------
select * from email
--------------
+----+----------+---------------------+---------------------+
| id | contenue | envoyer | date |
+----+----------+---------------------+---------------------+
| 1 | ut255347 | 2016-02-11 08:24:04 | 2016-02-03 11:05:23 |
| 2 | ut844972 | 2016-02-11 08:24:04 | 2014-05-08 09:17:35 |
+----+----------+---------------------+---------------------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
