A web developer's blog. PHP, MySQL, CakePHP, Zend Framework, Wordpress, Code Igniter, Django, Python, CSS, Javascript, jQuery, Knockout.js, and other web development topics.

MySQL Triggers

Posted this here for personal reference. (For MySQL 5.0)

Useful links for this post:

DELIMITER //
 
CREATE TRIGGER mytest_trigger BEFORE INSERT ON mytest_table
FOR EACH ROW
BEGIN
 
DECLARE maxYear INT;
DECLARE thisYear INT;
DECLARE maxNumForYear INT;
DECLARE newMaxNum INT;
 
SET thisYear = SUBSTRING(YEAR(curdate()),'3','2');
 
SELECT MAX(SUBSTRING(pr_number,'1','2')) INTO maxYear FROM mytest_table WHERE 1;
SELECT MAX(SUBSTRING(pr_number,'6','3')) INTO maxNumForYear FROM mytest_table WHERE maxYear=SUBSTRING(pr_number,'1','2');
 
IF maxYear < SUBSTRING(YEAR(curdate()),'3','2') THEN
SET NEW.pr_number = CONCAT(LPAD(thisYear,2,'0'),'-',NEW.employeeType_code,'-','001');
ELSE
SET newMaxNum = maxNumForYear + 1;
SET NEW.pr_number = CONCAT(LPAD(thisYear,2,'0'),'-',NEW.employeeType_code,'-',LPAD(newMaxNum,3,'0'));
END IF;
 
END;//
 
DELIMITER ;
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>