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
| --------------
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 `test`
--------------
--------------
CREATE TABLE `test`
( `initiale` char(02) NOT NULL,
`naissance` smallint unsigned NOT NULL,
`departement` smallint unsigned NOT NULL,
`compte` tinyint unsigned NOT NULL,
`rang` integer unsigned NOT NULL,
`lib` varchar(255) NOT NULL,
`clef` integer unsigned NOT NULL auto_increment,
primary key (`initiale`,`naissance`,`departement`,`compte`,`rang`),
unique `fk` (`clef`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TRIGGER IF EXISTS `increment`
--------------
--------------
CREATE TRIGGER `increment`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
SET NEW.rang=ifnull((select max(rang)+1 from `test` where initiale=NEW.initiale and naissance=NEW.naissance and departement = NEW.departement and compte = NEW.compte),1);
END
--------------
--------------
insert into `test` (`initiale`,`naissance`,`departement`,`compte`,`lib`) values
('AB', 2000, 25, 17, 'Un'),
('XY', 2007, 06, 11, 'Deux'),
('AB', 2000, 25, 17, 'Trois'),
('XY', 2007, 06, 11, 'Quatre'),
('AB', 2000, 25, 17, 'Cinq'),
('XY', 2007, 06, 11, 'Six')
--------------
--------------
select * from `test`
--------------
+----------+-----------+-------------+--------+------+--------+------+
| initiale | naissance | departement | compte | rang | lib | clef |
+----------+-----------+-------------+--------+------+--------+------+
| AB | 2000 | 25 | 17 | 1 | Un | 1 |
| AB | 2000 | 25 | 17 | 2 | Trois | 3 |
| AB | 2000 | 25 | 17 | 3 | Cinq | 5 |
| XY | 2007 | 6 | 11 | 1 | Deux | 2 |
| XY | 2007 | 6 | 11 | 2 | Quatre | 4 |
| XY | 2007 | 6 | 11 | 3 | Six | 6 |
+----------+-----------+-------------+--------+------+--------+------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager