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
| SET AUTOCOMMIT = 0;
START TRANSACTION;
-- ============
-- Base `essai`
-- ============
DROP DATABASE IF EXISTS ESSAI;
CREATE DATABASE IF NOT EXISTS `ESSAI`
DEFAULT CHARACTER SET `utf8`
DEFAULT COLLATE `utf8_general_ci`;
USE `ESSAI`;
COMMIT;
-- ============
-- Table `test`
-- ============
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`
(
`date` DATE NOT NULL,
`id` INT NOT NULL,
`montant` DECIMAL(6,2) NOT NULL DEFAULT 0.00,
PRIMARY KEY(`date`,`id`)
) ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`;
-- ===================
-- Trigger `increment`
-- ===================
DROP TRIGGER IF EXISTS `increment`;
DELIMITER $$
CREATE TRIGGER `increment`
BEFORE INSERT ON `test`
FOR EACH ROW BEGIN
SET NEW.id=ifnull((select max(id)+1 from `test` where date=NEW.date),1);
END$$
DELIMITER ;
-- ========================
-- Insertion dans `test`
-- ========================
INSERT INTO `test` (`date`, `montant`) VALUES
('2006-03-14', 5.00),
('2006-03-14', 25.00),
('2006-03-14', 121.00),
('2011-05-05', 75.00),
('2009-07-12', 32.00),
('2009-07-12', 777.00),
('2011-08-04', 32.00),
('2011-08-04', 17.00),
('2011-08-04', 49.00),
('2012-08-25', 89.00),
('2012-08-25', 666.00),
('2017-11-17', 451.00);
COMMIT;
-- ================
-- Vidage `test`
-- ================
select *
from `test`;
SET AUTOCOMMIT = 1; |
Partager