Read 354 times | Created 2013-10-04 02:40:22 | Updated 2017-09-26 07:02:38 | | |

 

DROP TABLE IF EXISTS `nomor`;   
CREATE TABLE IF NOT EXISTS `nomor` ( 
  `id` INT(4) ZEROFILL NOT NULL AUTO_INCREMENT, 
  `ym` CHAR(4) NOT NULL,
  `tanggal` DATE NOT NULL, 
  PRIMARY KEY (`ym`,`id`) 
) ENGINE=MyISAM  DEFAULT CHARSET=utf8; 
 
DELIMITER /
CREATE TRIGGER trig_before_insert_nomor 
BEFORE INSERT ON `nomor`
FOR EACH ROW 
BEGIN
SET NEW.ym=DATE_FORMAT(NEW.tanggal,'%y%m');
END /
DELIMITER ;
 
INSERT INTO `nomor`(`tanggal`) VALUES 
('2013-09-28'),
('2013-09-29'),
('2013-09-30'),
('2013-10-01'),
('2013-10-02'),
('2013-10-03');
 
SELECT CONCAT(ym,'-',id) AS kode_transaksi,tanggal 
FROM nomor ORDER BY tanggal; 
+----------------+------------+
| kode_transaksi | tanggal    |
+----------------+------------+
| 1309-0001      | 2013-09-28 |
| 1309-0002      | 2013-09-29 |
| 1309-0003      | 2013-09-30 |
| 1310-0001      | 2013-10-01 |
| 1310-0002      | 2013-10-02 |
| 1310-0003      | 2013-10-03 |
+----------------+------------+