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
| --------------
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 `t_mesinfos`
--------------
--------------
CREATE TABLE `t_mesinfos`
( `id` integer unsigned NOT NULL auto_increment primary key,
`user_id` integer unsigned NOT NULL,
`type_info` varchar(255) NOT NULL,
`data_info` varchar(255) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TRIGGER IF EXISTS `modif_data_info`
--------------
--------------
CREATE TRIGGER `modif_data_info`
BEFORE insert ON `t_mesinfos`
FOR EACH ROW
BEGIN
IF (NEW.type_info = 'Palier B') THEN
SET NEW.data_info = concat('CC-AAA',NEW.user_id,'-',date_format(now(),'%d%m%Y'));
END IF;
END
--------------
--------------
insert into `t_mesinfos` (`user_id`,`type_info`,`data_info`) values
(24, 'Palier A', '75849'),
(24, 'Palier B', 'AA-66-31012019'),
(54, 'Palier A', '655744'),
(62, 'Palier B', 'AA-66-31012019')
--------------
--------------
select * from `t_mesinfos`
--------------
+----+---------+-----------+-------------------+
| id | user_id | type_info | data_info |
+----+---------+-----------+-------------------+
| 1 | 24 | Palier A | 75849 |
| 2 | 24 | Palier B | CC-AAA24-01022019 |
| 3 | 54 | Palier A | 655744 |
| 4 | 62 | Palier B | CC-AAA62-01022019 |
+----+---------+-----------+-------------------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager