MySQL Triggers

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 ;