MySQL Trigger สร้าง Running Number หลังจาก Insert

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(15) DEFAULT NULL,
  `last_name` varchar(15) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `salary` float(8,2) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  `description` varchar(15) DEFAULT NULL,
  `Runno` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;

delimiter $$
CREATE TRIGGER `myTrigger1` 
  BEFORE INSERT ON `employee`
FOR EACH ROW 
BEGIN
  
  IF ((length(NEW.id)) = 1) THEN
    SET NEW.Runno = CONCAT('C00',NEW.id,(SUBSTRING((DATE_FORMAT(CURRENT_TIMESTAMP,"%Y")),3,2))); 
  ELSEIF ((length(NEW.id)) = 2) THEN
    SET NEW.Runno = CONCAT('C0',NEW.id,(SUBSTRING((DATE_FORMAT(CURRENT_TIMESTAMP,"%Y")),3,2))); 
  ELSE
    SET NEW.Runno = CONCAT('C',NEW.id,(SUBSTRING((DATE_FORMAT(CURRENT_TIMESTAMP,"%Y")),3,2))); 
  END IF;
  
END$$
delimiter ;

insert into Employee(first_name, last_name, start_date, end_Date,   salary,  City,       Description)
 values('James',    'Cat',     '19960917',  '20020415', 1232.78,'Vancouver', 'Tester');