Document Number with Trigger – MariaDB


Membuat document number 1700000001, 1700000002, 1700000003 …. dst

Hasil field document number, terlihat seperti berikut

Download database *.sql : disini

document_no

 

 

 

 

CREATE TABLE `payroll` (
`payment_no` int(11) NOT NULL,
`year` year(4) NOT NULL,
`description` varchar(100) DEFAULT NULL,
`delete` char(1) DEFAULT ‘0’,
`posting_date` date DEFAULT NULL,
PRIMARY KEY (`payment_no`,`year`)
) ENGINE=MyIsam DEFAULT CHARSET=latin1;

/* Trigger structure for table `payroll` */
DELIMITER $$
/*!50003 CREATE */ /*!50017 DEFINER = ‘root’@’localhost’ */ /*!50003 TRIGGER `payroll` BEFORE INSERT ON `payroll` FOR EACH ROW BEGIN

DECLARE newLineItem INT;
SELECT
IFNULL( (MAX(p.payment_no) + 1) , 1700000000+1) INTO newLineItem
FROM payroll p
WHERE p.year = new.year
AND p.delete = 0
ORDER BY payment_no DESC
LIMIT 1;
SET new.payment_no = newLineItem;
END */$$

DELIMITER ;

================

Setiap menjalankan script insert dibawah ini, trigger akan membuat document number baru & akan di reset pada tahun yg berbeda

INSERT INTO payroll(YEAR) VALUES (2013);
atau

INSERT INTO payroll(YEAR) VALUES (2014);

 

Semoga manfaat

/* SQLyog Enterprise – MySQL GUI v8.1 MySQL – 10.0.1-MariaDB-log : Database – umg ********************************************************************* *//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=”*/;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;/*Table structure for table `payroll` */CREATETABLE `payroll` ( `payment_no` int(11)NOTNULL, `year` year(4)NOTNULL, `description` varchar(100)DEFAULTNULL, `delete` char(1)DEFAULT‘0’, `posting_date` dateDEFAULTNULL,PRIMARYKEY(`payment_no`,`year`))ENGINE=InnoDBDEFAULTCHARSET=latin1;/* Trigger structure for table `payroll` */DELIMITER $$ /*!50003 CREATE *//*!50017 DEFINER = ‘root’@’localhost’ *//*!50003 TRIGGER `payroll` BEFORE INSERT ON `payroll` FOR EACH ROW BEGIN DECLARE newLineItem INT; SELECT IFNULL( (MAX(p.payment_no) + 1) , 1700000000+1) INTO newLineItem FROM payroll p WHERE p.year = new.year AND p.delete = 0 ORDER BY payment_no DESC LIMIT 1; SET new.payment_no = newLineItem; END */$$ DELIMITER;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

About blackphp

All about programming php, ajax, jquery, mysql, etc, tips n trick. Now, i'm concern in php programming with codeigniter framework n also training my design skill with photoshop or corel... wish me luck! ('o')

Posted on December 23, 2013, in MariaDB and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: