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